edubun
English
English한국어EspañolPortuguêsFrançaisDeutschItalianoРусский
Home›Spreadsheets and data›Spreadsheet formulas

AND, OR and nested IF

A score of 81 with 70% attendance passes the score test and fails the attendance test. AND needs both; OR needs only one.

BackgroundA made-up example. A teacher builds a grade sheet the night before marks are due. The rule is A for 90 and up, B for 80, C for 70, D for 60 and F below that. She writes the tests in the order they come to mind, lowest first: =IF(B2>=60, "D", IF(B2>=70, "C", ...)). Every student with 60 or more gets a D, including one who scored 93. Nothing shows an error. A student points it out the next morning. The fix is to turn the order round, and the reason is worked out below.

Two conditions at once: AND and OR

The same teacher has a second rule. To pass, a student needs a score of at least 60 and attendance of at least 80%. AND takes one or more tests (Excel allows up to 255) and returns TRUE only when every one of them is TRUE: =AND(B2>=60,C2>=80). OR returns TRUE when at least one test is TRUE, so =OR(B2>=90,C2=100) could flag a student for a prize who either scored very high or never missed a class. NOT flips TRUE to FALSE and back, which suits rules phrased with "unless".

On their own, AND and OR give only TRUE or FALSE. Put them inside IF to turn that into words: =IF(AND(B2>=60, C2>=80), "Yes", "No"). The tests inside can point at different columns, compare text, or use calculations. AND and OR skip text and empty cells in ranges, and if they find nothing they can test at all, they return #VALUE!. The table below runs the two tests, a score of at least 60 and attendance of at least 80, on four students.

Say the rule in plain words before you write it. "Pass if the score is at least 60 and attendance is at least 80" maps straight onto AND; "pass if either test is passed" maps onto OR. A rule that mixes the two, such as "at least 60, and either 80% attendance or a doctor's note", needs one function inside the other: =AND(B2>=60, OR(C2>=80, D2="note")). Write the inner part first. Check that it gives TRUE and FALSE where you expect, and only then wrap it.

ScoreAttend %ANDOR
9395TRUETRUE
8170FALSETRUE
6688TRUETRUE
5570FALSEFALSE

Why the 93 in the story became a D

IF gives two outcomes. For more, put another IF in the place of the false answer, and the grade scale becomes =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))). The spreadsheet tries the first test and moves to the next only when it fails. That is exactly what went wrong in the story. With B2>=60 first, a 93 passes the very first test and stops at D. The higher tests are never read. Start with the highest threshold and work down.

Count the brackets: four IFs need four closing brackets at the end. Excel's limits page allows 64 nested levels of functions, but beyond three or four levels a formula gets hard to read and easy to break. Newer versions of both programs have IFS, which lists test-and-answer pairs one after another without nesting, and Google's IF help links to it. For a long scale the lookup table in the next unit is usually clearer still, because the thresholds sit in cells where anyone can see and change them.

In the lab below, the grade column uses four nested IFs and the pass column uses IF with AND. Fill both down and compare Ben with Dev. Ben has the better score and fails on attendance. Dev turns up but scores below 60.

Test the edges. For a grade scale, try exactly 90, 89.9, 60 and 59.9 before trusting the formula. Most mistakes hide at a boundary, where > and >= give different answers.
Loading

Drag to turn. Scroll or pinch to zoom.

Write the grade formula in D2 and fill down. Try putting the 60 test first and watch Ana drop to a D.
Common mistakes
  • Starting a nested IF with the lowest threshold. The first TRUE test wins. With B2>=60 first, every score of 60 or more stops at D.
  • Writing =IF(B2>=60 AND C2>=80, ...). AND is a function that takes the tests as arguments, not a word placed between them. Write AND(B2>=60, C2>=80).
  • Using OR when the rule says both. A student with 81 points and 70% attendance passes with OR and fails with AND. Say the rule aloud: an "and" in the sentence means AND.
  • Losing count of the closing brackets. Four nested IFs need four ) at the end. With one missing, the formula is not accepted.
Teacher's noteNested IF is often the first formula learners cannot read at a glance. Build it in steps on the board: a single IF for A or "not A", then replace "not A" with a second IF, and so on, testing after each step. Have learners predict the grade for 93, 85, 60 and 59.9 before they fill down. The wrong-order story is worth telling. It fails without any error, and the fix is plain once seen.
  1. B2 is 81 and C2 is 70. What does =AND(B2>=60,C2>=80) return?
    • FALSE
    • TRUE
  2. Same cells. What does =OR(B2>=60,C2>=80) return?
    • TRUE
    • FALSE
  3. What does =NOT(5>3) return?
    • FALSE
    • TRUE
  4. B2 is 93. What does =IF(B2>=90, "A", IF(B2>=80, "B", "C")) return?
  5. B2 is 85. Same formula. What does it return?
  6. B2 is 93. What does the wrongly ordered =IF(B2>=60, "D", IF(B2>=90, "A", "F")) return?
  7. How many closing brackets end a formula with four nested IFs and nothing else?
  8. How many nested levels of functions does Excel allow?
  9. B2 is 66 and C2 is 88. What does =IF(AND(B2>=60, C2>=80), "Yes", "No") return?
  10. Which scores best test a "60 or more passes" formula?
    • 60 and 59.9
    • 100 and 0
    • 75 and 76
    • 30 and 90
Key points
  • AND: every test TRUE. OR: at least one TRUE. NOT: the opposite.
  • Use them inside IF to turn TRUE or FALSE into words.
  • Nested IF: put the next IF in the false slot, highest threshold first.
  • Test the boundaries. For long scales, a lookup table is clearer.
Sources for this course (19)
  • Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
  • Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
  • Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
  • Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
  • Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
  • Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
  • Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
  • Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
  • Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
  • Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
  • Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
  • Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
  • Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
  • Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
  • Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
  • Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
  • Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
  • Ablebits (IF without value_if_false returns FALSE)
  • Spreadsheet Class (Sheets circular dependency shows #REF!)

Checked: 2026-10-19