Top of PageOperators specify the type of calculation that you want to perform on the elements of a formula. Lists support three different types of calculation operators: arithmetic, comparison, and text.
The order in which a list performs operations in a formula
Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.
Arithmetic operators To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.
| Arithmetic operator | Meaning (Example) |
|---|---|
| + (plus sign) | Addition (3+3) |
| – (minus sign) | Subtraction (3–1) Negation (–1) |
| * (asterisk) | Multiplication (3*3) |
| / (forward slash) | Division (3/3) |
| % (percent sign) | Percent (20%) |
| ^ (caret) | Exponentiation (3^2) |
Comparison operators You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value of Yes or No.
| Comparison operator | Meaning (Example) |
|---|---|
| = (equal sign) | Equal to (A=B) |
| > (greater than sign) | Greater than (A>B) |
| < (less than sign) | Less than (A<B) |
| >= (greater than or equal to sign) | Greater than or equal to (A>=B) |
| <= (less than or equal to sign) | Less than or equal to (A<=B) |
| <> (not equal to sign) | Not equal to (A<>B) |
Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.
| Text operator | Meaning (Example) |
|---|---|
| & (ampersand) | Connects, or concatenates, two values to produce one continuous text value ("North"&"wind") |
Formulas calculate values in a specific order. A list formula might begin with an equal sign (=). Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Lists calculate the formula from left to right, according to a specific order for each operator in the formula.
Operator precedence
If you combine several operators in a single formula, lists perform the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — lists evaluate the operators from left to right.
| Operator | Description |
|---|---|
| – | Negation (as in –1) |
| % | Percent |
| ^ | Exponentiation |
| * and / | Multiplication and division |
| + and – | Addition and subtraction |
| & | Connects two strings of text (concatenation) |
| = < > <= >= <> | Comparison |
Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because a list calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, the list adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses around the first part of the formula force the list to calculate [Cost]+25 first and then divide the result by the sum of the values in columns EC1 and EC2.
=([Cost]+25)/SUM([EC1]+[EC2])