Operators are probably the most familiar way of computing new values from existing ones. The following table describes each operator, what it does and the type of data that it can be used with:
| Operator | Action | Work on | Examples | Result |
|---|---|---|---|---|
| ^ | To the power of | Numbers | =12^2 | 144 |
| * | Multiply | Numbers | =6*9 | 54 |
| / | Divide | Numbers | =12/4 | 3 |
| + | Add | Numbers | =12+15 | 27 |
| - | Subtract | Numbers | =9-4 | 5 |
| & | Glue | Text | ="Hel" & "lo" | Hello |
| = | Equal | Any | =3=12 ="Dog"="Boy" | False False |
| <> | Not Equal | Any | =3<>12 ="Dog"<>"Boy" | True True |
| < | Less than | Any | =3<12 ="Dog"<"Boy" | True False |
| > | Greater than | Any | =3>12 ="Dog">"Boy" | False True |
This idea might have been taught to you at school as BODMAS, BIDMAS, BEDMAS or PEMDAS. It is important to understand this is the principle determines that order that operators are processed:
| Mnemonic | Alternative | Name | Symbols |
|---|---|---|---|
| B | P | Brackets (parenthesis) | ( ) |
| O | E | Order (exponents) | ^ |
| DM | MD | Multiplication, Division | * / |
| AS | AS | Addition, Subtraction | + - |
Things higher up the list get done first. Where two things are at the same level (like multiplication and division), they are done from left to right.
=4 + 5 * 6
It’s easy to fall into the trap of think that this is “4 + 5 = 9”, and then “9 * 6 = 54”. But, because
multiplication comes first, it is actually “5 * 6 = 30”, “4 + 30 = 34”.
If you actually wanted 54 as the result, this is where brackets come in.
=(4 + 5) * 6
Anything in brackets always has priority. The more brackets, the higher the priority. Now, the “4 + 5” part happens first, because it has more brackets around it than the multiplication.
Tip
If in Doubt - Add brackets!
A function performs an operation on a provided value or values and returns a result. There are a wide range of functions that you can use to perform operations such as manipulating dates, text or carrying out advanced mathematical operations. You can find a full list of functions here:
Functions are used by first typing the name of the function, and then supplying the required parameter values enclosed within brackets. If the function requires mutiple values then these are separted using a comma. For example, the MIN function accepts two values and returns the value of the minimum value. For example, to determine the minimum value between the two number 6 and 9, you would write:
=MIN(6, 9)In the above case, the function would return the result 6.
If you want the minimum of three numbers, you need to nest two MIN functions as follows:
=MIN(6, (MIN(9, 15)))Things in brackets get done first, so first the “(MIN(9, 15))” gets calculated first returning a result of 9. This value is then compared to the value 6, so effectively “MIN(6, 9)”. The final result is once again 6.
You can mix different functions and operators however you like, provided you put everything in the right place. For example:
=9 * MIN(4 * 2, MAX(VALUE("7"), 36 / 6) + 3) – 2