- Practice
- About 16 min
- 5 practice questions
Formula evaluation drills: order of operations, comparisons, percentages and text joins
Work out what a formula shows before pressing Enter, avoid the common precedence and percentage traps, and use comparisons and the & operator correctly.
If something looks wrong, please tell us.
Many spreadsheet questions show a formula and ask what the cell will display. They are quick marks if you apply the order of operations every time, and lost marks if you read left to right. This lesson drills the method and the traps, using rules shared by Calc and Excel.
The method: four passes
- Work everything inside brackets first (innermost first).
- Do powers (^).
- Do multiplication and division in one pass, strictly left to right.
- Do addition and subtraction in one pass, strictly left to right. Text joins with & and comparisons (=, <>, >, <, >=, <=) come last.
The second and fourth passes are where most errors happen: × and ÷ have equal rank, as do + and −, so their order is decided only by position.
Worked example 1: three traps in one table
| Formula | Wrong reading | Correct working | Shows |
|---|---|---|---|
| =20/4*2 | 20 ÷ 8 = 2.5 | 20 ÷ 4 = 5, 5 × 2 = 10 | 10 |
| =10-3-2 | 10 − 1 = 9 | 10 − 3 = 7, 7 − 2 = 5 | 5 |
| =2*3^2 | 6² = 36 | 3² = 9, 2 × 9 = 18 | 18 |
| =(2*3)^2 | - | 6² = 36 | 36 |
| =18-6/3*2 | 12/6 = 2 | 6 ÷ 3 = 2, 2 × 2 = 4, 18 − 4 = 14 | 14 |
Percentages: the % sign means ÷ 100
In a formula, 10% is simply 0.1. So =200*10% gives 20, but =200+10% gives 200.1, not 220, because it adds 0.1. To increase 200 by 10% write =200*(1+10%) or =200*110%, which give 220. To reduce by 20%, multiply by (1−20%): 1500 × 0.8 = 1200. 'What per cent is 45 of 60?' is =45/60, which is 0.75 and shows 75% once formatted as a percentage.
Worked example 2: percentage change
A1 holds last year's sales, 600, and B1 this year's, 690. Percentage change = (new − old) ÷ old. The formula =(B1-A1)/A1 works out as 90 ÷ 600 = 0.15, which shows 15% in percentage format. Leaving out the brackets, =B1-A1/A1, divides first: 690 − 1 = 689 - a classic trap.
Comparisons give TRUE or FALSE
A comparison is itself a formula. If A1 holds 45 and B1 holds 60, then =A1>B1 shows FALSE, =A1<>B1 shows TRUE (<> means 'not equal to'), and =A1>=45 shows TRUE because 'greater than or equal' includes equal. These logical results are what IF tests in the next lessons.
Joining text with &
The & operator joins values into text. ="Rs "&500 shows Rs 500, and if A1 holds Ravi and B1 holds Kumar, =A1&" "&B1 shows Ravi Kumar - the " " adds the space. Joining numbers produces text: if A1 holds 12 and B1 holds 34, =A1&B1 shows 1234 as text, which lines up on the left. In Excel, arithmetic comes before &, so ="Total "&40+60 first adds 40 + 60 and shows Total 100.
Brackets → ^ → × ÷ (left to right) → + − (left to right) → & → comparisons. 10% = 0.1, so 'add 10%' is ×(1+10%), never +10%.
Key takeaways: run the four passes every time; treat × and ÷, and + and −, strictly left to right; read % as ÷ 100; bracket the numerator in a percentage change; and expect TRUE or FALSE from a comparison.
Check your understanding
-
1. What does =18-6/3*2 show?
- 8
- 16
- 14
- 4
Show answer
Answer: C. Division and multiplication first, left to right: 6 ÷ 3 = 2, then 2 × 2 = 4. Then subtract: 18 − 4 = 14.
-
2. What does =500+5% show?
- 500.05
- 525
- 505
- 25
Show answer
Answer: A. 5% in a formula is 0.05, so the formula adds 0.05 to 500, giving 500.05. To add five per cent you would write =500*(1+5%), which gives 525.
-
3. A1 holds 600 and B1 holds 690. Which formula gives the percentage increase (15% when formatted)?
- =B1-A1/A1
- =(B1-A1)/A1
- =(B1-A1)/B1
- =B1/A1-A1
Show answer
Answer: B. Percentage change is (new − old) ÷ old = (690 − 600) ÷ 600 = 0.15, i.e. 15%. Without brackets the division happens first; dividing by B1 uses the new value instead of the old one.
-
4. What does =2*3^2 show?
- 36
- 12
- 81
- 18
Show answer
Answer: D. The power is worked first: 3^2 = 9, then 2 × 9 = 18. 36 would need the brackets =(2*3)^2.
-
5. In Microsoft Excel, what does ="Total "&40+60 show?
- Total 4060
- Total 100
- Total 40+60
- #VALUE!
Show answer
Answer: B. Arithmetic is done before the & join, so 40 + 60 = 100 first, and then "Total " is joined to it, giving Total 100.
Sign in to keep track of the lessons you finish.