Feature: Dramatically improved documentation for formulas and properties

What's got half a million words? The Lord of the Rings trilogy. And our new formula and property documentation, which is now on the web. Thousands of formula examples make getting started with all the new features easy.

When we launched the first beta version in late 2015, Calcapp supported 20 functions. To document them, we had a simple window, with most functions only being afforded a single sentence:

The very first window documenting Calcapp's 20 functions

Calcapp went from supporting 20 functions to 244 functions overnight in 2016. The old documentation window would not have scaled to that number of functions, so we introduced the function browser (which later evolved into the reference sidebar). While all functions were documented, the descriptions of how they worked remained as terse as ever, with no examples.

Truth be told, we always wanted our formula documentation to equal the best that spreadsheets offered. We could never justify making that investment with a small team, though, not when fundamental features like copy and paste remained unimplemented.

Making a case for better documentation

Back in 2016, when we went from 20 functions to 244 functions, the only function that was exclusive to Calcapp was BLANK, all the others were standard spreadsheet functions implemented by most spreadsheets. We didn’t consider the function library to be the Calcapp function library as much as we considered it to be simply an implementation of standard spreadsheet functions.

Over the years, we started making additions. We didn’t like Excel’s TEXT function and its convoluted format codes, so we created FORMATNUMBER. We needed regular expressions to create an app for generating CHOOSE formulas in 2018, so we adopted Google Sheets functions like REGEXMATCH and REGEXREPLACE and added our own extensions.

Of the 102 new functions added with our new release, some are exclusive to Calcapp, and others feature Calcapp extensions we have brought over from traditional programming. Examples include the formula fragments you can use with functions like FILTER and SUMIF and the enums you can use with functions like XLOOKUP.

All that is to say that we have taken ownership of Calcapp’s function library. We continue to respect the huge investment in time many app authors have made in learning the standard spreadsheet functions, but we’re not afraid to add flourishes of our own or reject ideas that we don’t think fit our vision.

Not all app authors using Calcapp are well-versed in spreadsheet formulas. To help them get started, and to realize what possibilities a well-thought-out formula language offers, we need documentation that can stand on its own, without relying on readers having any prior knowledge of the topic at hand. To make the documentation easy to digest, we need lots of examples.

Finally, Calcapp doesn’t just feature functions and operators, it also features a wholly-original set of properties which can be used to do everything from setting the recipients of a report to what screen the user is brought to when moving on to the next screen. Properties most definitely need great documentation, because it isn’t to be found anywhere else.

All of that is to say that we decided to invest many months of work into writing better documentation because we couldn’t see another way.

The new documentation

Our new documentation consists of thousands of formula examples and around half a million words, which is equivalent to the entire Lord of the Rings trilogy. (Granted, the trilogy is not as repetitive as our documentation for SUMIF, AVERAGEIF and friends, which are all based on the same template.)

The documentation has moved from the reference sidebar to the web. The reference sidebar was much too narrow to give us the room we needed for the new documentation. Also, by moving it to the web, it is much easier to print, and much easier for search engines to discover and index. (If we’re lucky, spreadsheet users will find our documentation through search engines, gain value from it and learn of our existence.)

Without further ado, here’s our formula documentation (encompassing functions and operators) and here’s our property documentation.

We have de-emphasized the “formal parts” of the documentation, including notes on the parameters to functions, operands to operators as well as return values. It’s still there (and has often been expanded) — just press Details to get access to it.

All formulas exist in two versions, one written with decimal points and one written with decimal commas. Press the button next to all formulas, labeled with a decimal point and a decimal comma, to switch between the two variants. The button next to it copies the formula to the clipboard, enabling you to easily paste it into the formula field in Calcapp Creator.

This documentation has been written by our core development team, which is the same team who answers all your support questions. As a result, we are very much aware of the problems app authors have encountered while using Calcapp, and we have tried to clear up as many formula misconceptions as possible through copious examples.

Some functions come with lengthy documentation and lots of examples — for instance, a hard copy of the documentation for REDUCE would fill 13 pages. Other functions, like the specialized LOGNORM.INV, come with documentation that is every bit as terse as it was in 2016. This mainly reflects a lack of domain expertise on our part.

If you need to use a function where you find the documentation lacking, please write us, and we’ll be sure to prioritize it for the next round of improvements to the documentation.

Behind the scenes

Our efforts to modernize and expand our documentation has not just involved writing, it has also been an engineering effort. Notably, we developed something close to a CMS (a Content Management System — like a plain text version of WordPress) to make the writing process as enjoyable as possible.

Our CMS has close technical ties to Calcapp proper, to ensure that all the thousands of formulas in the documentation are correct, meaning that we know for a fact that they won’t emit any errors if you copy and paste them into Calcapp Creator. (Provided that all referenced fields and other elements are present, of course.)

(A bonus to having deep technical ties to Calcapp proper is that we can write formulas once, using decimal points, and have the version using decimal commas generated automatically, using the same system that is used for converting your formulas from one decimal separator to the other when you change that preference.)

Those technical ties weren’t always in place. The bulk of the documentation was written before we realized that we needed to invest time in that engineering effort. Once those ties were in place, we had to spend weeks sorting out all our formula blunders. Yes, we make lots of mistakes when writing formulas too — and we’re the ones who wrote the entirety of Calcapp.

Just because a formula doesn’t emit any errors in Calcapp Creator doesn’t mean that it’s correct. A computer scientist would say that a syntactically correct formula isn’t necessarily semantically correct, meaning that it doesn’t necessarily do what we need it to do.

Testing thousands of formulas manually, through copy and paste, wasn’t an enticing prospect, so we put on our engineering hats once more. We wrote a program that took our documentation and generated an app for Calcapp, containing the documentation in its entirety, including not only all formulas but also their results, when calculated through Calcapp.

Our program essentially generated tens of thousands of text fields with multiple lines whose formulas read something like this:

"Returns whether a text string ends with another text string.
ENDSWITH(""support@calcapp.net"", ""@calcapp.net"") [" & ENDSWITH("support@calcapp.net", "@calcapp.net") & "] returns TRUE, as
""support@calcapp.net"" ends with ""@calcapp.net""."
"Returns whether a text string ends with another text string.
ENDSWITH(""support@calcapp.net"", ""@calcapp.net"") [" & ENDSWITH("support@calcapp.net"; "@calcapp.net") & "] returns TRUE, as
""support@calcapp.net"" ends with ""@calcapp.net""."

Essentially, we got an app containing all formulas immediately followed by the result of evaluating said formula in brackets. The formula above generates the following output in the app:

            Returns whether a text string ends with another text string.
ENDSWITH("support@calcapp.net", "@calcapp.net") [TRUE] returns TRUE, as
"support@calcapp.net" ends with "@calcapp.net".

Still, checking this app to ensure that all formulas returned the expected results took weeks. It helped us catch and fix hundreds of errors, not just formula errors in the documentation — and there were plenty of those — but in Calcapp itself. The part of Calcapp that implements our function library already had thousands of automated tests prior to our documentation effort, but those weren’t enough to fully stamp out all bugs. Thanks to this work, our function library is of higher quality.

We think we can be reasonably confident that our documentation is mostly correct. If you find any errors — in terms of formulas or in terms of grammar — we want to hear from you.

With the new tools we built, we are looking forward to documenting all the new properties and functions we are planning for future versions.

« Feature: IF without all the parentheses, SWITCH and IFBLANK Feature: Decimal commas in formulas »