What Module 3 asks for, and the dataset used here

The syllabus splits Module 3 Spreadsheets into two parts. Module 3.1 Program Features covers cell references, Goal Seek and Conditional Formatting. Module 3.2 Functions covers logical, mathematical, statistical, text, lookup and date functions, plus the operators + – * / % ^, the comparison operators and & for joining text.

Paper 2 is lab-based, so you build formulas on a computer and submit the file. Every example below uses the sheet shown here. Columns A to G hold six students. Columns H and I hold a grade table and two settings.

Quiz is out of 100 and Project is out of 20. G2 holds =D2*$I$7+E2, filled down to G7, so Final is out of 100. For Aisha: 72 × 0.8 + 18 = 57.6 + 18 = 75.6.

Relative, absolute and mixed cell references

Module 3.1.1 asks you to use relative, absolute and mixed references so formulas give the correct results when copied to similar cells in a table. A dollar sign locks the part of the reference that follows it.

Fill-down example: what changes

Here is G2 filled down two rows, with and without the dollar signs on the weight cell.

D and E are relative, so they move to each student’s row. The weight must stay on I7, so it needs both dollar signs. Without them, G3 multiplies Ben’s quiz mark by the date in I8, which the spreadsheet stores as a large serial number.

Mixed references

Use a mixed reference when one formula is copied both across and down. Suppose B1:D1 holds 2, 3 and 4, and A2:A4 holds 10, 20 and 30. In B2, =$A2*B$1 gives 10 × 2 = 20. Copied to D4 it becomes =$A4*D$1, which gives 30 × 4 = 120. The $ before A keeps the left-hand value in column A, and the $ before 1 keeps the top value in row 1.

Goal Seek and Conditional Formatting

Both features sit in Module 3.1 Program Features, which the Paper 2 spreadsheet question assesses together with the functions. Paper 1 covers all five modules, so you should also be able to explain them in writing.

Goal Seek (3.1.2)

Goal Seek finds the value needed in one cell for another cell to reach a target. You give it three things: the cell holding the formula (Set cell), the target (To value) and the input cell it may change (By changing cell). The changing cell must hold a typed value, and the Set cell must hold a formula that depends on it. In Excel it sits under Data, What-If Analysis.

Example: what Quiz mark does Gopal need for a Final of 70?

• Set cell: G7
• To value: 70
• By changing cell: D7

Goal Seek sets D7 to 66.25. Check: 66.25 × 0.8 + 17 = 53 + 17 = 70.

Conditional Formatting (3.1.3)

Conditional Formatting changes a cell’s format, such as fill colour or font colour, when a rule is true, and updates automatically when the data changes. Two rules on this sheet:

• Cell value rule: on G2:G7, format values less than 50 with a red fill. Only G3 (Ben, 48) turns red.
• Formula rule: on A2:G7, use =$F2>$I$8 to shade every row submitted after the due date. Rows 3, 5 and 7 (Ben, Darren and Gopal) are shaded.

The formula rule uses a mixed reference. $F keeps every cell in a row checking the Submitted column, the row number follows each row, and $I$8 stays on the due date.

The 42 examinable functions, grouped as the syllabus groups them

The syllabus lists these six groups in Module 3.2. Examples use the dataset in chapter 01. Square brackets mark optional arguments. The syllabus asks for rounding normal, up and down: those are ROUND, CEILING.MATH and FLOOR.MATH.

Date and Time

Text

Logical

Lookup

Mathematical

Statistical

RANK.EQ ranks from the largest when order is 0 or left out, and from the smallest when order is 1, so =RANK.EQ(D2,$D$2:$D$7,1) gives 4. =MODE.SNGL(D2:D7) returns #N/A on this sheet because no quiz mark repeats. MEDIAN sorts the six quiz marks to 45, 59, 64, 72, 88, 91 and averages the middle pair: (64 + 72) ÷ 2 = 68.

Worked examples: IF, SUMIF, COUNTIF, AVERAGEIF, text and dates

IF with AND and OR

Award: quiz at least 70 and project at least 15. Support: quiz below 50 or project below 10. Enter in row 2 and fill down.

=IF(AND(D2>=70,E2>=15),"Award","No")
=IF(OR(D2<50,E2<10),"Support","OK")

AND needs every condition true, so Farah (91, 9) misses the award. OR needs only one, so her project mark of 9 flags her for support.

Grades with nested IF

=IF(G2>=85,"Distinction",IF(G2>=70,"Merit",IF(G2>=50,"Pass","Fail")))

Test from the highest threshold down. Aisha’s 75.6 fails the first test and passes the second, so the result is Merit. The lookup method in chapter 06 gives the same grades with a shorter formula.

SUMIF, COUNTIF and AVERAGEIF

Put a criterion with an operator in quotes. To compare against a cell, join the operator to the cell with &, as in the last row.

Text: LEFT, MID, RIGHT and FIND

Text functions return text, so 014 keeps its leading zero. For case, FIND is case sensitive and SEARCH ignores case: on Aisha, =FIND("a",B2) gives 5 and =SEARCH("a",B2) gives 1.

Dates: DAYS, TODAY and NOW

DAYS takes the end date first. With the due date in I8, =DAYS(F2,$I$8) gives -2 for Aisha, and =MAX(0,DAYS(F2,$I$8)) turns that into days late, with 0 for on-time work.

=TODAY() returns the current date and =NOW() the current date and time. Both update when the sheet recalculates, so =DAYS(TODAY(),F2) counts days since submission and changes every day.

VLOOKUP, HLOOKUP, INDEX and MATCH for G3 Computing

Module 3.2.4 lists five lookup tasks: exact lookup on an unsorted table, approximate lookup on a sorted table, classifying values with a secondary table, finding the value where a row and column meet, and finding a value’s position. VLOOKUP and HLOOKUP handle the first three. INDEX and MATCH handle the last two, and together they handle all five.

VLOOKUP with exact match

=VLOOKUP("S3C-019",$A$2:$G$7,2,FALSE)   → Darren

VLOOKUP searches the first column of the table (column A here) and returns the value from the column number you give, counted from the left edge of the table. Column 2 is B, so the result is Darren. FALSE asks for an exact match, which works on the unsorted Student IDs. A missing ID returns #N/A.

VLOOKUP with approximate match and a secondary table

=VLOOKUP(G2,$H$2:$I$5,2,TRUE)

TRUE finds the largest value in H2:H5 that is less than or equal to the lookup value. For Aisha’s 75.6 that is 70, so the result is Merit. Filled down to G7 the grades are Merit, Fail, Distinction, Pass, Merit and Pass, the same as the nested IF.

Approximate match needs the first column sorted in ascending order, smallest first. Start the thresholds at 0, because a value below the first threshold returns #N/A.

HLOOKUP does the same job on a table laid out in rows. If the thresholds ran along L1:O1 with the grades in L2:O2, =HLOOKUP(G2,$L$1:$O$2,2,TRUE) would return the grade from row 2.

INDEX with MATCH

MATCH returns a position in a range. INDEX returns the value at a position. Combined, they look up in any direction.

Working for the intersection: Darren is entry 5 in B1:B7 and Project is entry 5 in A1:G1, so INDEX returns row 5, column 5 of A1:G7, which is E5 = 15. The first row shows why INDEX with MATCH matters: the ID column sits to the left of the Name column, which VLOOKUP cannot reach.

Exam-style practice with worked answers

Use the dataset in chapter 01. Write each formula yourself, then check it against the answer.

Question 1

Count the students with a Final of 70 or more.

=COUNTIF(G2:G7,">=70")

Answer: 3. Aisha (75.6), Chloe (90.4) and Farah (81.8) qualify. Gopal’s 68.2, Darren’s 62.2 and Ben’s 48 fall below 70.

Question 2

Find the average Project mark for class 3A, rounded to 1 decimal place.

=ROUND(AVERAGEIF(C2:C7,"3A",E2:E7),1)

Answer: 18.3. (18 + 20 + 17) ÷ 3 = 55 ÷ 3 = 18.33, which rounds to 18.3.

Question 3

K2 holds a Student ID. In K3, return that student’s Class. Test with S3A-022.

=VLOOKUP(K2,$A$2:$G$7,3,FALSE)

Answer: 3A. S3A-022 is in A7. Column 3 of A:G is C, and C7 is 3A. FALSE is required because the IDs are unsorted.

Question 4

Return the name of the student with the lowest Project mark.

=INDEX(B2:B7,MATCH(MIN(E2:E7),E2:E7,0))

Answer: Farah. MIN gives 9. MATCH finds 9 at position 5 of E2:E7. Position 5 of B2:B7 is Farah.

Question 5

Use Goal Seek to find the Project mark Ben needs for a Final of 50.

Set cell G3, To value 50, By changing cell E3.

Answer: 14. 45 × 0.8 = 36, and 50 – 36 = 14.

Question 6

In J2, write a formula to fill down to J7 that shows On time, or Late followed by the days late in brackets.

=IF(F2>$I$8,"Late ("&DAYS(F2,$I$8)&")","On time")

Answer, J2 to J7: On time, Late (1), On time, Late (5), On time, Late (2). $I$8 is absolute so every row compares against the same due date.

Question 7

Rank Farah’s Final, with the highest Final ranked 1.

=RANK.EQ(G6,$G$2:$G$7)

Answer: 2. Only Chloe’s 90.4 is higher than Farah’s 81.8.

If you want someone to mark your formulas line by line, weekly G3 Computing tuition at Computhink starts with a free trial lesson.

Common spreadsheet mistakes in Paper 2

• Missing $ on a fixed cell. A weight, rate or date kept in one cell needs a reference such as $I$7, or it slides down with the fill. Check the last filled cell, where the drift is easiest to see.
• Wrong match type. VLOOKUP and HLOOKUP use approximate match when the last argument is left out, and MATCH uses 1. For IDs and names, type FALSE or 0. Keep TRUE and 1 for sorted threshold tables.
• Unsorted approximate table. Approximate match on a table out of ascending order can quietly return a wrong grade.
• Range sizes that do not line up. INDEX and MATCH ranges must start and end on the same rows. =INDEX(B2:B7,MATCH(MAX(G2:G7),G1:G7,0)) returns Darren instead of Chloe, because MATCH counts from G1 and finds 90.4 at position 4, and position 4 of B2:B7 is Darren. Give SUMIF and AVERAGEIF matching ranges too, such as C2:C7 with D2:D7.
• Counting the column index from column A. col_index_num counts from the first column of table_array. In =VLOOKUP(G2,$H$2:$I$5,2,TRUE), 2 means column I.
• DAYS arguments reversed. The end date goes first. Swapping them flips the sign of the answer.
• Criteria without quotes. Write “>=60” in quotes, and join a cell to an operator with &, as in “>=”&H3.
• Typed numbers inside formulas. =D2*0.8+E2 works until the weight changes. Keep 0.8 in I7 and reference it, so one edit updates the whole column.

To see how Module 3 fits with the other four modules, read the full G3 Computing syllabus guide. The rest of Paper 2 is Python, which the December G3 Computing Python camp works on.

Frequently asked questions

Which spreadsheet functions are in the O-Level / G3 Computing syllabus?

G3 Computing (Syllabus K349) lists 42 functions in six groups: Date and Time, Text, Logical, Lookup, Mathematical and Statistical. The Lookup group is HLOOKUP, INDEX, MATCH and VLOOKUP.

Is XLOOKUP examinable in G3 Computing?

XLOOKUP is absent from the syllabus list, as are IFS, SUMIFS and COUNTIFS. Use VLOOKUP, HLOOKUP, or INDEX with MATCH, which are the lookup functions the syllabus names.

When should VLOOKUP use TRUE and when should it use FALSE?

Use FALSE for an exact match, such as finding a Student ID in an unsorted list. Use TRUE for an approximate match on a table sorted in ascending order, such as converting a mark into a grade with threshold values.

Are spreadsheets tested in Paper 1 or Paper 2?

Paper 2, the 2 hour 30 minute lab-based paper, has one spreadsheet question and four to five programming questions. Paper 1 is a written paper that assesses all five modules, so spreadsheet knowledge can appear there too.

Is G3 Computing (Syllabus K349) the same subject as O-Level Computing?

From 2027 the subject sits under the Singapore-Cambridge Secondary Education Certificate (SEC) as G3 Computing, subject code K349. K349 replaces O-Level Computing 7155 from 2027, with the same content for this topic.

Sources and syllabus reference

• SEAB: 2027 Singapore-Cambridge Secondary Education Certificate G3 Computing Syllabus K349