#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.
#NUM! error in Excel: what it means and how to fix it
Works in every version of ExcelKey takeaways
- #NUM! means the calculation has no valid number as its answer, with these inputs.
- IRR and RATE show it when they can't find a rate: check the cash flows include both a negative and a positive value.
- LARGE and SMALL show it when you ask for more items than the range holds.
- DATEDIF shows it when the start date is after the end date.
- Impossible maths, such as the square root of a negative number, and results too large for Excel also give #NUM!.
On this page
#NUM! means a formula’s inputs are numbers, but no valid number comes out of them. Sometimes the maths has no answer, such as the square root of a negative number. Sometimes a function searches for an answer and gives up, as IRR can. And sometimes the inputs ask for something that isn’t there, such as the 4th best score out of three. Each case has its own fix, so start by finding which one you have.
1. IRR or RATE can’t find a rate
IRR looks for the rate at which a project’s discounted cash flows add up to zero (its NPV is zero). That needs money going out and money coming in: at least one negative value and one positive. Leave out the year-0 investment and every value is positive, so there’s no rate to find:
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Year | Cash flow | IRR | |
| 2 | 0 | -5,000 | #NUM! | |
| 3 | 1 | 1,500 | ||
| 4 | 2 | 2,000 | ||
| 5 | 3 | 2,500 |
Include the investment, as a negative number:
=IRR(B2:B5) → 8.9%IRR and RATE work by trial: IRR starts from a guess of 10% and gives up with #NUM! if it hasn’t settled on an answer after 20 tries. If the cash flows are right and IRR still shows #NUM!, give it a guess closer to the answer you expect as its second argument: =IRR(B2:B5,-10%) for a project that loses money.
2. More items than the list holds
LARGE returns the k-th largest value and SMALL the k-th smallest. There are three scores, so asking for the 4th best has no answer:
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Player | Score | 4th best | |
| 2 | Ana | 88 | #NUM! | |
| 3 | Ben | 75 | ||
| 4 | Cara | 92 |
Asking for item 0 gives #NUM! too. When the list can be shorter than the position you ask for, check its length first: =IF(COUNT(B2:B4)<4,"",LARGE(B2:B4,4)).
3. Dates the wrong way round
DATEDIF counts the years, months or days between two dates, and it takes the earlier date first. Give it the later date first and it returns #NUM!:
| Row | A | B | C |
|---|---|---|---|
| 1 | Born | Today | Age |
| 2 | 14 Jun 2001 | 11 Nov 2026 | #NUM! |
Swap the two dates:
=DATEDIF(A2,B2,"y") → 254. Maths with no answer
The square root of a negative number has no real answer, and neither has the logarithm of zero or of a negative number, so SQRT, LN and LOG show #NUM! for them:
| Row | A | B |
|---|---|---|
| 1 | Area | Side |
| 2 | -16 | #NUM! |
Don’t reach for ABS to make the number positive: a negative area usually means the input is wrong, and that’s the thing to fix. When a negative is possible, say so instead: =IF(A2<0,"Check the area",SQRT(A2)).
5. A result too large for Excel
Excel can’t hold a number much above 10 to the power of 308. A formula whose result would be larger, such as =10^309 or =FACT(171), shows #NUM!. Check the inputs: a growth rate typed as 5 instead of 5%, compounded over many periods, can pass that limit.
Dates have a limit too. Excel’s last date is 31 December 9999, so DATE returns #NUM! for a year of 10000 or more, and for a year below 0.
Practise it: an IRR that doesn’t show #NUM!
Your task: In D2, work out the IRR of the project's cash flows, from the investment in year 0 (B2) to year 3 (B5).
Graded live| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Year | Cash flow | IRR | |
| 2 | 0 | -8,000 | ||
| 3 | 1 | 2,500 | ||
| 4 | 2 | 3,000 | ||
| 5 | 3 | 3,500 |
Write the formula in the bar above, then press Check or Enter.
Hint
IRR takes the whole range of cash flows, starting with the negative investment: B2:B5.
Solution
=IRR(B2:B5) Any formula that returns the same result passes.
FAQ
What does #NUM! mean in Excel?
A formula's inputs make a valid numeric answer impossible: a square root of a negative number, a rate IRR can't find, a position past the end of a list, or a result too large for Excel.
Why does IRR return #NUM!?
Either the cash flows don't include both a negative and a positive value, or IRR couldn't settle on a rate within its 20 tries. Check the range includes the investment, then try a guess as the second argument, such as =IRR(B2:B5,-10%).
Why does DATEDIF return #NUM!?
The start date is later than the end date. DATEDIF takes the earlier date first: =DATEDIF(A2,B2,"y") with the birth date in A2 and today's date in B2.
How is #NUM! different from #VALUE!?
#VALUE! means an input is the wrong kind, such as text where a number belongs. #NUM! means the inputs are numbers, but no valid number comes out of them.
Keep going
Guides that build on this one.
#VALUE! error in Excel: what it means and how to fix it
#VALUE! means a formula got the wrong kind of value, most often text where it needs a number. The usual causes and fixes, with a practice sheet.
#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.
Reading is step one. Writing the formula is the skill.
Practise in a live grid that grades what your formula calculates, with a specific hint when it's wrong.
Runs in your browser · Graded on your result · No install