Formula Operators in Calculators
When creating a calculator, you have two options for building out a formula:
- Simple formula builder
- Excel style table
The simple formula builder essentially operates as a single Excel cell, so anything you can achieve with a single cell you can achieve with that.
The Excel style builder givers you access to an entire table, where you can import reference data and select a desired results cell.
Basic Operators
Here's a list of these basic operators:
| Condition | Operator | Formula example | Value example | Result |
| Add Find the sum of two or more values. |
+ | Q_1300 + Q_1301 | 100+45 | 145 |
| Minus Subtract a value from another. |
- | Q_1300 - Q_1301 | 200-80 | 120 |
| Divide Divide a value by another value. |
/ | Q_1300 / Q_1301 | 100/20 | 5 |
| Multiply Multiply a value by another value. |
* | Q_1300 * Q_1301 | 5*30 | 150 |
| Percent Express a value as a percentage. |
% | Q_1300% | 30% | 0.3 |
| Power Multiply a value by itself a specified number of times. |
^ | Q_1300 ^ 1301 | 3^2 | 9 |
| Grouping Define operations that should be performed first. |
( ) | Q_1300 * (Q_1301 + Q_1302) | 2*(3+5) | 16 |
| Max Function Return the maximum value from all given values. |
Max | Max (Q_1300, Q_1301, Q_1302) | Max(300,75,400) | 400 |
| Min Function Return the minimum value from all given values. |
Min | Min (Q_1300, Q_1301, Q_1302) | Min(30,120,50) | 30 |
Tip: When using multiple operators in a formula, these will be executed according to the BODMAS order of operations.
Comparison and Reference Operators
You also have access to a wider range of operators. Most of these can still be used in the Simple formula builder, but if you need to reference a dataset we'd recommend using the Excel-style formula builder.
| Condition | Operator | Examples |
| Range (Excel table only) Defines a range to be used as part of a formula. |
: | A1 : A4 |
| Union (Excel table only) Allows you to define multiple ranges to be used in formulae. |
, | SUM (A1:A4 ,B1:B4) |
| Equals Test if one value is equal to another. Returns TRUE or FALSE. |
= | Q_1300 = 100 |
| Greater Than Test whether one value is greater than another value. Returns TRUE or FALSE. |
> | Q_1300 > 200 |
| Less Than Test if one value is less than another. Returns TRUE or FALSE. |
< | Q_1300 < 150 |
| Greater Than or Equal To Test if one value is greater than or equal to another. Returns TRUE or FALSE. |
>= | Q_1300 >= 10 |
| Less Than or Equal To Test if one value is less than or equal to another. Returns TRUE or FALSE. |
<= | Q_1300 <= Q_1301 |
| Not Equal To Test if one value is not equal to another. Returns TRUE or FALSE. |
<> | Q_1300 <> Q_1301 |
Note: Any formula that returns a text answer (e.g. TRUE, FALSE) cannot be shown as a total.
Using IF Statements
IF statements allow to to show one of two results depending on whether a logical condition you set has been met.
There are four variants of the IF function: IF, IF with AND, IF OR, and IF NOT.
Let's get into those below:
| Function | Description | Formula Example | Explanation |
| IF | IF a statement is true, THEN return value 1, ELSE return value 2. | IF(Q_3180>=1000,Q_3180-(Q_3180*0.1),Q_3180) | IF question 1 returns a value of greater than or equal to 1000, THEN apply a 10% discount, ELSE the initial value is given as the result. |
| IF with AND | IF statement 1 is true AND statement 2 is true, THEN return value 1, ELSE return value 2. | IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187) | IF question 1 returns a value higher than 500 AND question 2 returns a value greater than 100, THEN return the sum of both values minus 10%, ELSE return the sum of questions 1 and 2. |
| IF OR | IF statement 1 is true OR statement 2 is true, THEN return value 1, ELSE return value 2. |
IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187) | IF question 1 returns a value higher than 500 OR question 2 returns a value higher than 100, THEN return the sum of both values minus 10%, ELSE return the sum of questions 1 and 2. |
| IF NOT | IF statement 1 is NOT true, THEN return value 1, ELSE return value 2. | IF(NOT(Q_3180+Q_3187>=1000), Q_3180+Q_3187,Q_3180+Q_3187-(Q_3180+Q_3187)*0.1) | IF the sum of question 1 and question 2 is NOT greater than or equal to 1000, THEN return the sum of question 1 and question 2, ELSE return the sum of question 1 and question 2 minus 10%. |
You can head over to our help doc all about creating IF statements for a walk-through on each of the above.