Why Your IF Formula Returns the Wrong Result: 6 Causes and Fixes

Q: Why does IF say a number is greater than 100 when it isn't?

The number is almost certainly stored as text. Excel treats any text as larger than any number in a comparison. Check with `=ISNUMBER(cell)` and convert with `VALUE()`.

Q: Why does my IF formula show FALSE or 0?

The third argument, value_if_false, is missing (shows FALSE) or empty (shows 0). Add it, for example `""` for a blank result.

Q: How many IFs can I nest?

Excel 2007 and later allow up to 64 nested IFs, but a formula becomes hard to check long before that. For more than three or four bands, use LOOKUP with a band table, or IFS in Excel 2019 and later.

Q: Is the IF comparison case-sensitive?

No. `"YES"="Yes"` is TRUE. Use `EXACT(text1,text2)` inside the IF when upper and lower case must count as different.

Q: Does the test workbook work in Google Sheets?

It opens and calculates there, and the fixed formulas all work. Google Sheets compares text with numbers differently from Excel, though, so the "broken" results on sheets 1 and 5 may not reproduce. Causes 1 and 5 describe Excel's behaviour.

Last updated: 5 October 2026 · Download: if-wrong-result-test-workbook.xlsx, with one sheet per cause, a broken formula and a fixed formula on each.

IF is the simplest decision in Excel: test something, then return one value if it's TRUE and another if it's FALSE. When an IF gives the wrong answer, the formula is almost never broken. The test is answering a different question from the one you meant to ask.

This guide covers six common reasons an IF formula returns the wrong result without showing any error. Each section has the broken formula, a quick check, and the fix. Every example is in the downloadable workbook: the inputs are in column I, the formulas in column B, and column C shows the answer you should get.

First: look at the test on its own

Copy just the condition out of your IF into an empty cell. For example, if the formula is =IF(I6>100,"High","Low"), enter:

=I6>100

It returns TRUE or FALSE. If that's not the answer you expect for the value in I6, the problem is in the test, not in the IF. Every cause below is a reason the test says something different from what it appears to say.

Cause 1: the number is stored as text

Sheet "1 Text numbers" in the workbook.

I6 holds 50, but stored as text, which is common with imported data, CSV files and values pasted from web pages. When Excel compares text with a number, it treats text as larger than any number. So this test is TRUE:

=IF(I6>100,"High","Low")          → High   (should be Low)
=IF(VALUE(I6)>100,"High","Low")   → Low

No error appears, and a score of 50 is labelled High.

Test: =ISNUMBER(I6) returns FALSE for a number stored as text. Text numbers also align to the left of the cell by default and often show a small green triangle in the corner.

Fix: convert inside the test with VALUE(I6) or --I6, or fix the data once: select the column and use Data › Text to Columns › Finish. The same problem breaks lookups and SUMIFS too. See Why SUMIFS returns 0.

Cause 2: nested IFs check the bands in the wrong order

Sheet "2 Nested order".

The grading bands are A for 90 and above, B for 80+, C for 70+, D for 60+, and F otherwise. This formula checks the lowest band first:

=IF(I6>=60,"D",IF(I6>=70,"C",IF(I6>=80,"B",IF(I6>=90,"A","F"))))   → D   (85 should be B)

A nested IF stops at the first TRUE test. 85 is greater than or equal to 60, so the formula returns D and never reaches the B test. Any score of 60 or more comes out as D.

Test: try one value from each band, such as 95, 85, 75, 65 and 40. If several bands return the same letter, the order is wrong.

Fix: with "greater than" tests, check the highest threshold first and work down:

=IF(I6>=90,"A",IF(I6>=80,"B",IF(I6>=70,"C",IF(I6>=60,"D","F"))))   → B

With more than three or four bands, a lookup is easier to read and change:

=LOOKUP(I6,{0,60,70,80,90},{"F","D","C","B","A"})   → B

The thresholds must be in ascending order. LOOKUP returns the grade for the largest threshold that's less than or equal to the score. In Excel 2019 and later, IFS is another option, and the highest-first rule applies there too.

Cause 3: an empty cell counts as zero

Sheet "3 Blank cells".

I6 is empty because the student hasn't taken the test yet. In a comparison, Excel treats an empty cell as 0:

=IF(I6<50,"Fail","Pass")   → Fail   (they haven't taken it)

0 is less than 50, so every blank row is marked Fail. The same thing happens with stock levels, overdue checks, budgets and any test where 0 counts as a real result.

Test: =ISBLANK(I6) returns TRUE for an empty cell. Look at whether the rows with the surprising answer are the blank ones.

Fix: handle the empty cell first:

=IF(I6="","Not taken",IF(I6<50,"Fail","Pass"))   → Not taken

Use I6="" rather than ISBLANK(I6) if the cell might contain a formula that returns an empty string. ISBLANK returns FALSE for those, while I6="" catches both.

Cause 4: text that looks equal but isn't

Sheet "4 Hidden text".

I6 contains Yes with a trailing space, typed by accident or left over from a form or export. The test I6="Yes" compares every character, so it's FALSE:

=IF(I6="Yes","Ship","Hold")         → Hold   (should be Ship)
=IF(TRIM(I6)="Yes","Ship","Hold")   → Ship

Test: =LEN(I6) returns 4, although "Yes" has 3 letters.

Fix: compare TRIM(I6), which removes leading and trailing spaces. For text pasted from web pages, the space may be a non-breaking space that TRIM doesn't remove. The VLOOKUP #N/A guide shows how to detect and remove it.

Upper and lower case work the other way round. The = comparison ignores case, so "YES"="Yes" is TRUE. If case matters, use EXACT:

=IF(I7="Yes","Ship","Hold")          → Ship   (I7 contains YES)
=IF(EXACT(I7,"Yes"),"Ship","Hold")   → Hold

Cause 5: a date typed as text inside the condition

Sheet "5 Text dates".

I6 holds the date 15 March 2026. The goal is to flag anything after 1 February 2026:

=IF(I6>"2026-02-01","Late","On time")       → On time   (should be Late)
=IF(I6>DATE(2026,2,1),"Late","On time")     → Late

Inside the quotes, "2026-02-01" is a piece of text, not a date. Excel stores real dates as numbers, and when it compares a number with text, the number is always the smaller one. So the test is FALSE for every date, and nothing is ever flagged.

Test: look for a date in quotation marks inside the condition.

Fix: build the date with DATE(year,month,day). Better still, put the cut-off date in its own cell and refer to that cell:

=IF(I6>I7,"Late","On time")   → Late   (I7 holds 1 Feb 2026)

Then nobody has to edit the formula when the cut-off changes. If the dates in your data are stored as text, which is common in CSV exports, the comparison fails the same way. Convert them with Data › Text to Columns.

Cause 6: value_if_false is left out

Sheet "6 Missing else".

IF takes three arguments, but only the first two are required. With 40 units sold and a bonus threshold of 100:

=IF(I6>100,"Bonus")      → FALSE
=IF(I6>100,"Bonus",)     → 0
=IF(I6>100,"Bonus","")   → (blank)

If the third argument is missing, a false test shows the word FALSE. If it's present but empty, which is just a trailing comma, it shows 0. In a report, a column of FALSE or 0 looks like an error or like a real value of zero.

Fix: always write the third argument. Use "" for a cell that should look empty, or a clear label such as "No bonus".

A related trap: testing a range with 10<=A1<=20

Formulas like =IF(10<=A1<=20,"In range","Out") don't work in Excel. Excel works left to right: 10<=A1 becomes TRUE or FALSE, and then Excel compares that TRUE or FALSE with 20. Excel ranks logical values above all numbers, so that second comparison is always FALSE, and the formula returns "Out" whatever is in A1.

Use AND to test both limits:

=IF(AND(A1>=10,A1<=20),"In range","Out")

Checklist

When an IF returns the wrong result:

Put the condition alone in a cell. Does it return the TRUE or FALSE you expect?
Is the value a number stored as text? Check with ISNUMBER (cause 1).
In a nested IF, is the highest threshold tested first? (cause 2)
Are the wrong answers on blank rows? Handle "" first (cause 3).
Does LEN show extra characters in text you're comparing? (cause 4)
Is there a date in quotes in the condition? Use DATE() or a cell (cause 5).
Does every IF have its third argument? (cause 6)

For a long nested IF someone else wrote, the Formula Explainer lists each test in the order Excel evaluates it. To colour cells by the same kind of condition, the Conditional Formatting Builder writes the rule formula for you.

FAQ

Why does IF say a number is greater than 100 when it isn't? The number is almost certainly stored as text. Excel treats any text as larger than any number in a comparison. Check with =ISNUMBER(cell) and convert with VALUE().

Why does my IF formula show FALSE or 0? The third argument, value_if_false, is missing (shows FALSE) or empty (shows 0). Add it, for example "" for a blank result.

How many IFs can I nest? Excel 2007 and later allow up to 64 nested IFs, but a formula becomes hard to check long before that. For more than three or four bands, use LOOKUP with a band table, or IFS in Excel 2019 and later.

Is the IF comparison case-sensitive? No. "YES"="Yes" is TRUE. Use EXACT(text1,text2) inside the IF when upper and lower case must count as different.

Does the test workbook work in Google Sheets? It opens and calculates there, and the fixed formulas all work. Google Sheets compares text with numbers differently from Excel, though, so the "broken" results on sheets 1 and 5 may not reproduce. Causes 1 and 5 describe Excel's behaviour.