Stretch tasks Formulas and functions

Round 1: Quiz Blitz

1. It is shown as text, not calculated Without the equals sign the spreadsheet has no idea it is meant to be a formula.
2. 20 The brackets are worked out first: 6 + 4 = 10, then 10 x 2 = 20.
3. It updates automatically if the value in B2 changes Referring to the cell links the formula to the data, so the answer is always up to date.
4. =COUNTIF(B2:B20,">50") COUNTIF counts only the cells that meet a condition. COUNT counts every cell holding a number.
5. False COUNT only counts cells that contain numbers. Text and empty cells are ignored.
6. 6 The largest is 10 and the smallest is 4, so 10 - 4 = 6. This gap is called the range.
7. Pass >= means greater than or equal to, so 50 passes.
8. Pass 69 is not 70 or more, so the second IF runs. 69 is at least 50, so it shows Pass.
9. =AVERAGE(B2:B5); =SUM(B2:B5)/4 Without brackets, =B2+B3+B4+B5/4 divides only B5 by 4, because division happens before addition.
10. Brackets: 4 - 1 = 3 → Multiply: 3 x 3 = 9 → Add: 2 + 9 = 11 Brackets first, then multiplication and division, then addition and subtraction. The answer is 11, not 15.
11. 3.14 ROUND keeps the number of decimal places you ask for: 2 places gives 3.14.
12. B2, C2 and D2 A range runs from its first cell to its last: here across row 2 from column B to column D.

Round 2: Quick Fire

1. • The answer updates automatically when the data changes
   • A value only needs changing in one place, so there are fewer mistakes
   Model answer

   If a formula uses cell references, the answer updates automatically whenever the data changes. A value only has to be changed once, in one place, so there are fewer mistakes than retyping numbers in every formula.

2. • Fail
   • 45 is less than 50, so the condition is false
   Model answer

   It shows Fail, because 45 is less than 50, so the condition B2>=50 is false and the value if false is used.

3. • COUNT counts how many cells contain numbers
   • COUNTIF only counts the cells that meet a condition
   Model answer

   COUNT counts how many cells in a range contain numbers. COUNTIF only counts the cells that meet a condition, such as values over 50.

4. • Only B5 is divided by 4, because division happens before addition
   • Put brackets round the addition, or use AVERAGE
   Model answer

   Division happens before addition, so only B5 is divided by 4. The fix is to put brackets round the addition, =(B2+B3+B4+B5)/4, or simply use =AVERAGE(B2:B5).

Round 3: Big Think

1. • AVERAGE for the mean
   • MAX for the top mark
   • COUNTIF, with a condition such as >=50, for the number who passed
   • IF, or nested IFs, for each student's grade
   • Uses ranges such as B2:B31, so the results update when marks change
   Model answer

   She should use =AVERAGE(B2:B31) for the mean mark and =MAX(B2:B31) for the top mark. =COUNTIF(B2:B31,">=50") counts how many passed. For each student's grade she can use a nested IF function, such as =IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail")), filled down the column. Because every function uses a range of cells, all the results update if a mark is changed.

2. • Formulas recalculate automatically when a value changes
   • What if questions can be answered by changing an input
   • The data is stored, so it can be saved, edited and shared
   • Functions such as SUM handle many values at once
   • There are fewer mistakes than retyping on a calculator
   • Charts can show the costs visually
   • You can check how every total was worked out
   Model answer

   A spreadsheet stores all the costs, so the work can be saved, edited later and shared with other staff, whereas a calculator forgets everything. Formulas recalculate automatically, so if the coach price changes the total and the cost per student update instantly. That means what if questions, such as what happens if only 50 students go, take seconds by changing one input. Functions such as SUM add up many values at once, there are fewer mistakes than retyping numbers into a calculator, and anyone can check how each total was worked out by looking at the formulas. The costs can also be shown on a chart.

This or That

1. =B2+C2 Without the equals sign the spreadsheet treats what you type as text.
2. =MAX(B2:B20) MAX finds the largest value. SUM adds them all up.
3. 7.5 Rounding to 1 decimal place looks at the next digit. It is 5, so 7.4 rounds up to 7.5.
4. IF IF tests a condition and shows one result when it is true and another when it is false. COUNTIF counts cells.
5. Within budget 100 is not greater than 100, so the condition is false and the second result is shown.
6. 3 The IF inside the IF gives Merit, Pass or Fail: three results.
7. 6 AVERAGE adds them up to 18, then divides by how many there are, 3.
8. Cells B2, B3, B4 and B5 A colon makes a range: every cell from the first to the last.

Memory Match

• A 4 SUM: Adds up a range
• B 6 AVERAGE: Finds the mean of a range
• C 2 MAX: Finds the largest value
• D 5 COUNT: Counts the cells that hold numbers
• E 1 COUNTIF: Counts the cells that meet a condition
• F 3 ROUND: Turns 7.456 into 7.5 with 1 decimal place