Topic 10

Logical Functions

A logical question is any question with exactly two possible answers: YES or NO, TRUE or FALSE. Logical functions let you ask those questions inside a formula and return a different result depending on the answer.

5 functions Practice file: if.xlsx 3 tasks · task1, task2, task3 sheets

What Does "Logical" Mean?

A logical question always has a yes/no answer. In Excel, every logical test returns exactly one of two values: TRUE or FALSE. You can't get "maybe."

"Is this employee Full Time?" → TRUE or FALSE
"Is the salary above 50,000?" → TRUE or FALSE
"Is the rating equal to 5?" → TRUE or FALSE

IF then uses that TRUE/FALSE to decide which of two results to return. AND and OR combine multiple questions. IFERROR handles the case where a formula fails.

Returns one value if true, another if false

AND

TRUE only when all conditions are true

TRUE when at least one condition is true

Nested IF

IF inside IF — for multiple outcome levels

IFERROR

Returns a fallback value when a formula errors

IF — The Foundation

Ask a question, get one of two answers depending on the result.

= IF(logical_test, value_if_true, value_if_false)

logical_test The question — any expression that returns TRUE or FALSE. Uses comparison operators: = <> > < >= <=

value_if_true What to return when the test is TRUE. Can be a number, text, another formula, or even another IF.

value_if_false What to return when the test is FALSE. Use 0 or "" for nothing, or nest another IF here for multiple outcomes.

Example — 500 bonus for Full Time, 0 for everyone else:

= IF([@Status] = "Full Time", 500, 0)

AND & OR — Combining Conditions

Both functions combine multiple logical tests and return a single TRUE or FALSE — which is then passed to an IF.

AND

All conditions must be TRUE

Like a security door that requires both a keycard and a PIN — one alone is not enough.

Condition 1	Condition 2	Result
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE

At least one condition must be TRUE

Like a door that opens with either a keycard or a PIN — either one is sufficient.

Condition 1	Condition 2	Result
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

AND inside IF — bonus only for Full Time AND rating 5:

= IF(AND([@Status] = "Full Time", [@[Job Rating]] = 5), 1000, 0)

OR inside IF — bonus for Half-Time OR Hourly workers:

= IF(OR([@Status] = "Half-Time", [@Status] = "Hourly"), 300, 0)

Nested IF — Multiple Outcomes

When there are more than two possible outcomes, you place another IF in the value_if_false position. Each inner IF handles the next level. Excel evaluates them in order — the first TRUE it finds wins.

= IF(rating = 5, 1500,
   IF(rating = 4, 1000,
      IF(rating = 3, 750,
         IF(rating = 2, 500,
            0))))

💡

Count your closing parentheses

Each IF needs a closing ). With 4 nested IFs you need 4 closing brackets at the end. Excel's colour coding helps — each opening bracket is a different colour matching its closing one.

Excel 365 Alternative

IFS() — cleaner syntax for multiple outcomes

Instead of nesting, list all condition-result pairs. No closing bracket pyramid required.

= IFS([@[Job Rating]] = 5, 1500,
     [@[Job Rating]] = 4, 1000,
     [@[Job Rating]] = 3, 750,
     [@[Job Rating]] = 2, 500,
     TRUE, 0) ← TRUE as the last condition = "everything else"

IFERROR — Handle Errors Gracefully

When a formula can't find a result — like VLOOKUP returning #N/A, or a division by zero giving #DIV/0! — IFERROR catches the error and returns a clean value instead.

= IFERROR(value, value_if_error)

value The formula to try — e.g. a VLOOKUP, a division, or any calculation that might fail.

value_if_error What to show if the formula errors. Use "" for blank, 0 for zero, or a descriptive message like "Not found".

Example — clean up a VLOOKUP that might not find a match:

= IFERROR(VLOOKUP(A1, table1, 2, 0), "Not found")

⚠️

Don't use IFERROR to hide real errors

IFERROR catches all errors — including ones caused by a mistake in your formula. If a lookup stops working because of a typo, IFERROR silently shows "Not found" instead of alerting you. Use it intentionally, not as a blanket error suppressor.

Practice — if.xlsx

Download if.xlsx

Sheet: task1 · Simple IF

Give 500 bonus to Full Time workers, 0 to everyone else

Fill the Bonus column using a single IF. The condition is whether the Status column equals "Full Time".

= IF([@Status] = "Full Time", 500, 0)

Check your results — enter the Bonus for each employee

Employee	Status	Bonus
Nicholson, Lee	Half-Time
Cole, Elbert	Full Time
Allen, Thomas	Contract

Sheet: task2 · Nested IF

Bonus based on Job Rating — five levels

Fill the Bonus column using nested IF (or IFS). Each rating maps to a different bonus amount.

Rating	Bonus
5	1,500
4	1,000
3	750
2	500
1	0

= IF([@[Job Rating]]=5, 1500,
  IF([@[Job Rating]]=4, 1000,
    IF([@[Job Rating]]=3, 750,
      IF([@[Job Rating]]=2, 500, 0))))

Check your results

Employee	Rating	Bonus
Nicholson, Lee	4
Allen, Thomas	5
Palmer, Terry	1

Sheet: task3 · IF + AND

Give 1,000 bonus to Full Time workers with rating 5 only

Both conditions must be true at the same time — Full Time AND rating 5. Any employee meeting only one condition gets 0.

= IF(AND([@Status] = "Full Time", [@[Job Rating]] = 5), 1000, 0)

📌

The AND trap — check both conditions separately

Allen, Thomas has rating 5 — but his status is Contract, not Full Time. He gets 0, not 1000. Only employees who satisfy both conditions qualify. This is the key test of understanding AND.

Check your results

Employee	Status · Rating	Bonus
House, Paul	Full Time · 5
Allen, Thomas	Contract · 5
Cole, Elbert	Full Time · 4

Topic 09 — Data Validation

Topic 11 — Coming soon