Our new formula engine introduces support for arrays and ranges. These are the constructs that make possible many of the new functions and properties of this release. They enable you to write space-saving formulas like SUM(Field1:Field10)SUM(Field1:Field10) and to determine what fields are included in a report using a formula.
While arrays and ranges provide great features today, they will become even more important in the future. In fact, many of the features we are working on for our next-generation Calcapp 4 project are built on arrays and ranges.
So what are arrays? An array is simply a list of values. If you like, you can think of an array as a value that consists of many values. The values of an array are known as the elements of the array.
A number array, consisting of the numbers 1, -2 and 3, is written like this:
If you’re familiar with spreadsheets, you may recognize this construct as an array constant. And you’d be right, but arrays in Calcapp are more powerful.
In Calcapp, array elements can consist of arbitrary calculations which call functions. Also, their elements don’t have to stay the same as your users go about using your app. (That means that they aren’t necessarily constant.) In fact, arrays in Calcapp are very similar to arrays in traditional programming languages, like JavaScript.
This formula is also valid, with three elements:
Here’s another valid formula, with two elements:
The first element of this array changes when the values of Field1 and Field2 change.
Arrays are passed to many of our new functions, including SUMIF, XLOOKUP, INDEX, XMATCH, NETWORKDAYS.INTL and SUMPRODUCT. Also, there are properties whose formulas must return arrays, including the IncludedFields property of reset buttons, email report buttons, open report buttons and server relay buttons.
In order to determine the number of elements of an array, use the new (and Calcapp-exclusive) SIZE function. This is especially useful in conjunction with the FILTER function, which returns a filtered version of an array. With SIZE and FILTER, you can determine the number of array elements that satisfy a condition.
Follow the links in the paragraph above to access more information about these functions and properties. Our newly-revamped documentation includes lots of examples, making it easy to get started with the new features.
Note: When you enter formulas that use arrays and ranges in Calcapp Creator, there is no need to press any special keys like Ctrl + Shift + Enter. That particular key combination is required by older versions of Microsoft Excel, but does not apply to Calcapp.
Ranges
In spreadsheets, a range refers to a part of the spreadsheet grid,
like A1:B10, which is a
range encompassing the cells A1 and B10 and all the cells that appear
between them:
Spreadsheets differentiate between ranges and array constants. Many spreadsheet functions (like SUMIF) only work with ranges and not array constants, whereas others happily work with both (like XLOOKUP). We find that confusing, and you’ll be happy to learn that Calcapp does not make a distinction between arrays and ranges.
Moreover, Calcapp is an app builder and does not have a spreadsheet grid, so defining a range as a collection of cells does not work for us.
So what is a range in Calcapp? A range is just an easy short-hand for constructing arrays. In fact, ranges and arrays look the same to Calcapp internally.
To make sense of this, consider a form screen which consists of exactly six fields, Field1, Field2, Field3, Field4, Field5 and Field6, in that order.
This formula returns the sum of all of these fields:
Can we make this shorter? Yes we can, using ranges:
Ranges rely on the order of your form screen elements. The range above formally contains Field1, Field6 and all elements that appear between them.
To drive the point home that ranges are just arrays, this formula also works:
In fact, you can combine ranges and individually-named elements. This formula returns the sum of Field1, Field2, Field3, Field4 and Field6:
Ranges are an effective means of creating arrays containing many elements. However, it is easy to lose track of what elements they contain, which can have surprising results. This post explores this topic in depth.
Most functions now accept arrays
There are many functions which explicitly require their parameters to be arrays, like COUNTIFS and SUMPRODUCT. What about all the regular functions that Calcapp has supported since its inception, like ABS, DATE, NPV and COS, surely they don’t support arrays?
They do, actually, and there are very few that don’t. Consider these formulas:
The first formula returns the absolute value of -4, 4. The second formula returns the array { 4, 4 }{ 4; 4 }.
When a function like ABS, which doesn’t natively support arrays, is given an array, Calcapp’s formula engine invokes ABS once for every array element and collects the results in an array. That makes these formulas equivalent:
Microsoft pioneered this behavior with Excel 2021 (though the feature was available earlier to Microsoft 365 subscribers). It is known as lifting.
Consider these DATE formulas:
The first formula returns the date November 14, 1981. The second formula returns an array consisting of the dates November 14, 1981 and May 14, 1981. The third formula returns an array consisting of the dates November 14, 1981 and May 28, 1981.
The second formula above uses an array for the month parameter. This causes the formula engine to invoke the DATE function twice, first pairing 1981 with 11 and 14, and then pairing 1981 with 5 and 14. That makes these formulas equivalent:
The third formula, DATE(1981, { 11, 5 }, { 14, 28 })DATE(1981; { 11; 5 }; { 14; 28 }), uses arrays for both the month and day parameters. They both contain two elements, causing the formula engine to invoke the DATE function twice, first pairing 1981 with 11 and 14, and then pairing 1981 with 5 and 28. That makes these formulas equivalent:
(What happens if you pass arrays that don’t have the same number of elements? The returned array will contain not available values.)
The documentation
for ABS (click Details) states that ABS returns
Number or
{ Number } (a number or an array of numbers). That’s
how you spot a function in our documentation that returns an array if
one or several parameters are arrays — we call them array-capable
functions.
(Technically, an array-capable function returns an array if one or several of its parameters, which are documented to either accept a regular value or an array, are invoked with arrays.)
Explore all the examples in our formula and property documentation to find lots of helpful tips on where you can use arrays with regular functions to great effect.
Arrays and matrix functions
Spreadsheets and Calcapp alike support specialized math functions for handling matrices, such as MMULT. A matrix is a two-dimensional array, which is an array whose elements are arrays. A matrix can be visualized as a two-dimensional area containing values, in contrast to a regular, one-dimensional array which can be visualized as a single line containing values.
Matrices are written differently in Calcapp compared to most
spreadsheets. For instance, =MMULT({ 3, 5; 1, 2 }, { 2, 6;
1, 1 }) (written =MMULT({ 3; 5\ 1; 2 }; { 2; 6\
1; 1 }) with decimal commas) is written like this in Calcapp:
Calcapp’s syntax for two-dimensional arrays is closer to what traditional programming uses than the syntax used by spreadsheets. We designed it this way as we prefer to use semicolons to separate statements in action formulas, which will be the focus of our next release.
Further reading on arrays
There are another four blog posts describing our new array support: