Operators
Operators are probably the most familiar way of computing new values from existing ones.
|
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 |
Order of Precedence
This idea might have been taught to you at school as BODMAS, BIDMAS, BEDMAS or PEMDAS.
|
Mnemonic
|
Alternative
|
Name
|
Symbols
|
|---|---|---|---|
|
B
|
P
|
Brackets (parenthesis)
|
( )
|
|
O
|
E
|
Order (exponents)
|
^
|
|
DM
|
MD
|
Multiplication, Division
|
* /
|
|
AS
|
AS
|
Addition, Subtraction
|
+ -
|
This is the order in which parts of the calculation happen. 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.
=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.
If in Doubt
Add brackets!
Other rules:
- All comparison operators have the same priority, and it is less than add and subtract.
- Where a “-“ sign is used to make a number negative (rather than to subtract), it has priority over everything except brackets. “-3^2”, therefore, is 9, not -9.
- Functions always have the lowest priority, and all have the same priority.
Functions
A function is written as a word, followed by brackets containing a list of values. If there is more than one value, then they are separated by commas. How many values are needed depends on the function.
There is a predefined list of functions, each with a special purpose.
=MIN(6, 9)
The MIN function takes two numbers, and results in the MINimum of the two. In this case, 6.
=2 * MIN(5 + 1, 12 – 3)
This formula works out to 12.
If you want the minimum of three numbers, you need to nest two MIN functions.
=MIN(6, MIN(9, 15))
Things in brackets get done first, to first the “MIN(9, 15)” happens (works out to 9), then the “MIN(6, 9)” part (works out to 6).
You can mix different functions and operators however you like, provided you put everything in the right place.
=9 * MIN(4 * 2, MAX(VALUE("7"), 36 / 6) + 3) – 2
There is a predefined list of functions, each with a special purpose.
=MIN(6, 9)
The MIN function takes two numbers, and results in the MINimum of the two. In this case, 6.
=2 * MIN(5 + 1, 12 – 3)
This formula works out to 12.
If you want the minimum of three numbers, you need to nest two MIN functions.
=MIN(6, MIN(9, 15))
Things in brackets get done first, to first the “MIN(9, 15)” happens (works out to 9), then the “MIN(6, 9)” part (works out to 6).
You can mix different functions and operators however you like, provided you put everything in the right place.
=9 * MIN(4 * 2, MAX(VALUE("7"), 36 / 6) + 3) – 2
Try This
Try copying that formula into a cell of your favourite spreadsheet application.