How can two spreadsheet formulas use the same comparison and still produce different classifications? Logical functions combine tests in different ways. AND requires every condition to be true, OR needs at least one, NOT reverses a logical result, and IF chooses which value to return. The correct formula is the one that expresses the stated rule, not merely one that displays a plausible label.
Ministry of Education V3.0 Reference 376 develops 2027 Singapore–Cambridge SEC G3 Computing K349 spreadsheet logic through IF, AND, OR and NOT. It teaches simple and combined conditions, inclusive boundaries, nested decision bands, reference copying, blank values, misleading classifications and verification. Original fictional classroom records and a step-by-step checking method help students understand why each result appears.
This is an independent eduKateSG educational study guide, not an official MOE, SEAB or Cambridge publication. The 2027 K349 syllabus and its examinable function list define the learning boundaries. Thresholds, status labels and example records here are invented rather than official academic grades. Logical truth in a spreadsheet depends on entered values and selected conditions; it does not prove that the source data are accurate or complete.
1. What official K349 Module 3.2.1 requires
The 2027 SEC G3 Computing spreadsheet section on logical functions requires students to perform logical OR, AND and NOT, and to select between two values according to a third logical value. The official function list names AND, IF, NOT and OR. Spreadsheet comparison operators include equals, greater than, less than, greater than or equal to, less than or equal to and not equal.
Module 3.1.1 also requires appropriate relative, absolute and mixed references when formulas are copied. This matters because a logical rule with the right function but the wrong source cell produces an unsuitable result. Students should know both the truth conditions and which input each condition actually examines.
2. Start with a true-or-false comparison
Suppose B2 contains the fictional number 72. The expression B2>=60 is TRUE, while B2>=80 is FALSE. A comparison produces a Boolean outcome rather than a new numerical score. This small distinction makes all the other logical functions possible.
If the score changes to exactly 60, B2>=60 remains TRUE, but B2>60 would be FALSE. Boundary values should be tested deliberately. A condition that seems correct for 72 may be wrong for the value exactly on the threshold, especially where a rule says at least or strictly more than.
3. IF chooses between alternatives
IF(logical_test,value_if_true,value_if_false) returns one of two specified outcomes based on the test. For example, =IF(B2>=60,”Meets target”,”Below target”) returns the first label for 72 and the second for 59. A learner should evaluate the test first, then identify which branch supplies the output.
These are invented teaching labels, not an official grading policy. The rule is what matters. The true and false values may be strings or numbers, but the returned type affects what later calculations can do. Do not confuse a displayed textual label with an underlying numerical measurement.
4. IF uses a comparison, not a guess
A student’s task is to implement ‘flag a quantity below five’. A formula such as =IF(C2<5,”Review”,”OK”) implements a strict threshold. With C2 equal to five it returns OK, not Review. If the task instead says ‘five or fewer’, the comparison must be C2<=5.
The formula does not know the task’s meaning; it simply evaluates the condition written. A reliable answer states the exact operator, range and expected label for three values: below, equal to and above the boundary. This catches many mistakes before a long nested formula is constructed.
5. AND requires every condition
AND(B2>=0,B2<=100) is TRUE for a numeric value of 72 because both comparisons succeed. It is FALSE for -1 because the lower-bound test fails, and FALSE for 101 because the upper-bound test fails. This makes AND suitable for the simple idea of a value lying inside an inclusive interval.
A frequent error uses OR instead of AND for a bounded range. The expression OR(B2>=0,B2<=100) is TRUE for essentially any ordinary numeric value, because a number is typically either above zero or below one hundred. It therefore does not express the requirement of satisfying both limits.
B2: 72
=AND(B2>=0,B2<=100) -> TRUE
=OR(B2>=0,B2<=100) -> TRUE
=IF(AND(B2>=0,B2<=100),"Valid","Invalid") -> Valid6. OR accepts at least one matching alternative
OR(A2=”Pending”,A2=”Review”) is TRUE when the fictional status in A2 is either Pending or Review. It is FALSE for Closed. The logical operator includes either condition without demanding both be true simultaneously.
Write complete comparisons on each side of OR. An expression that appears to test two labels may instead use a text constant as a standalone logical argument if incorrectly assembled. The safe practice is to state each condition explicitly and check all permitted values plus one excluded value.
7. NOT reverses the logical result
If B2>=60 is TRUE, NOT(B2>=60) is FALSE. If B2 is 42, NOT(B2>=60) is TRUE. The operator reverses truth; it does not reduce the source value or change its numerical sign.
For ordinary numeric comparisons, NOT(B2>60) is equivalent to B2<=60, including equality. Students who rewrite it as B2<60 accidentally omit the boundary. A good way to verify a negation is to test the exact threshold and a value on each side.
8. Combine IF and AND for two-sided decisions
The formula =IF(AND(B2>=0,B2<=100),”Within range”,”Outside range”) returns a label based on two separate comparisons. For B2=72 it returns Within range. For B2=-1 or B2=101 it returns Outside range. The inner AND is evaluated as the IF condition.
This is a rule for synthetic numbers rather than an official validation scheme. It assumes the cell contains a usable number. If the input can be blank, a text label or another unsuitable value, the design needs explicit handling under the task’s stated requirements. A numeric interval alone does not establish that every possible input is well formed.
9. Combine IF and OR for alternatives
A fictitious workflow might mark both Pending and Review as requiring follow-up. =IF(OR(A2=”Pending”,A2=”Review”),”Follow up”,”No follow-up”) implements that two-option rule. When the status is Closed, the inner OR is FALSE and IF returns the second label.
The output is only as defensible as the status categories. If an unexpected value such as ‘Pendng’ appears, the formula may fall through to No follow-up even though a person intended Pending. That is a data-quality problem distinct from the logical mechanism.
10. Nested IF can represent several bands
An invented four-level classification might first test B2>=80 and return A, then test B2>=60 and return B, then test B2>=40 and return C, otherwise return D. This can be represented by nested IF expressions, with the most restrictive high threshold checked first.
For B2=85, the first branch returns A. For 72, the first is false and the second true, returning B. For 45, the first two are false and the third true, returning C. For 20, all three comparisons are false, returning D. These labels are only a logical exercise, not official SEC grading cutoffs.
=IF(B2>=80,"A",IF(B2>=60,"B",IF(B2>=40,"C","D")))
B2=85 -> A
B2=72 -> B
B2=45 -> C
B2=20 -> D11. Condition order determines which nested branch wins
If a broad condition such as B2>=40 is checked before B2>=80, a value of 85 satisfies the first branch and never reaches the later high-threshold test. The result can therefore be labelled incorrectly even though both comparisons are numerically correct.
When building ascending or descending categories, write an ordered threshold table first. Then test one value in every band and one value at every boundary. A formula that works for a typical middle value can still misclassify the most important exact boundary.
12. Distinguish inclusive and exclusive conditions
A range 40 through 59 inclusive is not the same as greater than 40 and less than 60. The latter excludes 40. A correct explicit test could combine B2>=40 with B2<60 using AND. If the neighbouring band begins at 60, a value exactly 60 belongs to that next band.
Students should not infer inclusion rules from a coloured band or a familiar naming convention. Use the precise wording of the task and write each boundary as a mathematical comparison. The truth table of that comparison determines how the spreadsheet behaves.
13. Blank cells need special thought
A genuinely blank input may be treated as zero in some spreadsheet numerical comparisons, so a rule that checks B2>=0 may accidentally accept an absent value. Where the requirement calls for present information, add a meaningful presence condition such as B2<>”” before applying the numerical range check.
The exact handling of formula-generated empty strings or text depends on spreadsheet semantics and the task. A basic check for nonempty content is helpful but does not prove numeric type, measurement accuracy or validity of every format. Separate data collection, validation and classification rather than concealing missing records.
14. Logical formulas do not validate the real-world interpretation
An IF formula can correctly return A for a value at least 80 under the invented rule. It cannot establish that the underlying number was measured correctly, that the assigned classification is fair or that the source record belongs to the right person. Mathematical evaluation and institutional judgement are different questions.
A responsible workbook makes the decision rule visible, documents the meaning of its fields and allows source values to be checked. Students can learn this without handling actual school results; synthetic values are enough to explore Boolean logic.
15. Relative references must follow rows
If B2 contains the first value and C2 has a logical formula, filling C2 down normally changes B2 to B3 and B4 in subsequent rows. This is appropriate when each row is classified using its own input. If a fixed threshold is stored in F1, use a suitable absolute reference to keep it unchanged.
A copied formula can be logically correct but reference the wrong row. Inspect the first and last copies and change a known source value to check that the corresponding output changes. Formula checking is stronger when it examines both syntax and data dependencies.
16. Use a truth table to test compound rules
For two Boolean conditions P and Q, AND is true only when both are true. OR is false only when both are false. NOT(P) takes the opposite truth value of P. These are the core relationships a student should be able to describe before placing them inside IF.
A truth table covers the four possible P/Q combinations: true/true, true/false, false/true and false/false. Checking all four is a more complete logical test than using one friendly number that happens to make everything true. It also reveals why AND and OR cannot usually replace each other.
17. Twelve-stage Condition–Branch–Verification protocol
This independent eduKateSG routine is a teaching aid rather than an official mark scheme. It trains learners to translate the meaning of an intended rule into its individual Boolean tests and then follow the output branch, including the input boundary and any copied-cell references.
Stage 1 — Name the intended decision
Write in plain language what makes a record satisfy the rule and which output should appear if it does not.
Stage 2 — Identify the relevant input fields
Read which worksheet cells contain the status, numerical value or fixed threshold. Do not assume the value is in the nearest-looking column.
Stage 3 — State the comparison operators
Choose >, >=, <, <=, equality or inequality according to the wording and exact inclusion of boundaries.
Stage 4 — Evaluate each atomic test
For a specific row, write TRUE or FALSE beside each simple comparison before combining them.
Stage 5 — Use AND when every requirement is necessary
An acceptable interval needs both its lower and upper bounds satisfied, not merely one of them.
Stage 6 — Use OR for genuine alternatives
A follow-up status can match either Pending or Review; state each complete comparison.
Stage 7 — Use NOT to reverse one result
Preserve the equal-to boundary when negating a strict greater-than or less-than comparison.
Stage 8 — Choose IF branches deliberately
Evaluate the logical test, then return the true or false output value without accidentally swapping them.
Stage 9 — Order nested thresholds
Check more restrictive high bands before broad lower bands when classifying in descending order.
Stage 10 — Check missing and unsuitable inputs
A blank, text value or invalid number may need its own stated rule; do not automatically treat every value as suitable.
Stage 11 — Check relative and absolute references
Allow row-specific inputs to move while anchoring shared thresholds or reference tables when required.
Stage 12 — Test every relevant boundary
Use values just below, at and above a threshold, and at least one case that makes each branch execute.
18. Twenty-four original spreadsheet-logic worked cases
Case 1 — A simple true comparison
For B2=72, the condition B2>=60 is TRUE. The logical result is Boolean, not a grade or a new numerical score.
Case 2 — The equality boundary
For B2=60, B2>=60 is TRUE but B2>60 is FALSE. The inclusion of the equality sign changes the decision exactly at the cutoff.
Case 3 — IF choosing the first output
With B2=72, IF(B2>=60,”Meets target”,”Below target”) returns Meets target. The true branch is selected.
Case 4 — IF choosing the second output
With B2=59, the same IF returns Below target. The first comparison is false, so the false branch is used.
Case 5 — Both parts of AND true
For B2=72, AND(B2>=0,B2<=100) is TRUE because both lower and upper bounds are satisfied.
Case 6 — One part of AND false
For B2=101, AND(B2>=0,B2<=100) is FALSE because the upper-bound comparison fails.
Case 7 — The lower edge of AND
For B2=-1, the lower-bound test fails and the inclusive-range AND is FALSE.
Case 8 — An incorrect OR for a range
OR(B2>=0,B2<=100) fails to restrict ordinary numeric values to the interval because a number outside one side typically satisfies the other condition.
Case 9 — Alternative status one
With A2=”Pending”, OR(A2=”Pending”,A2=”Review”) is TRUE; the first comparison suffices.
Case 10 — Alternative status two
With A2=”Review”, the same OR is TRUE; the second comparison suffices.
Case 11 — Neither status matches
With A2=”Closed”, both status comparisons are false, so their OR is FALSE.
Case 12 — NOT of a true condition
For B2=72, NOT(B2>=60) is FALSE. NOT reverses the Boolean result, not the number itself.
Case 13 — NOT at an exact boundary
For B2=60, NOT(B2>60) is TRUE because strict greater-than is false at equality.
Case 14 — Top nested classification band
With B2=85, a descending nested IF testing >=80 first returns fictional label A.
Case 15 — Second nested classification band
With B2=72, the >=80 test fails and >=60 succeeds, returning fictional label B.
Case 16 — Third nested classification band
With B2=45, >=80 and >=60 are false, but >=40 is true, returning fictional label C.
Case 17 — Fallback nested classification band
With B2=20, all three thresholds fail and the final fallback returns fictional label D.
Case 18 — Incorrect nested threshold order
If >=40 is tested first, a value of 85 can be captured by that broad branch and never reach a more specific >=80 band.
Case 19 — Inclusive lower-bound interval
A synthetic rule AND(B2>=40,B2<60) includes exactly 40 and excludes exactly 60.
Case 20 — Exclusive lower-bound interval
Replacing >=40 with >40 excludes 40. The formula is now different even though most values in the interval give the same output.
Case 21 — A missing input under a numerical rule
A genuinely empty B2 must not automatically be reported as a valid recorded zero. The workbook should have an explicit presence policy.
Case 22 — One category is misspelled
A source status of ‘Pendng’ does not equal ‘Pending’. The logical result may follow the default branch even when a human reader infers the intended word.
Case 23 — Copied input reference
An IF formula in C2 referring to B2 normally changes to B3 when filled one row down, classifying the next record.
Case 24 — A fixed shared threshold
A formula comparing B2 with $F$1 can be copied down while preserving one common threshold cell, when that is the stated task.
19. A complete fictional worksheet exercise
Build a synthetic worksheet with four numbers 35, 60, 79 and 80. Use an invented four-band nested IF rule that returns A at or above 80, B at or above 60, C at or above 40, and D otherwise. The expected labels are D, B, B and A. These are learning labels, not actual Singapore examination grades. Students should write the predicted outputs before entering any formulas and then compare their results with the rule.
Change the value 60 to 59. The selected band becomes C because the value fails the >=60 test but satisfies >=40. Next change 79 to 80, which should move it to A. These boundary cases reveal how order and inclusive comparisons determine the result. A learner who only tests a middle value may miss a condition that was written incorrectly.
20. A copied threshold reference
Put a fixed fictional target in F1, and in C2 use a formula such as =IF(B2>=$F$1,”At target”,”Below target”). When filled down, B2 should change to B3, B4 and so on, but F1 must remain the same reference. This is how a shared benchmark can be applied to every row without shifting its location.
If the dollar signs are omitted, a filled-down formula may compare B3 against F2 instead. The formula may still calculate a true or false answer, but the question it answers is different. Check the first and last copy and then change the shared threshold to verify that every row reacts to the intended benchmark.
21. When the logical rule ignores data quality
Imagine that a missing numerical record is interpreted as zero by an overly simple comparison. A range condition could then label a blank record as valid. The correct response depends on the task’s stated policy: a blank may need to be flagged as missing rather than classified with real values.
Similarly, a misspelled label ‘Pendng’ is not equal to ‘Pending’ in a straightforward exact text comparison. The resulting default output can be mechanically correct yet practically unsuitable. The lesson is to validate source data before treating a Boolean classification as a complete decision.
22. A truth table for AND and OR
For P TRUE and Q TRUE, both AND and OR are TRUE. For TRUE and FALSE, AND is FALSE while OR is TRUE. For FALSE and TRUE, the same distinction applies. For FALSE and FALSE, both are FALSE. This four-case table captures the essential definitions and is more complete than checking one example that makes both functions true.
When the task requires two conditions to be satisfied, AND is natural. When either alternative is acceptable, OR is natural. NOT reverses a result, including what happens at exact boundaries. A tutor can ask the student to invent one numerical or label example for every line of the truth table.
23. Short-answer retrieval practice
Does IF choose between two outcomes?
Yes. It uses the logical test to select the stated true or false result, and the output can be text or another suitable value.
What is required for AND to be true?
Every component condition must be true. It is suitable for checking both boundaries of a valid numerical interval.
What is required for OR to be true?
At least one component condition must be true. It is useful for multiple accepted alternatives rather than simultaneous restrictions.
Does NOT mean numerical negation?
No. It reverses a Boolean truth value. The source number is not changed merely by using NOT.
Is B2>=60 the same as B2>60?
No. Exactly 60 satisfies the inclusive condition but fails the strict comparison.
Why can a nested IF misclassify a high value?
If a broad lower threshold is tested first, it can select a branch before the more specific high threshold is reached.
Are blanks always valid zeros?
No. Missing information and an observed numerical zero are different; a workbook needs an appropriate input policy.
Why do fixed thresholds need absolute references?
They ensure that every copied formula compares with the intended shared cell, rather than a threshold address that shifts between rows.
Does a logical label prove a source is correct?
No. It only reflects the expression and input cell values. Missing, mislabeled or inaccurate records require separate checks.
How should a student verify a formula?
Evaluate each comparison, combine the Boolean results, follow IF to the selected output and test exact boundaries.
24. Counterexamples expose the wrong logical operator
If a programme must accept only values between zero and one hundred, AND(B2>=0,B2<=100) expresses two necessary conditions. OR(B2>=0,B2<=100) is not equivalent: for B2=-1, the second comparison is true, and for B2=101, the first is true. Both invalid numbers pass the OR expression. A single test at B2=50 would not reveal this because both functions happen to be true at that input.
This is a general testing principle. Whenever a formula is intended to exclude a category of inputs, construct one from that category and see whether the rule actually rejects it. A correct example is useful, but a well-chosen counterexample often reveals a deeper flaw. Tutors should ask why the distinction matters rather than simply telling the student to replace OR with AND.
25. The difference between an IF decision and data validation
A fictional workbook may report ‘Within range’ for a numerical input according to an IF test. That label is not proof that the original number is accurate, belongs to the correct row or was entered in the required format. A blank field, a copied formula pointing to a wrong column or a text entry can defeat an otherwise sensible business rule.
Separate the input contract from the decision rule. First define what counts as a valid quantity or status. Next determine the logical condition for classifying that accepted value. Finally verify the display text. When a condition handles only numeric values but the source is allowed to be blank, the problem must specify the appropriate missing-data behaviour rather than inventing a status afterwards.
26. Reading logic as a decision tree
A nested IF formula can be converted into a short decision tree. Begin with the first threshold; a true answer returns its label, while a false answer moves to the next test. Continue until one branch succeeds or the final fallback is reached. This mental model makes complicated parentheses less intimidating because it focuses on the sequence of decisions.
For a four-band example with descending thresholds 80, 60 and 40, values 85, 72, 45 and 20 reach the first, second, third and fallback branches respectively. If the lowest threshold is tested first, higher values may be captured by the wrong band. Drawing a decision tree helps students see unreachable branches without depending entirely on an on-screen result.
27. What a good test table records
A practical test table should contain the input value, expected classification, actual classification, and a brief reason for any mismatch. For a threshold of sixty, test fifty-nine, sixty and sixty-one. For a combined interval, test one below the minimum, one inside it and one above the maximum. For OR over two labels, test both permitted options and an unrelated label.
When a result differs from the expectation, inspect the exact comparison, the order of nested branches and the referenced input cell before altering the output labels. A correct-looking final text can hide a bad rule. Incremental checking identifies the first point where the programme’s meaning diverges from the problem statement.
28. From spreadsheet formulas to wider computing
IF, AND, OR and NOT express the same broad logical relationships that students encounter in algorithmic selection and Boolean programming. The syntax differs across tools, but a statement like ‘both tests must succeed’ should have the same conceptual meaning. Learning to describe the truth values before choosing a software function creates transferable reasoning.
The same discipline applies in more complex work: a condition should be understood independently, the source data should be validated, and the resulting decision should be explainable. The goal is not to make every spreadsheet look impressive. It is to produce a transparent mapping from a stated requirement through a logical test to an output that can be checked.
29. Final independent challenge
Take a fictional numeric input of 60 and evaluate four tests: B2>60, B2>=60, NOT(B2>60), and AND(B2>=40,B2<60). The outputs are FALSE, TRUE, TRUE and FALSE respectively. Now repeat at 59 and 61, explaining why particular truth values change. This small challenge covers equality and strictness without relying on any actual school grade boundaries.
Finally classify a fictional status list Pending, Review, Closed using OR of two complete equality comparisons. The first two statuses should satisfy the follow-up condition; the third should not. If a copied formula references an unintended row, a test against the expected status list will reveal it. The student should be able to describe both the Boolean result and the reason the reference was chosen.
Official syllabus and connected eduKateSG references
The official 2027 SEC G3 Computing K349 syllabus, Section 3.2.1 and its function list, defines examinable logical operations. Continue with Reference 371 — Conditional Formatting, Reference 375 — Rankings, the MOE V3.0 reference library and the immutable Clementi teaching standard.
Reviewed 29 October 2026, Singapore. All thresholds, categories and input records are fictional educational examples, not official classifications. The lasting skill is to translate a requirement into exact conditions, follow the chosen branch and test every meaningful boundary.