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 start creating your first formula, add a new Calculation on the Build tab → Add Calculation:

The easiest way to create an accurate formula is by using the ✨ Generate with AI tool, which lets you describe complex math in natural language:

To use the ✨ Generate with AI tool in the Formula editor, create all the necessary fields first - Adding Calculator Fields. To create the entire widget using AI, check out this guide - Easy Start with Templates and AI Calculator Generator.

Simply click the ✨ Generate with AI button in the Formula editor → enter your prompt → click Generate:

The example above uses this prompt to calculate the shipping cost:

  • If the weight is less than 5kg, the cost is $1 per km.
  • If the weight is more than 5kg, the cost is $1.5 per km.
  • If the weight is more than 10kg, the cost is $1 per kg per km.

And the resulting formula is: IF(Weight <= 5, Distance * 1, IF(Weight > 5 AND Weight <= 10, Distance * 1.5, Weight * Distance)).

If you prefer to write the formula yourself, start typing 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:

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 orange

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 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 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.

Using Choice Fields with Multiple Selection

A Choice field with Multiple Selection works well with functions like MAX and MIN, which handle multiple arguments. However, for mathematical operations or functions that accept a limited number of arguments, you should first wrap this field inside the SUM or CONTAINS function.

With Multiple Selection, users see checkboxes, allowing them to select multiple options. Because of this, a Choice field stores an array of numbers instead of 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: However, when a user selects multiple options, the calculation will fail and no result will be shown:

To avoid this issue, use a Choice field with Multiple Selection within the SUM or CONTAINS function.

To 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

The CONTAINS function checks if a specified value is selected in a Choice field. Use it within an IF function to display different values based on the result. The formula template is IF(CONTAINS(Choice, value to test), value if true, value if false).

For example, to increase the shipping cost by 25% if hazardous products are included, use this formula: (Weight * 2) * IF(CONTAINS(Product Type, 15), 1.25, 1). This multiplies the base cost by 1.25 if hazardous products are selected:

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. If Multiple Selection is enabled, the CONTAINS function must be used.

2
Using SUM to calculate the sum of all selected options

To sum all selected options, use the SUM function: SUM(Choice).

For example, we can add a flat fee to the total cost based on selected fields using (Weight * 2) + SUM(Product Type):

3
Using SUM to test if at least one option has been checked

To check if at least one option is selected, use SUM within an IF function: IF(SUM(Choice) > 0, value if true, value if false). If no option is selected, SUM(Choice) returns 0.

Let's rename the Choice field from Product Type to Special Product Type and remove Standard as it has no fee. We'll add a $10 fee if any Special Product Type is selected using the formula (Weight * 2) + IF(SUM(Special Product Type) > 0, 10, 0):

Now that we've covered fields, let's learn how to handle complex calculations.

Using Formulas as Variables for Simplified 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 + 0.02 * 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:

Read the entire article for more details, or use the links above to go directly to the function references. The function references include each function’s purpose, syntax examples, and widget screenshots.

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) ROUNDUP(123.32342) 124
ROUNDUP(value, [places]) ROUNDUP(123.32342, 2) 123.33

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) ROUNDDOWN(123.32342) 123
ROUNDDOWN(value, [places]) ROUNDDOWN(123.32342, 2) 123.32

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 - Using 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