# Advanced formulas

Basic knowledge of Calcapp formulas goes a long way in enabling you to build featureful apps. However, there are some advanced techniques that make you more productive, or enable you to realize things that would otherwise not have been possible. This guide explores these techniques.

## Arrays and ranges

Let’s say that you want to calculate the sum of five fields. You add the
following fields to a form screen named *MyFormScreen*, in this
order: *Field1*, *Field2*, *Field3*, *Field4*
and *Field5*.

These formulas both get the job done:

The formulas above are fairly verbose. Calculating the sum of several hundred fields in this way wouldn’t be workable.

The SUM function
can also be invoked with an *array*, which is a value that
contains many other values (*elements*). They are written between
curly braces.

This array holds the values 1, 2 and 3:

You may recognize this as an *array constant* from spreadsheets.
Arrays in Calcapp are more powerful, though, as you can use any
calculations to derive the array elements and reference any values in
your app.

This array is also valid:

Returning to SUM, here’s how we can calculate the same sum by passing an array:

This formula is even longer, though. Is there a way to make it shorter?

Yes, using *ranges*:

In Calcapp, a range is simply an easier way to write an array. The range
above includes *Field1*, *Field5* and all fields that
appear between them in your form screen.

To illustrate that ranges are just arrays, this formula also works:

This formula calculates the sum of the values of *Field1*,
*Field3*, *Field4* and *Field5*:

Use IF to determine if an element should be part of an array:

In the formula above, *Field1* is only part of the array if its
value is greater than 5.

Use | to join arrays together with other arrays or values and SIZE to return the number of elements in an array.

## Accessing fields and other items as arrays

All the fields that belong to a form group, a form screen or the entire
app can be accessed as a single array. The five fields of
*MyFormScreen*, for instance, can be accessed through the
Items property.

If only those five fields are part of *MyFormScreen*, the
following formulas are equivalent:

The chief advantage to using *Items* instead of a range is that
you no longer need to worry about updating the formula when you add more
fields.

The *Items* property can often be accessed without typing it
explicitly. These equivalent formulas return the sum of all fields of
*MyFormScreen*:

This formula returns whether all fields of *MyFormScreen* are
valid:

If this formula is attached to the BackgroundColor property of a screen, it — and possibly the screens that follow it — turns red if a single field of the entire app is invalid:

If this formula is associated with the Enabled property of a
button, it only becomes enabled when
all fields of *MyFormScreen* have been filled out:

Above, we need to spell out MyFormScreen.ItemsMyFormScreen,Items, instead of just referencing MyFormScreenMyFormScreen. The reason is that ISDEFINED(MyFormScreen)ISDEFINED(MyFormScreen) applies to the form screen itself, and not its items. As the form screen is always defined, that formula would always return TRUE.

## Calcapp-specific functions and extensions

We support a large number of functions specific to Calcapp, including color functions, FORMATNUMBER, PARSENUMBER and REGEXMATCH. We also support extensions to existing functions, making them more powerful or making formulas using them easier to read and write.

SUMIF calculates the sum of the given values, but only considers values which pass a test you supply. This formula returns the sum of all field values which are greater than 5:

This formula returns the sum of all field values which are greater than
the value of *Field1* (using the & operator, which joins
text strings together):

If you need multiple conditions, which all must pass, the SUMIFS function is available.

However, what if you need more flexibility? Let’s say that you want to return the sum of all field values which are either greater than 5 or are odd numbers. This can’t be handled by either SUMIF or SUMIFS.

A Calcapp extension allows you to use a *formula fragment* instead
to determine which numbers should be considered. A formula fragment is a
regular part of a formula, but with access to special values.

SUMIF makes the array element under consideration available under the
`Element`

name.
These two formulas are equivalent:

By using a formula fragment, you have access to the full power of the formula language. Expressing “greater than 5 or is an odd number” is easy with a formula fragment:

We also offer extensions that make formulas easier to read and write. To illustrate this, consider the SORT function, which returns a sorted version of an array.

This formula returns the sorted array { 1, 2, 3 }{ 1; 2; 3 }:

To sort in descending order instead (where greater values appear first), -1 can be given as the second parameter.

This formula returns the sorted array { 3, 2, 1 }{ 3; 2; 1 }:

As a Calcapp extension, you can specify the sort order using text:

While longer, using text instead of a hard-to-remember number can help make formulas clearer. XLOOKUP, XMATCH and FORMATDATE are other functions which use a similar system.

## The LET function

The LET function enables you to write shorter, more descriptive formulas that run faster.

At its core, LET makes it possible to assign names to values, which can be used in calculations that appear to the right in the same formula.

The best way to demonstrate this is with an example:

The formula above first multiplies the values of the *Width* and
*Height* fields together and assigns the name *Area* to the
resulting value. It then multiplies this value by 1500, assigning it the
name *Cost*. Finally, it adds 1000 to the *Cost* value and
returns the sum.

The formula above can also be written (Width * Height * 1500) + 1000(Width * Height * 1500) + 1000, which is significantly shorter, but isn’t as descriptive.

LET is primarily useful when you need to repeat formula fragments in a formula, allowing you to assign a single name to the repeating part and then refer back to it. That can make formulas shorter and make apps perform better, as it gives Calcapp less work to do.

To learn more about Calcapp, including in-depth tips and detailed looks at our features, read our blog »