AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Term 2 in review

Week 21 · Review: Term 2 · 3 lessons
2

Sheets that decide

Computing 9: Topics 1.8–3.1, pp. 135–272
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.

FunctionWhat it doesFor B2:B6 of the seed sheet
SUMadds the numbers47
MAXfinds the largest15
MINfinds the smallest4
COUNTIF(B2:B6,">=9")counts cells that are 9 or more3
Worked examples
B7fx=SUM(B2:B6)
AB
1Seed packSold
2Beans12
3Carrots7
4Peas15
5Herbs4
6Squash9
7Total47
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 A2Result
5Short
6Medium
15Medium
16Long
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
ABCDE
1DaySalesFeeRate0.1
2Mon404
3Tue252.5
4Wed606
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)
AB
1DayVisitors
2Mon18
3Tue25
4Wed9
5Thu31
6Fri17
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 A2Result
2?
3.5?
4?
5?
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.
PartFull markWeightScore
Quiz504035
Project206015
Overall100?
4
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)
AB
1DayLoaves
2Mon12
3Tue14
4Wed11
5Thu16
6Fri13
7Total53