GoalReview spreadsheet functions, IF and nested IF, absolute references, weights and the evaluation of a spreadsheet.
New words
nested IF · an IF function placed inside another IF so that a formula can choose between three or more resultsabsolute reference · a cell reference with $ signs, such as $E$1, that does not change when a formula is filled downweight · a number that says how much one part counts towards a totalCOUNTIF · a function that counts the cells in a range that meet a condition
Explanation
A formula starts with = and can use a function on a range of cells, such as =SUM(B2:B6). IF chooses between two results using a test, such as =IF(B2>=50,"Pass","Fail"). A nested IF puts another IF inside the first to choose between three or more results, so the tests must be written in the right order. When a formula is filled down, a normal (relative) reference moves down with it, but a reference with $ (absolute) keeps pointing at the same cell, which is why a fixed rate is kept in its own cell. A weighted total multiplies each mark by how much it counts. What-if analysis changes one input to see the effect on the results, and the tests should include usual, extreme and wrong values. To judge a spreadsheet, check that the formulas are right (functional) and that it is easy to read and use (non-functional), and then recommend improvements.
Function
What it does
For B2:B6 of the seed sheet
SUM
adds the numbers
47
MAX
finds the largest
15
MIN
finds the smallest
4
COUNTIF(B2:B6,">=9")
counts cells that are 9 or more
3
Worked examples
B7fx=SUM(B2:B6)
A
B
1
Seed pack
Sold
2
Beans
12
3
Carrots
7
4
Peas
15
5
Herbs
4
6
Squash
9
7
Total
47
Functions on a range. A seed stall records how many packets of each kind it sold. B7 adds the range B2:B6 and shows 47. =MAX(B2:B6) shows 15, =MIN(B2:B6) shows 4, and =COUNTIF(B2:B6,">=9") counts the packs with at least 9 sold: beans, peas and squash, so it shows 3.
Queue length in A2
Result
5
Short
6
Medium
15
Medium
16
Long
A nested IF in the right order. A ferry sign shows Short for a queue of up to 5 cars, Medium for up to 15 cars and Long above that. The formula is =IF(A2<=5,"Short",IF(A2<=15,"Medium","Long")). Each boundary value is tested: 5 is Short, 6 is Medium, 15 is Medium and 16 is Long. If the tests were written the other way round, <=15 would catch the Short queues first.
C3fx=B3*$E$1
A
B
C
D
E
1
Day
Sales
Fee
Rate
0.1
2
Mon
40
4
3
Tue
25
2.5
4
Wed
60
6
An absolute reference. A puppet theatre pays a fee of 10 % of its sales, and the rate 0.1 is kept in E1. C2 holds =B2*$E$1 and is filled down. In row 3 the formula becomes =B3*$E$1: B moves down with the row, but $E$1 stays. The fees are 4, 2.5 and 6. Changing E1 once changes every fee.
Class activity
Paper spreadsheet (no computer). Draw a grid of 4 columns and 6 rows. Write a title and five numbers in a column, such as the minutes five friends ran. Write the formulas for the total, largest, smallest and a COUNTIF on cards and ask a partner to work out each result by hand. Then swap: your partner changes one number (a what-if question) and you work out which results change.
Practice
1
Work out the result of each formula for the sheet shown. (a) =SUM(B2:B6) (b) =MAX(B2:B6) (c) =MIN(B2:B6) (d) =COUNTIF(B2:B6,"<20") (e) =COUNTA(A2:A6)
A
B
1
Day
Visitors
2
Mon
18
3
Tue
25
4
Wed
9
5
Thu
31
6
Fri
17
(a) 18 + 25 + 9 + 31 + 17 = 100. (b) 31. (c) 9. (d) three days are below 20 (18, 9 and 17), so 3. (e) five day names, so 5.
2
A canoe hire formula is =IF(A2<=2,8,IF(A2<=4,14,20)), and A2 holds the hours. Work out the result when A2 is 2, 3.5, 4 and 5.
Hours in A2
Result
2
?
3.5
?
4
?
5
?
2 hours: 2 <= 2 is true, so 8. 3.5 hours: the first test is false and 3.5 <= 4 is true, so 14. 4 hours: 14. 5 hours: both tests are false, so 20.
3
An overall mark gives the quiz a weight of 40 and the project a weight of 60. Work out the overall mark for a pupil who scores 35 out of 50 and 15 out of 20, and write a formula for D2 if the scores are in B2 and C2.
The total in B7 shows 53, but the five days really add up to 66. Find the fault, say whether it is a functional or a non-functional fault, and write the correct formula.
B7fx=SUM(B2:B5)
A
B
1
Day
Loaves
2
Mon
12
3
Tue
14
4
Wed
11
5
Thu
16
6
Fri
13
7
Total
53
The formula adds only B2:B5, so it leaves out Friday’s 13. This is a functional fault, because the sheet does not do its job. The correct formula is =SUM(B2:B6).