How Do YOU Get Rid of the #NUM’s in “Divide by Zero” Cases?
If You Use IF(), It’s Time to Change Techniques
The Old Way: IF()
Since 2009, here’s how I have been handling this in my PowerPivot measures:
[Growth vs Last Year] =
IF([Units Sold Last Year]=0,
([Units Sold]-[Units Sold Last Year])/[Units Sold Last Year]
In English: I check to see if [Units Sold Last Year], which is the denominator in my desired ratio, is equal to 0. If so, I return BLANK(). Otherwise I just calculate my desired ratio.
And hey, that works:
Look Mom! No #NUM’s!
But Hiding in Plain Sight…
I only discovered DIVIDE() because a friend found it and told me about it! I’m sure I’ve seen the DIVIDE() function in the list of functions many, many times now. But who stops to look at a function named DIVIDE()??? I figured it was just a clumsy alternative to the “/” operator. If there was a function named ADD(), would you ever bother to inspect it? Yeah me neither.
Well it turns out that really, DIVIDE() should be called SAFEDIVIDE()! The description:
“Safe Divide function with ability to handle divide by zero case.”
So let’s rewrite my original measure using DIVIDE():
=DIVIDE([Units Sold]-[Units Sold Last Year],
[Units Sold Last Year]
This gives me:
By Default, DIVIDE() Returns BLANK() in Divide by Zero Cases
Nice huh? A much shorter formula and it gives me the same results as the awkward IF().
And there’s an optional third parameter to DIVIDE(), so if you want to return, say, 42 (the answer to everything) instead of BLANK(), you can. Or maybe you want to return a completely different measure. Knock yourself out.
But DIVIDE() is Also FASTER!
DIVIDE() isn’t just faster to write than the IF(). It’s faster to run as well.
With the IF() approach, the formula engine typically ends up doing TWO pieces of work – it evaluate the IF() test, then if it doesn’t get zero, it evaluates the ratio.
So this means we’re making the formula engine work pretty hard sometimes, especially when the measure or measures in the IF test are sophisticated formulas in their own right.
But with DIVIDE, the engine only has to do ONE unit of work. It just evaluates the ratio. And if it gets a divide by zero error back, it returns your alternate result. It takes no effort (or time) to return BLANK(), so that doesn’t count as work.
(Actually I suspect that the IF approach does three units of work – it evaluate the denominator twice, since the denominator appears on both “sides” of the IF. And it evaluates the numerator once. DIVIDE evaluates the numerator and denominator once each).
So, pivots with DIVIDE() measures will run faster in response to a slicer click (for instance) than ones written with the IF() approach.
You may not notice it until your formulas become more complex and your data sets become larger. But eventually, you may find cases where DIVIDE() is noticeably faster. Might as well get into the habit now.
Why Not IFERROR()?
I know a few of you are gearing up to leave a comment and point out that we’ve always had IFERROR, and that this whole DIVIDE() thing is pure window dressing.
Maybe, but I still prefer how short and compact my formulas are going to be with DIVIDE(), even relative to IFERROR(), since I don’t need to explicitly specify the BLANK().
And there may be other errors as well – errors that are NOT divide by zero – and we don’t want IFERROR() masking those.