#NUM! error in Excel
A formula asked for a number that cannot be calculated, or an argument is outside the range a function accepts.
What #NUM! means
#NUM! is the error for numbers that are valid in form but impossible in practice. The input is a real number, but the function cannot produce a real answer from it, or the number you supplied falls outside what the function allows.
It is rarer than #N/A or #VALUE!, and usually tied to a specific function: SQRT and LN with negative input, LARGE and SMALL with an oversized rank, DATEDIF with dates in the wrong order, or financial functions that fail to converge on an answer.
Because the cause is a number and not a type, look at the values going in. A quick way is to point a spare cell at each input and see what it holds.
Causes of #NUM! and how to fix each
1. A math operation with no real answer
Some calculations are undefined for the numbers given. The square root of a negative number is the classic case, and LN or LOG of zero or a negative number behaves the same way.
| A | B | |
|---|---|---|
| 1 | -9 | #NUM! |
Fix. Find out why the input is negative. If it is a legitimate result that should be treated as positive, use ABS: SQRT(ABS(A1)). If a negative value means bad data, trace it back to the source instead of masking it.
| A | B | |
|---|---|---|
| 1 | -9 | 3 |
ABS turns -9 into 9, and the square root is 3.
2. Asking for a position beyond the data
LARGE and SMALL return the kth biggest or smallest value. Ask for k larger than the number of values and no such value exists.
| A | B | |
|---|---|---|
| 1 | 40 | #NUM! |
| 2 | 10 | |
| 3 | 25 |
Fix. Keep k within the count of numbers: k must be between 1 and COUNT of the range. If the number of rows varies, set k with MIN(5,COUNT(A1:A3)).
3. A start date later than the end date
DATEDIF measures from an earlier date to a later one. Reverse them and Excel has no positive interval to report.
| A | B | C | |
|---|---|---|---|
| 1 | 5/1/2024 | 1/1/2024 | #NUM! |
Fix. Put the earlier date first. If the order is unknown, order them with MIN and MAX: DATEDIF(MIN(A1:B1),MAX(A1:B1),"d").
| A | B | C | |
|---|---|---|---|
| 1 | 5/1/2024 | 1/1/2024 | 121 |
From January 1 to May 1, 2024 there are 121 days.
4. A logarithm of zero or a negative number
LN and LOG are only defined for positive numbers. A zero in the input, perhaps from a growth calculation that starts at 0, has no logarithm.
| A | B | |
|---|---|---|
| 1 | 0 | #NUM! |
Fix. Make sure the input is above zero. If zeros are expected, test first with IF(A1>0,LN(A1),"") so those rows stay blank.
Trapping #NUM! in a formula
Trap #NUM! only when you can say what a no-answer means, for example "no 5th largest value". Otherwise let it show, because it is telling you that an input is impossible.
A numbers-too-large condition produces the same error in real Excel: results beyond roughly 1E+308 return #NUM!, which is useful to know when you see it on a compounding formula that has run away.
| A | B | |
|---|---|---|
| 1 | 40 | fewer than 5 |
| 2 | 10 | |
| 3 | 25 |
When the rank does not exist the formula returns a short message in place of the error.
#NUM! on Excel job tests
#NUM! can appear in date and finance work: DATEDIF with start and end reversed, or a rate or term of zero where a function needs a positive number.
It also appears when a Top-N list is built with LARGE and the data has fewer rows than N. A good answer either limits N to the data size or labels the shortfall.
Related guides
#NUM! questions
- What does #NUM! mean in Excel?
- A formula produced an invalid number, or one of its numeric arguments is outside the range the function accepts.
- Why does DATEDIF return #NUM!?
- The start date is after the end date. DATEDIF only counts forward, so swap the arguments or order them with MIN and MAX.
- What is the difference between #NUM! and #VALUE!?
#VALUE!means the wrong type of input, such as text.#NUM!means the input is a number but the result is impossible or the argument is out of range.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample