TL;DR:
IF has three parts: a logical test, what to return when it is true, and what to return when it is false.
In a nested IF, tests run top to bottom and the first true one wins, so order them carefully.
IFS lists the test and result pairs in one flat formula, and is easier to read when available.
IF is the first formula that makes a spreadsheet decide something. It is also where many people get lost, usually when the formula grows to four or five IFs inside each other. The way out is to read it as a list of questions asked in order.
The three parts of IF
An IF formula asks one question and gives one of two answers.
=IF(logical_test, value_if_true, value_if_false)The logical test is anything that comes out as TRUE or FALSE: B2>=60, A2="Paid", C2<TODAY(). Text answers go in quotation marks. Numbers do not. If you leave out the last part, the formula returns FALSE when the test fails, which often looks like a bug.
The comparison signs are =, <> (not equal), <, >, <= and >=. You can combine tests with AND(test1, test2) when both must hold, and OR(test1, test2) when either will do.
When one IF is not enough
Sometimes there are more than two outcomes, such as grading a score into A, B, C, D or F. The trick of nesting is to put a new IF in the value_if_false slot. In effect, if it is not an A, then ask the next question.
Writing a nested IF one question at a time
Write down the bands in plain words: Score 90 or more is A. 80 or more is B. 70 or more is C. 60 or more is D. Otherwise F.
Start with the top band:
=IF(B2>=90, "A", ...). If this is true, you are done. If not, the rest of the formula decides.Put the next question in the false slot:
=IF(B2>=90, "A", IF(B2>=80, "B", ...)).Repeat until the last band: The final false value is the catch-all:
"F".Count the brackets: Each IF opens one bracket, and each needs a closing one at the very end. Four IFs, four closing brackets.
Worked example: grading scores into letter bands
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))Try five scores. For each, follow the formula from the left and stop at the first true test.
Score | Test that stops it | Grade |
|---|---|---|
93 | 93 >= 90 is true | A |
89 | 89 >= 90 false, 89 >= 80 true | B |
70 | 70 >= 90 false, >= 80 false, 70 >= 70 true | C |
59 | fails 90, 80, 70 and 60 | F |
85 | 85 >= 90 false, 85 >= 80 true | B |
Note the score of 70. It lands in C, not D, because the test is >=, which includes the boundary. If you wrote >70 the 70 would fall through to D. Boundary scores are where most mistakes hide, so always test the exact edges: 90, 80, 70 and 60.
Why the order matters
Suppose you wrote the tests the other way round, starting with B2>=60. A score of 93 passes that first test, so the formula returns D and never reaches the A question. Every score of 60 or above would become D.
The rule is that Excel and Google Sheets stop at the first true test. So when you test with >=, go from the highest threshold down. If you test with <, go from the lowest threshold up. Choose one direction and keep to it.
IFS as an alternative
Both Excel (in current versions) and Google Sheets have IFS, which takes pairs of test and result with no nesting. If your Excel is old, it may not have it, so check before sending the file to someone else.
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F")The last pair, TRUE, "F", is the catch-all. Without it, a score of 59 would produce an error (#N/A), because IFS has no default. The same rule about order applies, since IFS also stops at the first true test.
Another option for bands is a small lookup table with the thresholds 0, 60, 70, 80, 90 and grades F, D, C, B, A, read with an approximate-match lookup. That moves the thresholds out of the formula, so changing them later is a table edit.
Mistakes to avoid
Numbers in quotation marks.
"90"is text and will not compare as you expect.Missing or extra brackets. If the formula gives a syntax error, count them.
Typed grades with different spelling. The returned text must be exactly what you want, including capitals.
Blank cells. A blank score compares as 0 and becomes F. Wrap the formula:
=IF(B2="", "", ...).Too deep. If you are nesting more than four or five, use IFS or a lookup table instead.
Checking your formula
A good habit is to put your test cases in a column beside the real data, as in the table above, and check that each gives the grade you worked out by hand. If you are stuck on a particular formula on your screen, you can ask DuckyHelper out loud while the pill is open and it will look at the sheet; but the order of tests is still yours to reason through.
Frequently asked questions
How many IFs can I nest?
Modern versions of Excel and Google Sheets allow many levels, but readability falls long before the limit. Past about four, switch to IFS or a lookup table.
Why does my IF return FALSE?
You probably left out the value_if_false part and the test was not true. Add the third argument.
Get started
