The Foundation
🟢 Picture a security guard at a school gate. She asks every student the same single yes-or-no question — and where you go next depends entirely on the answer. Scored 33 marks or more? You walk through the Pass door. Fewer than 33? You are sent to the Fail door. One question, two doors. That is the whole idea behind Excel’s most useful skill: logical functions.
Before a computer can decide anything, it needs a question it can answer with only TRUE or FALSE. We ask those questions with comparison operators — small symbols that compare two things:
- =B2>=33 asks “is the mark at least 33?” and replies TRUE or FALSE.
- Other questions use = (equal), <> (not equal), < (less than), > (greater than) and <= (at most).
The function that acts on the answer is IF. You hand it three things, in order: the test, what to show when it is TRUE, and what to show when it is FALSE. Written out: =IF(B2>=33,"Pass","Fail"). The guard checks once and opens exactly one door.
Sometimes one guard is not enough. AND is a row of guards where every one must say yes — pass Maths AND Science. OR is happy if any single guard says yes — a distinction in Maths OR Science. NOT simply flips a yes into a no. And when a formula might trip over an error such as #DIV/0!, IFERROR quietly catches it and shows a tidy value instead. Master these six and Excel starts making decisions for you.
=IF( marks >= 33 , Pass , Fail )
The guard checks once. TRUE opens the left door, FALSE the right.
Visual Architecture
Two quick maps: the first lays out the whole logic family, the second walks a single mark through the grade gates from A down to Fail.
View Mermaid.js source
mindmap
root((Logical Functions))
IF
one test
true or false
Nested IF
many bands
IFS
cleaner grades
AND OR NOT
combine tests
IFERROR
hide errorsView Mermaid.js source
flowchart TD
A[Marks in B2] --> B{At least 90}
B -- Yes --> C[Grade A]
B -- No --> D{At least 75}
D -- Yes --> E[Grade B]
D -- No --> F{At least 33}
F -- Yes --> G[Pass]
F -- No --> H[Fail]The Deep Dive
Every logical formula begins with a question, and every question is built from a comparison operator. Excel answers each one with the special values TRUE or FALSE. Keep this table beside you until the symbols feel automatic.
| Operator | Means | Question in Excel | When it is TRUE |
|---|---|---|---|
= | equal to | B2=33 | the mark is exactly 33 |
<> | not equal to | B2<>0 | the mark is anything but 0 |
> | greater than | B2>90 | the mark is 91 or above |
< | less than | B2<33 | the mark is 32 or below |
>= | at least | B2>=33 | the mark is 33 or above |
<= | at most | B2<=40 | the mark is 40 or below |
IF — the one-question decision. The syntax never changes: =IF(test, value_if_true, value_if_false). There are always three parts and exactly two commas. The test is anything that comes back TRUE or FALSE; the two values can be text (always inside straight quotes), numbers, or even other formulas. To mark one student: =IF(B2>=33,"Pass","Fail"). If B2 holds 72, the test is TRUE and the cell shows Pass; if it held 20, the cell shows Fail.
=IF( test , value if TRUE , value if FALSE )
One test → two outcomes. Three parts, two commas — every single time.
Nested IF — more than two outcomes. A single IF gives two answers, but grades need four: A, B, Pass and Fail. The trick is to drop a second IF into the FALSE slot of the first, so each failed test asks the next question. Build it from the top band downwards.
"C".Excel reads a nested IF from left to right and stops at the first test that is TRUE, so order matters — always go strictest first.
| Marks | First test that is TRUE | Result |
|---|---|---|
| 90–100 | B2>=90 | A |
| 75–89 | B2>=75 | B |
| 33–74 | B2>=33 | Pass |
| 0–32 | none are TRUE | Fail |
IFS — the same logic, far tidier. Once you nest three or four IFs the brackets pile up and become hard to read. IFS lets you list test-and-value pairs one after another, with no nesting at all: =IFS(B2>=90,"A",B2>=75,"B",TRUE,"C"). Excel checks each test top to bottom and returns the value beside the first TRUE. That final TRUE is a deliberate catch-all — it is always true, so it mops up everything left over. Leave it out and a mark that matches no test returns the error #N/A.
AND, OR and NOT — combining questions. These take one or more tests and boil them down to a single TRUE or FALSE, so they usually sit inside an IF. AND is strict: TRUE only when every test is TRUE. OR is generous: TRUE if at least one test is TRUE. NOT takes one test and flips it. With Maths in B2 and Science in C2:
| Function | Is TRUE when | Example (Maths 45, Science 20) | Answer |
|---|---|---|---|
AND | every test is TRUE | AND(B2>=33,C2>=33) | FALSE |
OR | any test is TRUE | OR(B2>=33,C2>=33) | TRUE |
NOT | the test is FALSE | NOT(B2>=33) | FALSE |
Wrap one inside IF to turn TRUE/FALSE into words: =IF(AND(B2>=33,C2>=33),"Pass","Fail") passes a student only when both subjects clear 33.
IFERROR — a safety net. Some formulas can trip: dividing by an empty cell gives #DIV/0!, and a failed lookup gives #N/A. IFERROR wraps a formula and says “try this; if it errors, show that instead”: =IFERROR(B2/C2,0). When C2 is a normal number the division runs as usual; if C2 were ever 0, the sheet shows a clean 0 rather than an ugly #DIV/0!. It keeps reports looking professional.
TRUE and FALSE are really 1 and 0. Behind the scenes Excel stores TRUE as 1 and FALSE as 0, which is quietly powerful — a test can be added up or fed straight into another function. IF happily sits on top of SUM, AVERAGE or anything else, as long as the test ends in TRUE or FALSE. For instance =IF(SUM(B2:C2)>=66,"Pass","Fail") passes a student on their combined total across two papers rather than each one alone.
Because logic drives the grading, it also shapes the picture your data paints. Here is how one class of 27 landed once the grade formula ran:
Expert Traps & Hacks
Logical functions are quick to write and easy to get subtly wrong. These are the slips that cost marks — and the habits that save you.
=IF(B2>=33,Pass,Fail) makes Excel hunt for named ranges called Pass and Fail, so you get #NAME?. Every text result must sit in straight double quotes: "Pass". Curly quotes copied from Word fail too — retype them inside Excel.B2>33 quietly fails the student who scored exactly 33; you want B2>=33. Whenever a boundary counts, ask “should the number on the line be included?” and choose >= or <= accordingly.B2>=33 before B2>=90 and everyone who scored 90 is caught by the 33 test and labelled Pass — the A grade never fires. Always work from the highest band down.TRUE,"C" (or TRUE,"Fail") so every leftover value has a home.=IFERROR(…,0) or =IFERROR(…,"Not found"). Clean cells beat a screen speckled with #DIV/0! and #N/A, especially in a report someone else will read.Interactive Sandbox — Formula Sandbox
Type each formula into the bar; the target cell fills in and turns green the moment your logic returns the right value.