We have supported formula functions like SUM and PRODUCT for a long time now. What they have in common is that you supply multiple parameters and get a single result back.
These formula functions are used frequently by Calcapp authors, but provide
little value beyond adding or multiplying numbers together using + and *.
The formulas SUM(Field1, 4) and Field1 + 4 do the exact same thing, which is
also true for the formulas PRODUCT(4, 8, 16) and 4 * 8 * 16.
Functions like SUM and PRODUCT are about to become significantly more useful, though, with the introduction of ranges. If you have more than a passing familiarity with spreadsheets like Microsoft Excel, you have definitely encountered ranges (whether or not you know them by that name).
In Excel, if cells A1 through A5 contain values you want to add together, you
can easily do so using the formula =SUM(A1:A5). By contrast, Calcapp has
historically forced you to enumerate all the values you wanted to add together,
by writing something like SUM(A1, A2, A3, A4, A5) or A1 + A2 + A3 + A4 + A5.
(Calcapp doesn’t require you to write = before formulas.) Compared to using
ranges, that’s a lot more work.
The good news is that our upcoming release will support ranges. If you have five
number fields named A1, A2, A3, A4 and A5 (appearing one after
another), adding their values together will be as easy as using the formula
SUM(A1:A5).
Calcapp authors don’t typically name their fields as though they were cells in a
spreadsheet grid. Rather, fields are automatically named using their labels as a
guide, so if you use the label “Maximum weight” for a field, the field is
assigned the name MaximumWeight automatically. (To break the association
between the label and the name, simply type a new name manually in the name
field, to the left of the formula bar.) As such, ranges in Calcapp formulas will
typically look more like SUM(MaximumWeight:IndividualWeight) than
SUM(A1:A5).
The formula SUM(MaximumWeight:IndividualWeight) will add all values together
that belong to not only the fields MaximumWeight and IndividualWeight, but
all fields that appear between them in the app designer.
Under the hood
In a spreadsheet, the cell reference A1 refers to the cell in the first column
and the first row of the spreadsheet grid and A2 refers to the cell in the
first column and the second row. The range A1:A2 refers to both of these
cells.
Spreadsheets also support something called arrays, which are similar to ranges
in that they hold multiple values. An array constant in a spreadsheet is
written between curly brackets, like { 2, 4 }, which is a value holding the
values 2 and 4. (As we have discussed earlier in this space,
we are adding support for arrays to Calcapp.)
Ranges and arrays are subtly different in spreadsheets. In Calcapp, there is no
spreadsheet grid, so a range and an array are one and the same. If Field1,
Field2 and Field3 appear one after the other in the app designer, the
formulas SUM(Field1:Field3) and SUM({ Field1, Field2, Field3 }) not only
return the same result, they look roughly identical to Calcapp. As such, a range
is simply a more concise way of writing an array.
An example
What if you have five fields (A1 through A5, appearing one after the other)
and you want to add their values together, but only those that are greater than
two? This has been possible with Calcapp ever since our first release, but you
had to use a long and cumbersome formula:
IF(A1 > 2, A1) + IF(A2 > 2, A2) + IF(A3 > 2, A3) + IF(A4 > 2, A4) + IF(A5 > 2,
A5)
(This formula would become even more unwieldy if you had, say, 100 fields instead of five.)
Spreadsheets make this considerably easier. The standard way to solve this is through the SUMIF formula function:
SUMIF(A1:A5, ">2")
Calcapp currently supports neither arrays nor ranges, explaining why we haven’t been able to support the SUMIF function earlier. This issue will be rectified by our next release, which will add support for not only SUMIF, but also SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MINIFS and MAXIFS.
As teased earlier, Calcapp will not only support the formula
SUMIF(A1:A5, ">2") (where the ">2" condition — determining which values
are considered – is expressed as a text string), but also an alternate form
which can be written like so:
SUMIF(A1:A5, Item > 2)
The second parameter is technically known as a lambda (or an anonymous
function), which returns a logical value (TRUE or FALSE), determining whether a
value should be considered. To help this lambda make this decision, the value
under consideration is available as Item. (The name can be changed, but that’s
a topic for another blog post.)
Lambdas enable you to use complicated logic to determine whether a value should be considered:
SUMIF(A1:A5, (Item > 2) && (Item < 10) && ISODD(Item)
The example above only considers values which are greater than two, are less than ten and are odd. (In other words, only 3, 5, 7 and 9 are considered.)
Modern Excel and dynamic arrays
One of the most exciting changes to come to Microsoft Excel are dynamic arrays, along with a number of new formula functions. Dynamic arrays make it much simpler to perform actions like filtering an array (returning a copy of said array only containing elements satisfying a certain condition with the FILTER function) and removing duplicates from an array (with the UNIQUE function).
We’re happy to announce that we will have full coverage of Microsoft’s new dynamic array functions when our next release becomes available.
Let’s return to the SUMIF(A1:A5, ">2") example above. If you think about it,
there are two things going on here. First, the array consisting of the A1:A5
values is filtered to only contain values that are greater than two. Second,
these values are added together and returned.
Filtering the array can be accomplished using the new FILTER function, and summing them can be done using the SUM function. Here’s what that looks like:
SUM(FILTER(A1:A5, A1:A5>2))
FILTER is asked to filter the A1:A5 range, passed as the first parameter. The
second parameter is also an array, with the same size as the first parameter.
However, it is a logical array, consisting of TRUE and FALSE values. A1:A5>2
returns an array consisting of TRUE values where the value is greater than two,
and FALSE values elsewhere. That means that { 1, 2, 3, 4 }>2 returns { FALSE,
FALSE, TRUE, TRUE }, because only 3 and 4 are greater than two.
SUM(FILTER(A1:A5, A1:A5>2)) may look more cumbersome than SUMIF(A1:A5, ">2")
— and it is — but it provides far more flexibility. Which you wind up using
depends on your needs and on your preferences.
When will this become available?
We’re in the last stages of our big formula revamp (which is the first step of our next-generation Calcapp 4 project). We hope that our next release, containing these features, will see the light of day in March.