Working with Formulas in Calculator widget

At the heart of the Calculator widget are formulas, which allow you to perform complex mathematical operations on user data.

In this guide, we'll cover the basics of making formulas. If you've used Excel or similar spreadsheet programs before, you'll find it especially easy to follow along.

Working with Formulas

To start writing your first formula, add a new Calculation in the Build tab → Add Calculation:

1

Choose a Field variable to pull the value of the user response from that field. Fields can be used in the same way as numbers in mathematical operations such as addition and multiplication. You can also pass them as arguments in the functions.

Click on the Field dropdown to open the list of available fields:

When using Choice and Image Choice fields with Multiple Selection enabled, please keep in mind three special rules of writing formulas:

1. To test whether a particular option has been selected, you need to use the CONTAINS function. The first argument within the CONTAIN function needs to be a Choice field and the second - value you're testing, for example, IF(CONTAINS(Choice, 1), 1, 0). An expression IF(Choice == 1, 1, 0) will not produce the right result if more than one option has been selected.
2. You can test whether any option has been checked in the Field by using the SUM function. Just make sure the values of options in this Field are different from 0. Then you can use the IF function and compare the sum of all selected options to 0. For example, IF(SUM(Choice) > 0, 1, 0).
3. And finally, use the SUM function to get the sum of all selected options. Enter the desired Field as the argument, for example SUM(Choice).

2

Use a Calculation variable to pull up data from another Calculation.

If your formula is getting big and complicated, simplify it by factoring out parts of it! For this, create a separate Calculation, enter the factored-out formula and use it later as a variable in your main formula.

For example, let's say we've created 3 separate calculations to get the costs of different types of products (Breakfast, Lunch, and Dinner):

Now you can select them from the Calculation dropdown in the main formula that calculates the total cost of the order. As a result, the main formula is easier to read and edit:

Using Mathematical Functions

First, we'll go over 9 mathematical functions that allow you do all sorts of standard calculations.

1

MAX - Returns the largest value in a set of values. When using multiple values, separate them by commas. For example:

Syntax	Input	Output
MAX(value1, [value2], ...)	MAX(3, 4, 1)	4

Enter the values from your keyboard or select a Field/Calculation as an argument:

2

MIN - Returns the smallest value in a set of values. Use commas to separate multiple values. For example:

Syntax	Input	Output
MIN(value1, [value2], ...)	MIN(3, 4, 1)	1

Enter the values from your keyboard or select a Field/Calculation as an argument:

3

ROUND - Returns a number rounded to a given number of decimal places. If you don't specify the number of places, the function rounds to the nearest whole number. Note that the function rounds the number up if the last significant digit is 5 or greater and rounds it down if the last significant digit is less than 5. For example:

Syntax	Input	Output
ROUND(value)	ROUND(123.32342)	123
ROUND(value, [places])	ROUND(123.32342, 2)	123.32

Enter the values from your keyboard or select a Field/Calculation as an argument:

4

ROUNDUP - Returns a number rounded up to a given number of decimal places. If you don't specify the number of places, the function rounds up to the nearest whole number. For example:

Syntax	Input	Output
ROUNDUP(value, [places])	ROUNDUP(123.32342)	124

Enter the values from your keyboard or select a Field/Calculation as an argument:

5

ROUNDDOWN - Returns a number rounded down to a given number of places. If the number of places isn't specified, the function rounds down to the nearest whole number. For example:

Syntax	Input	Output
ROUNDDOWN(value, [places])	ROUNDDOWN(123.32342)	123

Enter the values from your keyboard or select a Field/Calculation an argument:

6

ABS - Returns the absolute value of a number. It means that negative numbers will be converted to positive numbers, and positive numbers will remain the same. For example:

Syntax	Input	Output
ABS(value)	ABS(-123)	123

Enter the values from your keyboard or choose a Field/Calculation as an argument:

7

RAND - Returns a random number between 0 and 1. For example:

Syntax	Input	Output
RAND()	RAND()	Random value between 0 and 1, for example 0.3

Please note that this function doesn't take any arguments:

8

RANDBETWEEN - Returns a random integer between two given numbers. For example:

Syntax	Input	Output
RANDBETWEEN(min value, max value)	RANDBETWEEN(2, 5)	Random value between 2 and 5, for example 4

Enter the values from your keyboard or choose a Field/Calculation as an argument:

9

SUM - Returns the sum of values supplied. Enter your values manually or select a Choice field as an argument. Don't forget to separate multiple values by commas. For example:

Syntax	Input	Output
SUM(value1, [value2], [value3], ...)	SUM(1,1,1)	3

As you may remember from the Working with Formulas section, SUM is particularly useful when it takes Choice or an Image Choice field as an argument. Let's remember these two tips.

First, SUM provides a simple way to get the sum of all selected options in a Choice field, like so:

Second, SUM can be used to check if at least one option has been checked in a Choice field. Just make sure the values of options are different from 0 compare the sum of all selected options to 0. For example:

Using Logical Functions

Logical functions allow you to test whether a condition is true or false. Depending on the result of the test, you can choose what action to perform next.

The Calculator widget supports 5 logical functions:

1

IF - Runs a logical test on supplied values or expressions and returns the value after the 1st comma if the result is True, and the value after the second comma if the result is False. For example:

Syntax	Input	Output
IF(logical test, value if true, value if false)	IF(true, 200, 0)	200

Use a combination of numbers and Fields/Calculations to compose your IF function:

Other logical functions AND, OR, NOT and CONTAINS, as well as comparison operators (>, <, ==) can only be used within the IF function. When other logical functions are nested within IF, they allow you to test more than one condition at a time. Let's take a look at how you can expand the capabilities of the IF function.

Note that the equality operator in the Calculator widget is double equals ==, and not a single equal = as in Excel.

2

AND - Returns True only if both results are True, and returns False otherwise. For example:

Syntax	Input	Output
IF(AND(logical expression 1, logical expression 2), value if true, value if false)	IF(AND(2==2, 1==1), 0, 1)	0

Use a combination of numbers and Fields/Calculations to compose your AND function:

Unlike Excel, AND function in the Calculator widget accepts only 2 arguments.

3

OR - Returns True if any given argument is True, and returns False if both arguments are False. For example:

Syntax	Input	Output
IF(OR(logical expression 1, logical expression 2), value if true, value if false)	IF(OR(2==3, 1==1), 0, 1)	0

Use a combination of numbers and Fields/Calculations to compose your OR function:

Unlike Excel, OR function in the Calculator widget accepts only 2 arguments.

4

NOT - Returns the opposite of the result of the comparison. When given True, NOT returns False, and when given False, NOT returns True. For example:

Syntax	Input	Output
IF(NOT(logical expression), value if true, value if false)	IF(NOT(2==3), 0, 1)	0

Use a combination of numbers and Fields/Calculations to compose your NOT function:

5

CONTAINS - Returns True if the set of provided values contains the value specified after the comma, and returns False otherwise. For example:

Syntax	Input	Output
IF(CONTAINS(set of values, value), ,value if true, value if false)	IF(CONTAINS([1, 2, 3], 50), 0, 1)	1

Use a Choice field as the argument of your CONTAINS function to check if some particular option has been selected:

This is a unique Calculator widget function not present in Excel.

Now you know how to use functions and fields in formulas to build your first Calculator!