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.

In case you need a refresher on the basics of the Calculator widget, please head over to this article - Getting Started with the Calculator.

In this article

Working with Formula Editor

To begin crafting your first formula, add a new Calculation on the Build tab → Add Calculation:

Then start writing your formula in the Formula editor:

The editor provides you with distinct building blocks arranged in two rows, each color-coded for clarity:
1
Fields act as variables that dynamically fetch values from their corresponding fields. They are selected from the dropdown. When added, they are depicted in purple:

For a more in-depth overview of working with Fields, refer to this section of the article - Using Fields in Formulas.

Remember, you must select a Field from the dropdown menu. Even if you enter the field name correctly using your keyboard, the formula won't validate.
2
Calculations also function as variables, retrieving values from their respective calculations. They are represented in green:

You can find a more detailed guide on using calculations in this article section - Simplifying Formulas with Calculations.

Keep in mind that you must select a Calculation from the dropdown menu. Even if you enter the field name correctly using your keyboard, the formula won't validate.
3

Functions are tools used to perform specific operations or calculations on data. They can be selected from the dropdown menu. The names of functions and syntax elements, like round brackets and commas, are displayed in white:

The article section titled Calculator Functions provides detailed guidance on how to use the functions available.

4

Some Mathematical operators such as addition, subtraction, multiplication, division, and exponentiation, along with round brackets, are available as buttons in the bottom row. They appear in white:

The editor follows the standard mathematical order of operations: Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction.

For example, this formula will yield a result of 7:

However, adding round brackets to the formula, like this, will change the result to 9:

5

Additionally, you can input numbers directly from your keyboard, which are highlighted in brown:

Numeric values can also be expressed as decimals (e.g., 0.01), or as percentages (e.g, 1%).

You'll notice that writing formulas in the widget is similar to Excel, but instead of cell data, you work with Fields and Calculations.

To assist you in writing accurate formulas, the widget also includes a built-in validator. Simply click outside of the Formula editor to activate it. If there's an issue with your formula, it will display an Invalid Formula error message.

The validator catches the following errors:

  • Syntax mistakes in the formula:
  • Use of a deleted Field or a deleted nested Calculation:
  • Use of an invalid nested Calculation:
  • Use of an unrecognized Field / Calculation variable entered from keyboard:

Be sure to carefully review your formula to correct any errors.

For guidance on styling the Calculation element of the widget, check out this article section - Adding Calculations.

Now, let's take a closer look at the Field component.

Using Fields in Formulas

When you choose a Field, you're selecting a variable that dynamically fetches the value of the user's response from that particular field:

A Field can be used as a variable both in mathematical operations and functions. Here's a simple example demonstrating the use of Field variables, first as an addend and then as arguments of a function:

Using Fields can get a bit tricky with Choice and Image Choice fields when Multiple Selection is enabled:

In the next section, we'll delve deeper into these nuances.

Peculiarities of Choice Fields with Multiple Selection

A Choice field with Multiple Selection works well with functions MAX and MIN, which can handle multiple arguments. However, when used in mathematical operations or with functions that take a limited number of arguments, you should first put this field inside the SUM or CONTAINS function.

With Multiple Selection, users are presented with checkboxes, allowing them to select multiple options. Because of this, a Choice field might store an array of numbers instead of just a single number. Let’s imagine you have a Choice field with three checkboxes:

  • Option 1 with a value of 1
  • Option 2 with a value of 2
  • Option 3 with a value of 3

When nothing is selected, the result shows 0: If a user selects just one option, let's say Option 1, it's straightforward - the Calculator simply uses the assigned value of that option (in this case, 1): However, when multiple options are selected in a Choice field, the Calculator cannot determine which value to use, resulting in a calculation failure: To avoid this issue, a Choice field with Multiple Selection is used within the SUM or CONTAINS function.

To better understand how to use these functions, let’s look at a practical example. We'll design a shipping cost calculator where the Product Type is a Choice field with Multiple Selection. The product type values are:

  • Standard: $0
  • Fragile: $10
  • Perishable: $5
  • Hazardous: $15

The base shipping cost is $2 per kg, calculated simply as Weight * 2. Additionally, a surcharge fee is determined by the type of product being shipped.

Here's what the widget will look like:

Let's look at three ways to calculate the additional fee, highlighting special cases where you can use SUM or CONTAINS with Multiple Selection:

1
Using CONTAINS to check if a certain option has been selected

Within the CONTAINS function, the first argument should be the Choice field, and the second argument is the value you're testing. Remember, CONTAINS can only be used within an IF function. Don't forget to specify what values to display when the IF function returns True and False. The template of the formula is IF(CONTAINS(Choice, value to test), value if true, value if false).

For example, we want to increase the shipping cost by 25% if the shipment includes hazardous products. We can use the formula (Weight * 2) * IF(CONTAINS(Product Type, 15), 1.25, 1). This formula multiplies the base shipping cost by 1.25 if hazardous products are included; otherwise, it leaves the base cost unchanged:

You might think that using (Weight * 2) * IF(Product Type == 15, 1.25, 1) would be the right formula for this scenario.

However, the logical check in the format IF(Product Type == 15, 1.25, 1) only works if Multiple Selection is disabled. In our example Multiple Selection is enabled, so the CONTAINS function needs to be used instead.

2
Using SUM to calculate the sum of all selected options

To get the sum of all checked options, input the desired field as the argument of the SUM function: SUM(Choice).

In our example, we'll now add a flat fee to the total cost based on the values of the selected fields. We can achieve this using the formula (Weight * 2) + SUM(Product Type):

3
Using SUM to test whether any option has been checked

To check if the user has selected at least one option, we can place the SUM function as the first argument of the IF function: IF(SUM(Choice) > 0, value if true, value if false). If the user doesn't pick anything, SUM(Choice) returns 0 by default. So, we compare this sum with 0. If the sum is greater than 0, it means at least one option has been checked.

Let's rename the Choice field from Product Type to Special Product Type and remove Standard from the options since Standard products don't have any fees. We'll add a flat fee of $10 if at least one Special Product Type is selected. If none are selected, the IF function returns 0, and no fee is added.

The formula for this would be (Weight * 2) + IF (SUM(Special Product Type) > 0, 10, 0):

Simplifying Formulas with Calculations

If your formula is becoming large and complex, simplify it by breaking it down into smaller parts! To do this, create a separate Calculation: Then, use this Calculation as a variable in another formula to streamline and clarify your overall calculation process. Let's go through a practical example. We'll create a basic deposit calculator. The user enters the deposit amount and the term. The calculator will show two results:

  • the total amount to be received at the end (including the initial deposit and interest earned)
  • the gained interest

Here’s what the widget will look like: To simplify the formula, we divided it into two calculations. In the first one, we calculate the total amount to be received at the end of the term Total Amount Receivable, and in the second one, we calculate the gained interest Interest Receivable. To calculate Total Amount Receivable, create the first Calculation. In our example, we'll use the future value formula for simple interest, like this: The future value formula for simple interest is FV = PV * (1 + r * t), where:

  • FV is the future value of the investment
  • PV is the present value or principal amount
  • r is the annual interest rate (expressed as a decimal)
  • t is the time the money is invested for in years

In our example, this formula is written as Principal Amount * (1 + (2% * Term in years)), where Principal Amount and Term in years are Slider fields. Next, create another Calculation for Interest Receivable. To calculate the interest, subtract Principal Amount from another Calculation - Total Amount Receivable: All done! The advantages of simplifying formulas may not be immediately noticeable in a short formula like in this example. However, the formula Interest Receivable is already easier to use, read, and edit.

Calculator Functions

Now that you've grasped the basics of working with formula building blocks, let's delve into one of the most powerful tools in our widget's arsenal - functions. Our Calculator supports 14 powerful Functions that you may know from doing basic calculations in spreadsheets:

Be sure to read the article all the way to the end to learn more about them, or simply follow the links above to skip ahead to the function references.

You can use numeric values, Fields and Calculations as function arguments.

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. Commas are used to separate multiple values:
Syntax Input Output
MAX(value1, [value2], ...) MAX(3, 4, 1) 4
2

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

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

ROUND - returns a number rounded to a given number of decimal places. 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:

Syntax Input Output
ROUND(value) ROUND(123.32342) 123
ROUND(value, [places]) ROUND(123.32342, 2) 123.32
If you don't specify the number of places, the function rounds to the nearest whole number.
4

ROUNDUP - returns a number rounded up to a given number of decimal places:

Syntax Input Output
ROUNDUP(value, [places]) ROUNDUP(123.32342) 124
If you don't specify the number of places, the function rounds up to the nearest whole number.
5

ROUNDDOWN - returns a number rounded down to a given number of places:

Syntax Input Output
ROUNDDOWN(value, [places]) ROUNDDOWN(123.32342) 123
If the number of places isn't specified, the function rounds down to the nearest whole number.
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:

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

RAND - returns a random number between 0 and 1:

Syntax Input Output
RAND() RAND() Random value between 0 and 1, for example 0.3
Note that this function doesn't take any arguments.
8

RANDBETWEEN returns a random integer between two given numbers:

Syntax Input Output
RANDBETWEEN(min value, max value) RANDBETWEEN(2, 5) Random value between 2 and 5, for example 4
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:

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

As you may recall from the previous section, SUM is particularly handy when it takes a Choice or an Image Choice field as an argument. You can find more information on this topic by following this link - Peculiarities of Choice Fields with Multiple Selection.

Writing Conditions

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 first comma if the result is True, and the value after the second comma if the result is False:

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

Keep in mind that 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.

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

Let's take a look at how you can expand the capabilities of the IF function.

2

AND - returns True only if both results are True, and returns False otherwise:

Syntax Input Output
IF(AND(logical test 1, logical test 2), value if true, value if false) IF(AND(2==2, 1==1), 0, 1) 0
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:

Syntax Input Output
IF(OR(logical test 1, logical test 2), value if true, value if false) IF(OR(2==3, 1==1), 0, 1) 0
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. When given False, NOT returns True:

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

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

Syntax Input Output
IF(CONTAINS(set of values, value to test), value if true, value if false) IF(CONTAINS([1, 2, 3], 50), 0, 1) 1
This is a unique Calculator widget function not present in Excel. Use a Choice field as the argument of CONTAINS to check if a particular option has been selected.

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


If you have any new features in mind or somethings doesn't work as expected, do make a post in our Wishlist! We'll consider every idea and do our best to implement it 🙌

You're also welcome to check this category on our forum where you can find customization ideas, ask questions, share your feedback and tips.

Did this article answer your question? Share your feedback: Thanks for sharing your feedback. It helps us grow! There was a problem submitting your feedback. Please try again later.

Still looking for assistance? Contact Us Contact Us