EXCEL
Precedence of Operators
An important fact to bear in mind when constructing a formula is the order in which calculations are carried out. Look at the equation:
2 + 3 x 4 =
You might think the answer to this is 20, type the following in an Excel cell:
=2+3*4
and hit Enter. You get the answer 14!
The
reason for this answer is the precedence of operators, the operators
are the mathematical signs like plus, multiply and brackets. Fourteen is the mathematically correct answer as
operators are applied in the following order:
Operator |
Description |
: (colon) (single space) , (comma) |
Reference operators |
– |
Negation (as in –1) |
% |
Percent |
^ |
Exponentiation |
*
and / |
Multiplication and division |
+
and – |
Addition and subtraction |
& |
Connects two strings of text (concatenation) |
= <
> <= >= <> |
Comparison |
Therefore the multiplication is carried out before the addition so the answer fourteen is obtained when you add 2 to 3 x 4. If you want to force operations to be carried out in a different order you need to use brackets as anything in the brackets will be calculated first. To obtain the answer 20 from our formula we would need to use =(2 + 3) * 4.
When a formula contains more than one operator with the same order of precedence, Excel performs the operation from left to right. Thus, in the formula =4*10/8, Excel multiplies 4 by 10 before dividing the product by 8. The result of the calculation is 5.
Click here to return to the Excel index.