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.