18.1 The IF Function

IF tests a condition and returns one of two values depending on whether the condition is TRUE or FALSE.

Syntax

Basic IF Examples

Text vs Number Comparisons

• When comparing text, enclose it in double quotes: IF(C2="Finance",...)
• Text comparisons are not case-sensitive: "FINANCE" and "finance" are treated the same
• When comparing numbers, do not use quotes: IF(B2>50000,...) — using quotes would compare as text and give wrong results
• To check if a cell is blank: IF(A2="",...) or IF(A2="",...)
• To check if a cell is not blank: IF(A2<>"",...)

Returning Different Data Types

IF can return text, numbers, dates, formulas, or blank — in any combination in the true and false positions:

18.2 Nested IF — Multiple Conditions in Sequence

A nested IF places one IF formula inside another. This lets you test multiple conditions in sequence, assigning a different result to each outcome. Think of it as a chain of "else if" decisions.

Nested IF Syntax

Excel evaluates the conditions from left to right. As soon as a condition is TRUE, it returns that result and stops. If no condition is TRUE, it returns the last default_result.

Grading Example (5 Grade Bands)

SA Tax Bracket Example

Nested IF Limitations

• Maximum 64 levels of nesting in Excel 2007+ (7 in earlier versions)
• Long nested IFs become difficult to read, audit, and maintain
• For many conditions, consider IFS (Section 18.5) or SWITCH (Section 18.6) instead
• Common mistake: testing conditions in the wrong order. Always test from highest to lowest (or lowest to highest) consistently, and test each range boundary precisely.

18.3 The AND Function

AND returns TRUE only when all of its conditions are TRUE. If even one condition is FALSE, AND returns FALSE. It is used inside IF to test multiple conditions that must all be met simultaneously.

Syntax

AND Examples — Standalone

AND Inside IF — Practical Examples

18.4 The OR Function

OR returns TRUE when at least one of its conditions is TRUE. It only returns FALSE when ALL conditions are FALSE. Use OR when any one of multiple criteria is sufficient.

Syntax

AND vs OR — The Key Difference

OR Inside IF — Practical Examples

18.5 The NOT Function

NOT reverses a logical value — TRUE becomes FALSE and FALSE becomes TRUE. It is used when it is easier to define what something is not than what it is.

Syntax

NOT Examples

NOT is less commonly used alone but becomes useful when combined with ISBLANK, ISNUMBER, ISTEXT, ISERROR, or complex AND conditions that are easier to express as their negative.

18.6 The IFS Function (Excel 2019+)

IFS tests multiple conditions in sequence and returns the result of the first TRUE condition. It replaces long nested IF chains with a cleaner, flat structure — far easier to read and maintain.

Syntax

• Each pair of arguments is: condition → result if that condition is TRUE
• IFS stops at the first TRUE condition and returns its value
• If no condition is TRUE, IFS returns #N/A. To add a catch-all default, end with: TRUE, "default value"

IFS vs Nested IF — Same Grading Formula

More IFS Examples

18.7 The SWITCH Function (Excel 2019+)

SWITCH compares a single expression against a list of values and returns the result paired with the first match. Unlike IFS which evaluates conditions, SWITCH tests one value for exact equality against alternatives — like a lookup table without VLOOKUP.

Syntax

SWITCH Examples

18.8 IFERROR and IFNA — Handling Formula Errors

Formula errors like #N/A, #DIV/0!, #VALUE!, and #REF! are distracting and can cascade through a spreadsheet. IFERROR and IFNA intercept these errors and replace them with a friendlier alternative.

IFERROR — Catch Any Error

IFNA — Catch #N/A Only

IFNA catches only #N/A errors (the "not found" error). Other errors like #DIV/0! or #VALUE! are still displayed. Use IFNA instead of IFERROR when you want to see other errors (they might indicate real data problems) but hide "not found" gracefully.

18.9 Combining Logical Functions

The real power emerges when you combine AND, OR, NOT, and IFERROR with IF in the same formula to handle complex real-world decision logic.

IF + AND + OR Together

SA Payroll Scenarios Using Logical Functions

Data Validation Checks Using Logical Functions

18.10 IS Functions — Testing Cell Types

Excel provides a family of IS functions that return TRUE or FALSE based on the type of content in a cell. They are particularly useful inside IF to handle mixed data types gracefully.

IS Functions in Practice

18.11 Quick Self-Check

Q1: Write an IF formula that assigns a grade letter to a score in B2: A (90+), B (80–89), C (70–79), D (60–69), F (below 60).

✓ Using nested IF: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))). Using IFS (cleaner): =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F"). Both produce the same result. The conditions are tested from highest down so a score of 92 matches ≥90 first and returns "A" without evaluating the rest. The TRUE at the end of IFS is the catch-all default for anything below 60.

Q2: An employee qualifies for a year-end bonus if their performance rating (column C) is "Exceeds" or "Outstanding" AND they have worked for at least 6 months (column D = months employed). Write the IF formula to calculate 10% of their salary (column B) as bonus, or 0 if not eligible.

✓ =IF(AND(OR(C2="Exceeds",C2="Outstanding"),D2>=6),B2*0.1,0). The inner OR checks whether the rating is either "Exceeds" or "Outstanding". The AND ensures that BOTH the OR condition (good rating) AND the minimum service (6+ months) must both be true. If either fails, the AND is FALSE and the formula returns 0. If both pass, it returns 10% of the salary in B2.

Q3: A VLOOKUP formula in column D sometimes returns #N/A when a product code is not in the price list. Your manager wants the cell to show "Price TBC" instead of the error. Rewrite the formula to handle this.

✓ Wrap the VLOOKUP with IFNA: =IFNA(VLOOKUP(A2,$G$2:$H$100,2,0),"Price TBC"). IFNA catches only the #N/A error (not found) and replaces it with "Price TBC". If there is a genuine formula error (like a #VALUE! from a wrong data type), that still shows — alerting you to a real problem. If you want to catch ALL errors and show "Price TBC" for any issue, use IFERROR instead: =IFERROR(VLOOKUP(A2,$G$2:$H$100,2,0),"Price TBC").

Q4: You have a department code in B2 (possible values: "FIN", "HR", "OPS", "IT", "MKT"). Write a SWITCH formula that translates the code to the full department name. If the code is unknown, show "Unknown Department".

✓ =SWITCH(B2,"FIN","Finance","HR","Human Resources","OPS","Operations","IT","Information Technology","MKT","Marketing","Unknown Department"). SWITCH compares B2 against each value in sequence. When it finds a match, it returns the paired result. The last argument "Unknown Department" is the default returned when none of the codes match. This is much cleaner than nested IFs for exact-match lookups.

Q5: What is the difference between AND and OR? Give a real payroll example where you would use AND versus a real example where you would use OR.

✓ AND returns TRUE only when ALL conditions are true; OR returns TRUE when ANY ONE condition is true. AND example: An employee qualifies for overtime pay only if they are NOT a manager AND worked more than 45 hours that week — BOTH conditions must be met: =IF(AND(C2<>"Manager",D2>45),(D2-45)*E2*1.5,0). OR example: An employee is exempt from the medical aid deduction if they are on contract OR are working less than 20 hours per week — either one is sufficient: =IF(OR(C2="Contract",D2<20),0,B2*0.01).

Q6: A column of data should contain only numbers, but some cells have text entries (data entry errors). Write a formula using an IS function that flags each cell as "OK" or "Not a number".

✓ =IF(ISNUMBER(A2),"OK","✗ Not a number"). ISNUMBER(A2) returns TRUE if A2 contains any numeric value (whole number, decimal, date, time, percentage) and FALSE if it contains text, is blank, or contains an error. The IF then returns "OK" for valid numeric cells and "✗ Not a number" for everything else. To count the total number of text entries across a range, use: =COUNTIF(A2:A100,"*") — this counts cells with any text. Or more precisely: =SUMPRODUCT(--ISTEXT(A2:A100)) counts all text cells including those that don't match a specific pattern.

✓ Module 18 Complete — You Have Learned:

• IF function — syntax (logical_test, value_if_true, value_if_false); returning text/numbers/formulas/blanks; text vs number comparisons (quotes for text, none for numbers); blank/not-blank tests; 6 practical formula examples
• Nested IF — placing IF inside IF for multiple conditions; 5-grade grading example; SA tax bracket example; 64-level nesting limit; order matters (test most specific first); limitations of long chains
• AND function — TRUE only when ALL conditions true; syntax; standalone examples; AND inside IF (5 practical SA examples); the AND + OR combination pattern
• OR function — TRUE when ANY ONE condition true; AND vs OR comparison table; OR inside IF (5 practical examples including weekend check, VAT exemption)
• NOT function — reverses logical value; combining with ISBLANK; NOT(AND) = OR of opposites (De Morgan's law)
• IFS function (Excel 2019+) — flat multi-condition testing; TRUE as catch-all default; IFS vs nested IF comparison; leave entitlement, credit rating, delivery priority examples
• SWITCH function (Excel 2019+) — exact-match lookup against a list; day name, department code, quarter mapping examples; SWITCH vs IFS (equality vs range comparisons)
• IFERROR — catch any error type; practical VLOOKUP, division, percentage change error handling; returning 0, "", or custom text
• IFNA — catch only #N/A errors; when to choose IFNA over IFERROR (preserve other errors for debugging)
• Combining logical functions — IF + AND + OR together; SA payroll scenarios (UIF exemption, overtime, leave approval, net pay protection with MAX); data validation checks (complete record, phone format, salary range)
• IS functions — 7 IS functions (ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, ISLOGICAL, ISFORMULA); practical uses for data validation and error handling; SUMPRODUCT(--ISERROR()) for counting errors