The Order of operations guide
Excel works out * and / before + and -, from left to right. =B2+B5/B6 divides B5 only. To add first and divide after, put the addition in brackets: =(B2+B5)/B6.
The order Excel uses
| First to last | Operators |
|---|---|
| 1 | Brackets ( ) |
| 2 | A minus sign in front of a number, as in -B2 |
| 3 | Percent % |
| 4 | Powers ^ |
| 5 | * and /, left to right |
| 6 | + and -, left to right |
One surprise: =-2^2 is 4, because the minus belongs to the 2 before it is squared. =0-2^2 is -4.
Split a total
Each person's share of a hotel bill and train tickets is the whole cost divided by the people. =B2*B3*B4+B5/B6 splits only the train tickets and leaves every person with the whole hotel bill. =(B2*B3*B4+B5)/B6 adds everything up, then divides.
Take a discount off
A 10% discount leaves 90% to pay, which is 1-B7 when B7 holds 10%. It needs its own brackets: =B2*(1-B7). Without them, =B2*1-B7 multiplies by 1 and then subtracts 0.1, ten cents off the bill.
Common mistakes
- An average without brackets.
=B2+B3/2halves B3 only. Use=(B2+B3)/2, orAVERAGE. - A change without brackets.
=C2-B2/B2is not the percent change;=(C2-B2)/B2is. See percent change. - The discount on everything. If only the hotel is discounted, only the hotel part gets
*(1-B7).
FAQ
- What order does Excel calculate in?
- Brackets first, then a negative sign, percent, powers, then multiplication and division from left to right, then addition and subtraction from left to right.
- Does Excel follow PEMDAS or BODMAS?
- Mostly, with one difference: a minus sign in front of a number comes before powers, so =-2^2 returns 4.
- Why does my Excel formula give the wrong total?
- Often because of the order: =A1+B1/2 divides B1 only. Put brackets round the part that should happen first: =(A1+B1)/2.
- How do I take a percentage off in Excel?
- Multiply by 1 minus the percentage, in brackets: =B2*(1-C2) when C2 holds the discount, such as 10%.



































































