Feature: Use LET to avoid repetition in formulas

The new function LET enables you to assign names to values for use inside a single formula. LET can make formulas shorter and easier to read and can help them run faster.

Our new release introduces support for the LET function, which 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:

LET(Area := Width * Height, Cost := Area * 1500, Cost + 1000)LET(Area := Width * Height; Cost := Area * 1500; Cost + 1000)

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.

LET is also useful when you want your formulas to be self-documenting, so that other people can more easily determine how they work.

If you need to assign names to values that are accessible from any formula, use named values instead (introduced here).

LET works particularly well with action formulas (formulas run in response to buttons being pressed, another new feature). Refer to our full documentation for more on that topic.

LET in Microsoft Excel

Microsoft Excel introduced support for LET back in 2020. Their version differs from the Calcapp version in that you need to use ,; instead of := to separate names from values.

Being able to use := is a Calcapp extension, which we think makes formulas easier to read. You can use ,; instead of := if you like. This may be useful if you copy and paste formulas between Excel and Calcapp Creator.

LET and arrays

The values you use with LET can be of any type, including arrays. This formula returns the array elements that are greater than 3:

LET(Array := { 1, 2, 3, 4, 5 }, FILTER(Array, Array > 3))LET(Array := { 1; 2; 3; 4; 5 }; FILTER(Array; Array > 3))

The version without LET would involve repeating the array:

FILTER({ 1, 2, 3, 4, 5 }, { 1, 2, 3, 4, 5 } > 3)FILTER({ 1; 2; 3; 4; 5 }; { 1; 2; 3; 4; 5 } > 3)

You can also filter an array without repeating it, and without using LET, using a feature specific to Calcapp:

FILTER({ 1, 2, 3, 4, 5 }, Element > 3)FILTER({ 1; 2; 3; 4; 5 }; Element > 3)

Be sure to check out our full documentation for more on LET, including lots of formula samples.

« Feature: Named values Feature: A grab bag of new features »