Array ranges, SUMIF and more are coming soon

Ranges will enable authors to easily and concisely refer to multiple values in formulas. Ranges, combined with the new function SUMIF, will make conditionally summing values much easier than before.
1 + 21 + 2

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.

« Tip: Share previews of apps Decimal commas in formulas and better documentation are coming soon »