DIVIDE() and Conquer

 
#NUM Divide by Zero Errors in PowerPivot:  A better way to get rid of them.

How Do YOU Get Rid of the #NUM’s in “Divide by Zero” Cases?
If You Use IF(), It’s Time to Change Techniques Smile

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,
  
BLANK(),
   ([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:

Returning BLANK() values when the denominator is zero.

Look Mom!  No #NUM’s!


But Hiding in Plain Sight…

The DIVIDE function.  PowerPivot has been hiding it from me.

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:

DIVIDE() returns BLANK() by default for all divide by zero cases.

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.

image

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.

20 Responses to DIVIDE() and Conquer

  1. Geoff McNeely says:

    Hiding in plain sight. I love how we can be completely competent with a tool and then humbled by something new in a heartbeat. I’ll be putting this to use as well. Thanks for sharing!

  2. Patricia Becker says:

    LOVE THIS!

  3. Richard says:

    MMMMM…sneaky one that DIVIDE(), real spy material. Nice post.

  4. Henson says:

    THANK YOU!

  5. Charlie says:

    i always say dividing something by nothing should leave you something. Nice post, very effective too.

  6. I may be wrong, but I only see the DIVIDE function in the PowerPivot version used with Excel 2013.

    • powerpivotpro says:

      This whole post was done with 2010, but I do not think PowerPivot v1 had DIVIDE(). Have you upgraded to v2? I am running PowerPivot version 11.0.3000.0

  7. Yes, I am using v2 for Excel 2010 also & I did not see it listed there.

  8. Mike Girvin says:

    DIVIDE is fabulous! Thanks!

  9. Richard says:

    Indeed, I had a look today and could not find it either, even tried writing out the function but alas it would not work. I’m using v2 for Excel 2010 as well…aha just checked and I’m still on version 11.0.2100.60…bugger. Will upgrade in a bit.

  10. Ann Saputelli says:

    love it! I will use it today! thank you

  11. Jason Price says:

    Thanks – good to know!

  12. Eltjo Verweij says:

    Wow, just updated from Excel 2010 and suddenly noticed divide by zero errors. This saved my day!

  13. Oleg Gvozdenko says:

    I only have v1, so don’t have access to DIVIDE(), but my go-to pattern for an IFer-BLANKer is:

    IF([Units Sold Last Year],
    ([Units Sold]-[Units Sold Last Year])/[Units Sold Last Year]
    )

    I am not sure if this is faster than the [Calculation=0, BLANK(),Formula] pattern you use since I am avoiding an explicit BLANK() evaluation, but I usually just use it to make my formulas shorter.

  14. Avichal says:

    Thanks Rob, DIVIDE is a gem! I’m updating all my formulas today.

  15. T says:

    Wow, welcome to 2003 MIcrosoft. Why did it take 8 years of requests to get this in there? All you need now is auto-caching on calculation results (not query result cache) and auto-handling of mutli-selects, and you might be ok.

  16. bhavikmerchant says:

    Its also interesting to note that this function gives excellent performance. I have tried various methods to achieve this in Excel 2013 – including IFERROR and manual checking of denominator and in my model i get terrible performance when the measure is added to a pivottable. This happens regardless of whether the alternate value is zero or BLANK(). Note i am using a star schema.

    I have a lot of experience with multidimensional SSAS, and i believe (but have not confirmed) that all the other methods force cell by cell evaluation and/or create granularity between dimensions that doesnt actually exist. One for “the Italians” to comment on i think?! I will reach out to them on twitter :)

  17. Adam says:

    Glad I saw this early on in Powerpivot life. What a time saver!

Leave a Comment or Question