skip to Main Content

#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,
   ([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.


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.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 20 Comments
  1. 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!

    1. 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

  2. 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.

  3. 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.

    1. Spoke too soon 🙁
      I am using Visual Studio 2010 and that does not seem to support Divide.
      Visual Studio 2012 – last I checked, did not support Tabular Model projects. So it seems I am stuck for a while (will check if Visual Studio 2013 Preview supports tabular model development)

  4. 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.

  5. 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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *