Spreadsheet Functions
Unit 3 - Spreadsheet Fundamentals
Function Reference
Basic Functions
- =SUM(range) - Add values
- =AVERAGE(range) - Mean value
- =MAX(range) - Largest value
- =MIN(range) - Smallest value
- =COUNT(range) - Count numbers
- =COUNTA(range) - Count non-empty
Logic & Lookup
- =IF(test, true, false)
- =VLOOKUP(value, range, col, 0)
- =COUNTIF(range, criteria)
- =SUMIF(range, criteria)
Use this data for Questions 1-7:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Maths | English | Science |
| 2 | Alice | 85 | 72 | 90 |
| 3 | Bob | 68 | 75 | 82 |
| 4 | Carol | 92 | 88 | 95 |
| 5 | David | 45 | 52 | 60 |
| 6 | Eve | 78 | 81 | 76 |
Question 1 (1 mark)
Write a formula to calculate the total of all Maths scores (B2:B6).
Question 2 (1 mark)
Write a formula to find the average English score.
Question 3 (1 mark)
Write a formula to find the highest Science score.
Question 4 (2 marks)
Write a formula to calculate Alice's total score across all subjects (row 2).
Question 5 (2 marks)
Write a formula to count how many students scored 80 or more in Maths.
Question 6 (3 marks)
Write a formula for cell E2 that displays "Pass" if Alice's Maths score is 50 or more, otherwise "Fail".
Question 7 (4 marks)
Write a formula for cell F2 that displays Alice's grade based on her Maths score:
- - 80+: "A"
- - 60-79: "B"
- - Below 60: "C"
VLOOKUP Questions - Use this lookup table:
| H | I | J | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P001 | Laptop | $999 |
| 3 | P002 | Mouse | $25 |
| 4 | P003 | Keyboard | $75 |
| 5 | P004 | Monitor | $350 |
Question 8 (3 marks)
Write a VLOOKUP formula that finds the product name for code "P003" in the lookup table.
Question 9 (3 marks)
If cell L1 contains a product code, write a formula that returns the price from the lookup table.
Question 10 (4 marks)
Explain why FALSE (or 0) is used as the last parameter in VLOOKUP.
View Answer Key
Q1: =SUM(B2:B6)
Q2: =AVERAGE(C2:C6)
Q3: =MAX(D2:D6)
Q4: =SUM(B2:D2) or =B2+C2+D2
Q5: =COUNTIF(B2:B6,">=80")
Q6: =IF(B2>=50,"Pass","Fail")
Q7: =IF(B2>=80,"A",IF(B2>=60,"B","C"))
Q8: =VLOOKUP("P003",H2:J5,2,FALSE)
Q9: =VLOOKUP(L1,H2:J5,3,FALSE)
Q10: FALSE (exact match) ensures the lookup value must match exactly. TRUE would allow approximate matching, which could return incorrect results if the exact code isn't found. For product codes, we always want an exact match.