How to fix the #NUM! error in Excel
#NUM! means a calculation produced a number Excel can’t handle, or a function got an argument outside its valid range.
#NUM!
Why it happens
Impossible math
=SQRT(-1): there’s no real square root of a negative number.
Functions that can’t find an answer
IRR and RATE give up after many tries if the inputs don’t make sense.
Numbers too large or small
Excel’s limit is roughly 1E+308.
How to fix it
Use ABS when the sign doesn’t matter
=ABS(-12) → 12
Guard the input first
=IF(D2<0,"",SQRT(D2))
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Tip: For IRR or RATE, add a sensible guess argument (like 0.1) to help Excel converge.
Other Excel errors
- #N/A“Not available”: a lookup couldn’t find what you asked for.
- #VALUE!A formula got the wrong kind of data, usually text where it needed a number.
- #REF!The formula points at a cell that no longer exists.
- #DIV/0!A formula tried to divide by zero or by an empty cell.
- #NAME?Excel doesn’t recognise a word in the formula.
- #SPILL!A formula that returns several results has no room to spill into.