#NUM! Error in Excel

A #NUM! error in Excel means your formula asked for a calculation that has no valid numeric answer. The formula is typed correctly, but a number going into it is impossible for that function: a start date after an end date, a "6th largest" value in a list of five, or the square root of a negative number. This guide covers the common causes and the fix for each.

What does #NUM! mean in Excel?#

#NUM! is Excel's "this number doesn't work here" error. The function understands what you asked; it just can't produce a valid number from the inputs.

That separates it from similar errors:

Because the cause is an input, the fix usually means correcting the data or guarding against it, not retyping the function.

Cause 1: DATEDIF with the dates the wrong way round#

DATEDIF counts the whole years, months or days between two dates. Its arguments are =DATEDIF(start_date, end_date, unit), and the start date must come first. If it's later than the end date, DATEDIF returns #NUM!.

The broken formula:

=DATEDIF(C2,B2,"Y")

C2 (2024) is later than B2 (2019), so the order is wrong. Swap them:

=DATEDIF(B2,C2,"Y")

The result is 5: Ines has worked five full years. The mix-up is easy to make because the similar DAYS function takes the dates the other way round: =DAYS(end_date, start_date). Our guide to days between two dates compares the two, and the DATEDIF function guide lists every unit.

If some rows might genuinely have the dates reversed, you can make the formula order-proof:

=DATEDIF(MIN(B2,C2),MAX(B2,C2),"Y")

But in HR data, a review before the hire date usually means a typing mistake, so it's often better to let the error show and fix the date. Tenure and age calculations like these come up constantly in Excel skills for HR and recruiting jobs.

A related point: DATEDIF doesn't appear in Formula AutoComplete or the Insert Function list in Excel, even though it works. Type it in full.

Cause 2: LARGE or SMALL asks for too many values#

=LARGE(range, k) returns the k-th largest number. If k is larger than the number of numbers in the range, or less than 1, there's nothing to return, so you get #NUM!.

=LARGE(B2:B6,5) returns #NUM!: the range has five cells but only four numbers, because LARGE skips empty cells and text. =LARGE(B2:B6,4) works and returns 33,700.

When a top-N list must cope with short lists, guard it with COUNT:

=IF(COUNT(B2:B6)>=5,LARGE(B2:B6,5),"")

SMALL behaves the same way. See our guide to finding the top 3 with LARGE and SMALL for ranking patterns.

Cause 3: impossible math#

Some calculations simply have no real-number answer:

• Square root of a negative number: =SQRT(-16) returns #NUM!. If you only need the size of the number, use =SQRT(ABS(A2)). If a negative input means bad data, fix the data.
• A result that's too big: Excel handles numbers up to about 1.8E+308. =10^309 or a growth formula compounding over thousands of periods returns #NUM!.
• Negative or zero logarithms: =LN(0) and =LOG(-5) return #NUM!.

Cause 4: IRR, RATE and other functions that search for an answer#

Finance functions like IRR and RATE don't calculate directly; they try values until they find one that fits. If they can't find a fit in their attempts, they return #NUM!.

Common reasons are cash flows that are all positive or all negative (there must be at least one of each for IRR), or a starting point far from the real answer. Check the signs of your cash flows first, then add a guess argument close to the answer you expect, such as =IRR(B2:B8,10%).

Find every #NUM! error in a sheet#

In a large sheet, let Excel collect the errors for you. Press Ctrl+F (⌘F on a Mac), type #NUM!, click Options, set Look in to Values and click Find All. Each cell in the list currently shows the error, and clicking one selects it.

Often the errors share one cause. In a tenure column, for example, every #NUM! row may be a person whose review date hasn't been entered yet or was typed in the wrong year. Sorting the column so the errors sit together makes the pattern easy to spot. Then decide whether to fix the data or guard the formula.

How to fix #NUM! errors: a checklist#

1. Click the cell and read the formula. Identify which function produced the error.
2. Check each input. Are dates in the right order? Is the k value within the count of numbers? Is any number negative where it shouldn't be?
3. Use Evaluate Formula (click the warning icon, then Show Calculation Steps) for long formulas, to see which part fails.
4. Fix the data if it's wrong; guard the formula if the situation is legitimate.
5. Use IFERROR only for expected cases, such as =IFERROR(DATEDIF(B2,C2,"Y"),"") while review dates are still being entered.

Two other problems sometimes show up next to #NUM! in date work: a column too narrow for its dates, which displays ##### (see why Excel shows ##### in a cell), and formulas that refer back to their own cell (see circular reference warnings).

Practise it#

Fix the DATEDIF formula in the exercise on this page. Then practise days between dates with DAYS and DATEDIF in a live spreadsheet, and the LARGE and SMALL lesson to find top and bottom values without errors.