I’ve seen many IIF’s, CASE-statements, +0.00001 additions and others to suppress or solve one of these ‘errors’:
NaN, Infinity or even #Error
Let’s start with the basics, when do these things emerge and why?
Normally, you get NaN when trying to divide 0 / 0 or Infinity when you are dividing any number by 0.
I personally use this function to overcome such obstacles:
Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double
If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
Return dividend / divisor
And here’s how you should use it:
Other more creative solutions are:
=IIF(Y = 0, 0, X/Y)
X/Y = "NaN",Nothing,
X/Y = "Infinity",Nothing,
X/Y = "Infinity",Nothing