Lesson 2: Turn conditions into decisions
Logical functions return TRUE or FALSE, or choose a result based on a test. In the examples, A2 contains a score and B2 contains an order status.
Example: if A2 is 82, =IF(A2>=70,"Pass","Retry") returns Pass. Text results need quotation marks.
Logical and error formulas
Use exact comparisons and explicit fallback results. IFERROR catches every error, so reserve it for cases where a friendly fallback is meaningful; IFNA catches only #N/A.
19 formulas shown
| Function | What it returns | Example |
|---|---|---|
| IF | One result when a test is true, another when false. | =IF(A2>=70,"Pass","Retry") |
| IFS | First result whose condition is true. | =IFS(A2>=90,"A",A2>=80,"B",TRUE,"C") |
| AND | TRUE only when every test is true. | =AND(A2>=70,B2="Paid") |
| OR | TRUE when at least one test is true. | =OR(B2="New",B2="Priority") |
| NOT | Reverses TRUE/FALSE. | =NOT(A2="Closed") |
| XOR | TRUE when an odd number of tests are true. | =XOR(A2>0,B2>0) |
| SWITCH | Matches one expression to listed values. | =SWITCH(C2,"S","Small","M","Medium","Other") |
| IFERROR | Replaces any formula error with a chosen result. | =IFERROR(A2/B2,0) |
| IFNA | Replaces #N/A only. | =IFNA(XLOOKUP(E2,A:A,B:B),"Not found") |
| ISBLANK | Tests whether a cell is truly empty. | =IF(ISBLANK(A2),"Missing","Entered") |
| ISNUMBER | Tests whether a value is numeric. | =ISNUMBER(A2) |
| ISTEXT | Tests whether a value is text. | =ISTEXT(A2) |
| ISNONTEXT | TRUE for non-text values, including blanks. | =ISNONTEXT(A2) |
| ISLOGICAL | Tests for TRUE or FALSE. | =ISLOGICAL(A2) |
| ISERROR | Tests for any Excel error value. | =ISERROR(A2) |
| ISERR | Tests for errors other than #N/A. | =ISERR(A2) |
| ISNA | Tests specifically for #N/A. | =ISNA(A2) |
| ERROR.TYPE | Returns a number identifying an error type. | =IFERROR(ERROR.TYPE(A2),0) |
| TRUE / FALSE | Return the logical values TRUE and FALSE. | =TRUE() returns TRUE. |
Copy a safe division pattern: =IFERROR(A2/B2,0)
Nested decisions and good error messages
Prefer IFS or SWITCH when many branches make nested IF difficult to audit. Use a deliberate final condition so unmatched values do not silently receive the wrong category.
- Test blank inputs before doing arithmetic.
- Return a meaningful label such as
"Check quantity"instead of masking every problem as zero. - Use IFNA for a lookup miss and let other errors remain visible during development.
Progress stays in this browser.