A spreadsheet calculates a class budget correctly on the first row, then returns wrong answers after the formula is copied down. Why? Perhaps a price cell moved when it should have remained fixed. Perhaps a lookup used approximate matching when it needed an exact match. The difficult part of spreadsheet work is not pressing a function button: it is defining the data, controlling references, choosing valid formulas and checking whether the results make sense.
This independent Ministry of Education V3.0 reference develops the 2027 G3 Computing K349 Spreadsheet module through relative, absolute and mixed references; conditional formatting and Goal Seek; logical, arithmetic and statistical functions; text extraction and concatenation; VLOOKUP, HLOOKUP, INDEX and MATCH; date calculations; and fault diagnosis. It includes 26 original worked examples, a twelve-stage Data–Formula–Verify protocol, an invented learning-centre worksheet and eduKate Depth–Load–Transfer practice.
This is independent eduKateSG teaching material, not an official MOE/SEAB paper or a live data-management system. All worksheet names, students, amounts, scores and entries below are fictional. Users should not disclose real students’ grades or identifying data merely to demonstrate formulas. Spreadsheet software may differ slightly in defaults, so explicitly defined arguments and school examination instructions take precedence.
1. A Spreadsheet Is a Model, Not a Truth Machine
A spreadsheet holds input values, formulas and formatted results. If the input is wrong or a cell reference points to the wrong place, a formula can calculate precisely but answer the wrong question. Good work begins by naming the model’s purpose and checking the meaning of every column. A neat green cell is no evidence of numerical correctness by itself.
2. Exact 2027 G3 Computing K349 Spreadsheet Scope
The 2027 G3 Computing K349 syllabus, Module 3 Spreadsheets, includes relative, absolute and mixed references, Goal Seek, conditional formatting, logical, mathematical, statistical, text, lookup and date functions. The officially listed examinable functions include IF, AND, OR, NOT, SUMIF, COUNTIF, AVERAGEIF, ROUND, LEFT, MID, RIGHT, LEN, FIND, SEARCH, CONCAT, VLOOKUP, HLOOKUP, INDEX, MATCH, DAYS, TODAY and NOW, among others. Paper 2 is lab-based and includes one spreadsheet question alongside programming tasks. This reference uses only functions from the published list in its assessed-style examples, rather than claiming that every newer spreadsheet feature is compulsory.
3. A Cell Reference Can Move or Remain Fixed
A relative reference such as A2 changes to A3 when a formula is copied down one row. An absolute reference $B$1 stays at B1 even if moved or copied. A mixed reference $B2 fixes the column but lets the row change; B$1 fixes the row but lets the column change. These forms are a deliberate part of the formula, not decorative punctuation.
4. Logical Functions Explain Decisions
An IF function returns one value when a condition is true and another when false. AND requires every condition to be true; OR requires at least one; NOT reverses a Boolean value. A spreadsheet can use these functions to classify fictional tasks or find records needing review, but a computed label should not substitute for a teacher’s professional judgement about a real student.
5. Conditional Formatting Changes Appearance, Not the Underlying Number
A rule can colour a cell when a value crosses a specified threshold, helping users spot exceptions. This changes display properties automatically; it does not alter the calculated value or repair incorrect input. A useful spreadsheet audit checks the formula and the underlying result independently from whatever colour appears.
6. Lookups Require Keys, Tables and Matching Rules
VLOOKUP searches down the first column of a supplied table; HLOOKUP searches across its top row. INDEX retrieves a value from a specified position; MATCH locates a position according to a match mode. Exact matching is appropriate for identifiers that must agree exactly. Approximate threshold matching requires a suitably sorted lookup table and a correctly defined comparison rule.
7. Data Types Matter as Much as Formulas
A blank cell, a numeric zero and a text label are different values. COUNT counts numeric cells, COUNTA counts nonblank cells and COUNTBLANK counts empty cells. Dates require genuine spreadsheet date values for functions such as DAYS to behave as intended. Text functions count or extract characters, which need not be the same as numeric interpretation.
8. Eight Rooms of Spreadsheet Reasoning
Room one states the question and output. Room two defines the data columns and their types. Room three chooses valid reference styles. Room four selects the correct function and arguments. Room five handles ranges, match modes and criteria. Room six copies formulas and checks shifted references. Room seven inspects errors or implausible results. Room eight transfers the design to an unfamiliar worksheet with independent verification.
9. Twelve-Stage Data–Formula–Verify Protocol
Stage 1 — Read the exact task
Is the user finding a total, average, category, lookup, position, date interval or target value? Name the expected output type before choosing a formula.
Stage 2 — Check input cells
Confirm whether values are numbers, text, dates or blanks. The text label ‘0’ is not always equivalent to numeric zero in spreadsheet operations.
Stage 3 — Identify data and reference tables
Separate raw input ranges from price lists, thresholds or parameters. Keep table headers and key fields consistent so lookups work reliably.
Stage 4 — Choose reference movement
Use A2 for a row-specific quantity and $B$1 for a fixed shared parameter where appropriate. Decide which row or column should change when copied.
Stage 5 — Select an approved function
For a conditional total use SUMIF, a conditional count COUNTIF, a two-way decision IF, or a specified lookup function. Do not add unnecessary complexity to a simple sum.
Stage 6 — Supply ranges and criteria precisely
Check range dimensions and ensure a criteria string expresses the intended condition. The expression ‘greater than or equal to 15’ is not equivalent to simply greater than 15.
Stage 7 — Define lookup match mode
For an identifier use exact match such as FALSE in VLOOKUP or 0 in MATCH. For approximate lookup, verify thresholds are sorted as required.
Stage 8 — Build the formula in one known row
Test it against a manually checked example before copying down the column. A first-row correct answer is only the beginning of verification.
Stage 9 — Copy and inspect a later row
Check whether references moved correctly. If a fixed-rate cell slid from B1 to B2, change it to absolute or mixed referencing.
Stage 10 — Investigate errors before hiding them
A missing key, inappropriate numeric operation or zero denominator should prompt diagnosis. Replacing all errors with blank output can conceal flawed data or an invalid model.
Stage 11 — Add Goal Seek or formatting where justified
Goal Seek can adjust an input to achieve a specified calculated output; conditional formatting highlights values meeting a rule. Neither validates the logic of the original formula automatically.
Stage 12 — Test independent transfer
Rebuild the same result using new fictional data, changed row positions and a different lookup key. Explain why each function and reference type is needed.
10. Fictional Workbook: The Learning Hub Stock and Task Ledger
Imagine a fictional workbook called Learning Hub. In cells B2:B6 the quantities 12, 18, 0, 24 and 6 are recorded. A separate table in F2:G4 contains item codes A01, A02 and A03 with prices $120, $150 and $180 respectively. A further sorted threshold table has lower bounds 0, 50, 70 and 90 with labels Beginner, Developing, Confident and Advanced. These are educational toy numbers, not grades or charges from an actual eduKate class.
In the main quantity column, SUM should return 60, AVERAGE 12, COUNTIF for entries at least 15 should return 2, and AVERAGEIF for values greater than zero should return 15. The count of zero is significant: zero means a recorded quantity of none, not a missing entry. The data and the formulas can be audited by hand before any formatting rules are applied.
11. Twenty-Six Original Worked Spreadsheet Examples
12. Worked Example A — Relative copy
Cell C2 contains =A2*B2. Copied to C3, its references become A3 and B3. This is correct when each row has its own quantity and unit price; the movement is a feature, not a fault.
13. Worked Example B — Absolute reference
A common fee multiplier is stored in B1. A formula =A2*$B$1 copied down to row 3 becomes =A3*$B$1, keeping the shared factor fixed.
14. Worked Example C — Mixed column
The reference $B2 locks column B but allows the row to change. Copied one row down it becomes $B3; copied one column right it still points at column B.
15. Worked Example D — Mixed row
The reference B$1 locks row 1 while the column is relative. Copied one column to the right it becomes C$1. These movements are useful in two-dimensional tables.
16. Worked Example E — Sum a range
For 12,18,0,24,6 in B2:B6, =SUM(B2:B6) returns 60. A manual total confirms the formula includes the intended rows and nothing else.
17. Worked Example F — Arithmetic mean
=AVERAGE(B2:B6) returns 12 because the sum of 60 is divided by five numeric cells, including the zero. Omitting zero as though it were blank would incorrectly change the average.
18. Worked Example G — Count numeric records
=COUNT(B2:B6) returns 5 for the five numeric values, including zero. COUNT does not count text labels as numeric entries.
19. Worked Example H — Count cells meeting a criterion
=COUNTIF(B2:B6,">=15") returns 2, since 18 and 24 meet the threshold. The criteria string includes greater-than-or-equal signs so 15 itself would also qualify.
20. Worked Example I — Sum qualifying entries
=SUMIF(B2:B6,">=15",B2:B6) returns 42, the sum of 18 and 24. SUMIF is a filtered total, not simply the count of matching entries.
21. Worked Example J — Conditional average
=AVERAGEIF(B2:B6,">0") returns 15. Only four values are positive and their total is 60, so 60/4=15.
22. Worked Example K — IF classification
If B2 is 12, =IF(B2>=15,"Ready","Review") returns Review. It applies the stated numeric classification and does not make a qualitative judgement about a real pupil.
23. Worked Example L — AND logic
=AND(B2>=10,B2<=20) returns TRUE for B2=12, because both comparisons are true. If either comparison were false, AND would return FALSE.
24. Worked Example M — OR logic
=OR(B2=0,B2>20) returns FALSE for B2=12. OR becomes true when one or more of its conditions is true, not only when both are.
25. Worked Example N — NOT logic
=NOT(B2=0) returns TRUE when B2 is 12. NOT reverses the truth of a single logical expression; it does not change the underlying stored quantity.
26. Worked Example O — COUNTBLANK versus COUNT
A genuinely empty B7 contributes to =COUNTBLANK(B2:B7), producing 1 in the sample. The numeric zero in B4 is not blank. Distinguish missing entry from a recorded value of zero.
27. Worked Example P — RANK.EQ and LARGE
=RANK.EQ(24,B2:B6,0) returns 1 because 24 is the highest sample value. =LARGE(B2:B6,2) returns 18, the second-highest value.
28. Worked Example Q — ROUND and SQRT
=ROUND(10/3,2) returns 3.33. =SQRT(81) returns 9. These are distinct computations: rounding a quotient and finding a principal square root.
29. Worked Example R — LEFT and RIGHT
For the string K349-SEC, =LEFT("K349-SEC",4) returns K349 and =RIGHT("K349-SEC",3) returns SEC. They extract characters, not arithmetic place values.
30. Worked Example S — MID and LEN
=MID("K349-SEC",6,3) returns SEC because the substring starts at character 6. =LEN("K349-SEC") returns 8 characters.
31. Worked Example T — FIND versus SEARCH
=FIND("SEC","K349-SEC") returns position 6. SEARCH is generally case-insensitive while FIND is case-sensitive in familiar spreadsheet implementations. Choose according to the task.
32. Worked Example U — CONCAT labels
=CONCAT("G3","-","Computing") returns G3-Computing. CONCAT combines text; it does not sum numeric quantities.
33. Worked Example V — VLOOKUP exact identifier
If codes A01,A02,A03 appear in F2:F4 and their prices 120,150,180 in G2:G4, =VLOOKUP("A02",F2:G4,2,FALSE) returns 150. The FALSE argument requests exact matching.
34. Worked Example W — INDEX with MATCH
=INDEX(G2:G4,MATCH("A02",F2:F4,0)) returns 150. MATCH finds A02 in the second position and INDEX retrieves the second price from G2:G4.
35. Worked Example X — Approximate threshold lookup
In a sorted table with lower bounds 0,50,70,90 and corresponding labels Beginner, Developing, Confident, Advanced, a lookup for 76 with approximate match selects the 70 threshold, giving Confident. Sorting and threshold semantics are essential.
36. Worked Example Y — DAYS between dates
If A2 is a valid spreadsheet date 10 January 2027 and B2 is 1 January 2027, =DAYS(A2,B2) returns 9. The function measures an interval, not the number of dates inclusively counted.
37. Worked Example Z — Goal Seek a target
Suppose a result cell D2 contains =A2*B2 with A2=20. To reach D2=500 using Goal Seek and adjusting B2, the required value is 25. Verify 20×25=500; Goal Seek does not validate business assumptions.
38. Relative References Make Copying Useful
A row-wise total should move from one record to the next. Relative referencing permits that movement, but a shared lookup table or fixed tax factor may need dollar signs. Students should test formulas not merely in the first row but in a copied lower row and a copied neighbouring column.
39. Absolute References Are Not an Error Repair for Everything
Adding dollar signs to every reference may keep formulas pointed at the original row when they should adapt to new records. Choose relative, absolute or mixed styles based on which coordinates must stay fixed. The correct form comes from the worksheet’s logic, not an arbitrary rule to lock every cell.
40. IF Does Not Explain the Quality of a Threshold
An IF formula may classify a quantity as Ready when it reaches 15. That classification expresses a rule selected by the workbook designer; it is not proof the threshold was educationally or operationally appropriate. Separate model design from accurate implementation, especially when interpreting real-world decisions.
41. Approximate Lookups Can Return Plausible Wrong Labels
For a classification table, approximate matching assumes suitably ordered threshold values. An unsorted table may produce a result that looks reasonable but represents the wrong boundary. Check the threshold ordering before trusting a colour-coded output.
42. Exact Match Protects Identifier Integrity
An item code A02 should match exactly when identifying a price record. Approximate matches can associate the wrong product or leave out a missing-code error. In student-friendly databases and lookups, identifiers represent exact keys rather than distances between numeric quantities.
43. INDEX and MATCH Are a Two-Stage Reasoning Chain
MATCH identifies the relative position of a target item in a range. INDEX retrieves a corresponding value by position. If the two ranges cover different rows or are offset, the formula can return an unrelated record. Align ranges carefully before accepting a result.
44. Counting Functions Answer Different Questions
COUNT measures numeric entries; COUNTA measures nonblank entries; COUNTBLANK counts blank cells. A numeric zero belongs in COUNT. A text entry that looks like a number may behave differently until correctly converted. Good data analysis depends on the field’s actual type.
45. The Date Function Needs Real Date Values
A date that merely looks like ’10/01/2027′ may be imported as text and produce an error in DAYS. Verify that the spreadsheet recognises it as a date, that the date format is unambiguous and that the first argument is the end date when calculating a positive interval.
46. TODAY and NOW Are Dynamic
TODAY returns the current date in the spreadsheet environment; NOW returns date and time. They are useful for relative calculations, but their outputs can change between sessions and software settings. A printed result should state its evaluation context instead of pretending it will remain constant forever.
47. Goal Seek Solves a Chosen Model Equation
Goal Seek changes a specified input until one formula cell reaches a desired output, within its numerical method. It does not find the best business strategy, check causal assumptions or guarantee that a target is feasible in practice. Verify both the result and sensible permitted input constraints.
48. Conditional Formatting Must Be Audited
If a cell is red because it is below a target, its displayed colour should match the actual formula result and rule. A formatting range shifted by one row can highlight the wrong item. Always check at least one boundary value and one counterexample.
49. A Formula Error Should Lead to Diagnosis
A lookup failing with a missing key, a division by zero or a mismatched argument range is information about the worksheet. Concealing every error behind an empty string can hide bad data. Identify the invalid input or wrong formula before deciding how a user-facing report should display the issue.
50. Paper 2 Requires More Than Memorising Function Names
The official 2027 Computing assessment has a lab-based Paper 2 that includes a spreadsheet question and programming tasks. Students should practise entering, copying, checking and interpreting formulas, not only identifying them from a list. Independent competence includes saving the result correctly according to examination instructions.
51. Three-Pupil Spreadsheet Audit
Pupil A defines data fields and expected output; pupil B writes the formula and decides reference anchoring; pupil C tests edge cases and lookup modes. Roles rotate over new datasets. The tutor checks that each can independently recreate an analysis without copying the model’s exact cell positions.
52. eduKate Depth–Load–Transfer Diagnostic
Depth: explain why an absolute cell should stay fixed and why an identifier lookup needs exact matching. Load: coordinate a mixed reference, IF, a lookup and a copied formula without an off-by-one error. Transfer: build the same calculation on a new worksheet with different columns and values. The immutable Clementi three-pupil teaching framework supplies the diagnostic method while the spreadsheet curriculum comes from K349.
53. Parent and Tutor Questions
Ask: ‘What should this formula calculate?’, ‘Which reference should change?’, ‘What data type is in the input cell?’, ‘Does the lookup require exact matching?’, ‘What happens at the boundary?’, and ‘Can you verify one row by hand?’ These questions reveal whether the student controls the model rather than merely recognises a function.
54. Misconception Ledger
Error: absolute and relative references are interchangeable. Repair: copy and inspect. Error: a zero is blank. Repair: COUNT versus COUNTBLANK. Error: approximate VLOOKUP is suitable for any code. Repair: use exact matching. Error: a red cell means bad data. Repair: separate formatting from formula. Error: Goal Seek proves the target is sensible. Repair: examine model assumptions.
54A. HLOOKUP Works Horizontally
For a horizontal version of the fictional price table, suppose M1:O1 contains A01, A02 and A03, while M2:O2 contains 120, 150 and 180. The formula =HLOOKUP("A02",M1:O2,2,FALSE) returns 150. HLOOKUP searches the table’s top row and retrieves from the specified row underneath it. The exact-match setting suits a product code. The table must contain the desired lookup keys across that first row; swapping in a vertical table without redesigning the formula is a common error.
54B. Conditional Formatting Needs a Tested Rule
In B2:B6, apply a conditional-formatting rule to highlight numbers greater than or equal to 15. In the fictional set 12, 18, 0, 24, 6, only the cells containing 18 and 24 should be highlighted. Test the boundary by temporarily changing one value to exactly 15; it should then be highlighted because the rule includes equality. Changing the appearance does not change SUM, COUNTIF or the stored value. If the wrong row is coloured, inspect both the rule’s reference and its applies-to range before altering the underlying data.
54C. Reconcile Three Ways to Find a Price
The same A02 price of 150 can be returned using exact VLOOKUP on F2:G4, INDEX with MATCH on the corresponding columns, or exact HLOOKUP on the horizontal M1:O2 table. The agreement does not mean that any lookup table will work: the key orientation, match mode and result range must each be consistent. Asking students to verify one lookup result from the original fictional table is a powerful first-principles check before copying formulas to hundreds of rows.
55. Frequently Asked Questions
What is a relative reference?
A cell reference that changes relative to its new position when a formula is copied.
What does $B$1 mean?
Both its column B and row 1 remain fixed when copied.
What is a mixed reference?
A reference such as $B2 or B$1 that fixes one coordinate and allows the other to change.
Does conditional formatting alter the cell’s numeric value?
No. It changes appearance under the specified rules, not underlying data.
When should I use COUNTIF?
When counting records meeting a particular criterion in a range.
What is an exact lookup?
A lookup that requires the key to match the intended value exactly, rather than selecting a nearby threshold.
Why sort an approximate lookup table?
The method generally assumes an appropriate order to decide which range threshold applies.
What do INDEX and MATCH do together?
MATCH locates a position and INDEX returns the entry at that position in the selected range.
Does DAYS count both endpoints?
It returns the difference between date values, so 1 January to 10 January spans nine days.
Is Goal Seek an optimisation guarantee?
No. It solves the provided formula for a target input without proving the model or practical choice is sound.
56. Independent Depth and Transfer Exercises
Practice 1 — Copy a reference correctly
A quantity in A2 is multiplied by the shared rate in B1. Write =A2*$B$1 and explain how the references behave at row 10.
Practice 2 — Calculate several functions
For B2:B6 containing 12,18,0,24,6, find SUM=60, COUNT=5, AVERAGE=12 and COUNTIF for >=15 equal to 2.
Practice 3 — Find a conditional sum
Use SUMIF to add the sample values at least 15. Show why the result is 42 instead of the count 2.
Practice 4 — Correct a lookup error
An item lookup for A02 uses approximate matching despite a required exact code. Replace it with an exact VLOOKUP or MATCH and state the correct price 150.
Practice 5 — Use mixed references
Design a small multiplication table with values in row 1 and column A. State where $A2 and B$1 are needed when formulas are copied across and down.
Practice 6 — Detect a blank versus zero
Add one truly empty cell after five numeric quantities including zero. Explain the difference between COUNT and COUNTBLANK for the extended range.
Practice 7 — Inspect a date difference
Enter recognised dates 1 and 10 January 2027, then use DAYS to produce 9. Explain what happens if either cell is imported as plain text.
Practice 8 — Independent Goal Seek transfer
A formula gives total = units × price. For 16 units and a target 800, use Goal Seek on price and verify the answer is 50.
57. Closing Principle
A spreadsheet becomes dependable when the data type, reference movement, function, lookup mode and model assumptions are all understood. Verify a calculation by hand, inspect copied formulas, test boundary cases and distinguish display formatting from value. The standard of learning is a fresh workbook that the student can build and audit independently.
Related eduKateSG References and Official Sources
Build from Reference 301 — Data Representation, Reference 306 — Computer Networks, Reference 203 — Algorithms and Debugging, the MOE V3.0 Reference Library and the immutable Clementi teaching reference. Official specification: SEAB 2027 G3 Computing K349 Module 3, Spreadsheets.
MOE V3.0 Sequence Navigation
Previous reference: Reference 310 · MOE V3.0 Library.