/en-tutor-prep-s3-ict-spreadsheet-formulas-functions-practice-2/
/en-tutor-prep-s3-ict-spreadsheet-formulas-functions-practice-2/
General Inquiry: info@TutorZone.com.hk
Office Hours: Monday to Saturday - 9:00 AM to 6:00 PM
WhatsApp Monday to Sunday - 24 hours
Hotline: +852 6828 1809
Instant Support: +852 9061 3106

Paper 2: Advanced: absolute references and conditional counts

Paper 2: Advanced: absolute references and conditional counts

  • 2026-11-21

Paper 2: Advanced: absolute references and conditional counts

Chapter practice with ten items, answers and a one-line note for each, about 25 minutes.

Paper 2 Ten items on this chapter with answers for immediate marking.

01 Paper details (check answers afterwards)

Item Detail
Questions 10
Suggested time 25
Difficulty mix Foundation 4 · Intermediate 2 · Challenge 2 · Error redo 2
Chapter Chapter: S3 ICT: spreadsheet formulas and functions
Level Advanced

02 Questions (10 items)

  1. FoundationWhat does the reference `$B$2` mean?
  2. FoundationD2 holds `=$B$2*C2`. After copying it to D3, what formula does D3 hold?
  3. FoundationD2 to D6 hold 66, 336, 387, 80 and 111. What does `=COUNTIF(D2:D6,”>100″)` give?
  4. FoundationB2 to B6 hold 12, 8, 3, 25 and 6, with matching amounts in D2 to D6. What does `=SUMIF(B2:B6,”>=10″,D2:D6)` give?
  5. IntermediateWhat does `=IF(B2>=10,”Yes”,”No”)` return when B2 is 12?
  6. IntermediateThe five quantities are 12, 8, 3, 25 and 6. Filling that IF formula down gives how many yes results?
  7. ChallengeWith the same amounts, what does `=MEDIAN(D2:D6)` give?
  8. ChallengeD6 is blank. How does `=AVERAGE(D2:D6)` treat it?
  9. Error redoA cell formatted as a percentage shows 39.49%. What value is actually stored?
  10. Error redoHow do `=SUM(D2:D6)` and `=D2+D3+D4+D5+D6` differ?

03 Answers and one-line notes

# Answer One-line note
1 An absolute reference that still points at B2 wherever it is copied. The two dollar signs lock both column and row so filling does not shift them.
2 `=$B$2*C3`. $B$2 stays locked while C2 moves to C3 because it is relative.
3 3. The values above 100 are 336, 387 and 111, which is three cells.
4 146. The quantities of 10 or more are 12 (66) and 25 (80), which total 146.
5 Yes. The first value is used when the test is true and the second when it is false.
6 2. Only the quantities 12 and 25 reach 10.
7 111. Sorted into 66, 80, 111, 336 and 387, the middle value is 111.
8 It skips the blank and divides the numeric total by four. AVERAGE does not treat a blank as zero, so the mean is not dragged down.
9 0.3949. The number format only changes how the value looks, not the value itself.
10 They give the same total now, but the SUM range grows automatically when a row is inserted. Adding cells one by one does not update on insert, so a row is easily missed.

04 What to do after marking

  • After marking, redo only the wrong items and write down which step went wrong (formula, unit, or misread number).
  • Repeat the same item two days later; move to the next paper only when both attempts are correct.
  • Parents only need the answer and note columns; there is no need to explain every item.

Want the whole chapter? See all four difficulty papers on the chapter page.Tutor Registration →

Questions and answers are written originally by the TutorZone team and are not taken from any school, publisher or examination authority paper. Every value is checked by computer.

Join as a tutor or coach — academic, music, sports, arts, STEM. Free to register. See how it works →