#DIV/0! error in Excel: what it means and how to fix it
#DIV/0! means a formula divided by zero or by an empty cell. Why it happens, averages of nothing included, and how to show a clean result, with a practice sheet.
IF function
IF checks a condition and returns one value when it's true and another when it's false: =IF(B2>=50, "Pass", "Fail").
Available in every version of Excel, Excel for the web and Google SheetsOn this page
IF syntax
=IF(logical_test, value_if_true, [value_if_false])| Argument | Required | What it means |
|---|---|---|
logical_test | Yes | Something that is TRUE or FALSE, such as B2>=50 or A2="Paid". |
value_if_true | Yes | What to return when the test is TRUE. |
value_if_false | No | What to return when it's FALSE. Leave it out and you get FALSE. |
Examples
Pass or fail
A score of 50 or more passes. Text results go in double quotes.
| Row | A | B | C |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ana | 72 | Pass |
| 3 | Ben | 48 |
=IF(B2>=50,"Pass","Fail") → PassTwo conditions with AND
A bonus only when sales are over 10,000 and the region's target was hit. AND is TRUE only when every test inside it is.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Rep | Sales | Target hit | Bonus |
| 2 | Lena | 12,400 | Yes | 620 |
| 3 | Marc | 9,800 | Yes |
=IF(AND(B2>10000,C2="Yes"),B2*5%,0) → 620Three outcomes with a nested IF
The second IF only runs when the first test is FALSE. For more than three outcomes, IFS or a lookup table is easier to read.
| Row | A | B | C |
|---|---|---|---|
| 1 | Order | Amount | Shipping |
| 2 | #1001 | $35.00 | Standard |
=IF(B2>=100,"Free",IF(B2>=30,"Standard","Paid")) → StandardPractise it
New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.
Your task: In C2, show "Reorder" when stock in B2 is below 20, otherwise "OK".
Graded live| Row | A | B | C |
|---|---|---|---|
| 1 | Item | Stock | Status |
| 2 | Paper | 12 |
Write the formula in the bar above, then press Check or Enter.
Hint
Test B2<20. The value if true is "Reorder", the value if false is "OK", both in double quotes.
Solution
=IF(B2<20,"Reorder","OK") Any formula that returns the same result passes.
Common mistakes
- Text without quotes. IF(B2>=50,Pass,Fail) returns #NAME?, because Excel reads Pass as a name. Write "Pass".
- Numbers in quotes. IF(B2>=50,"1","0") returns text, and SUM ignores text. Write 1 and 0 without quotes.
- Writing 10 < B2 < 20. Excel can't chain comparisons. Use AND(B2>10,B2<20).
- Deeply nested IFs. Past three or four levels they're hard to check. Use IFS, or put the bands in a table and look them up with XLOOKUP.
FAQ
How many IFs can I nest?
Excel allows 64, but past three or four the formula is hard to read and easy to get wrong. Use IFS or a lookup table instead.
How do I test for a blank cell?
Use =IF(A2="","Empty","Filled") or =IF(ISBLANK(A2),...). The first also treats a formula that returns "" as blank.
Can IF check if a cell contains some text?
Combine it with SEARCH: =IF(ISNUMBER(SEARCH("refund",A2)),"Yes","No") finds "refund" anywhere in A2, ignoring case.
Guides that use IF
Worked problems with practice sheets.
#NAME? error in Excel: what it means and how to fix it
#NAME? means Excel doesn't recognise a word in your formula: a misspelt function, text without quotes, a missing colon. Find it and fix it, with a practice sheet.
Know IF? Prove it on a real sheet.
Practise IF and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install