**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,

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:

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

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!

LOVE THIS!

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

THANK YOU!

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

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

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

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

DIVIDE is fabulous! Thanks!

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.

love it! I will use it today! thank you

Thanks – good to know!

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

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.

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

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)

You can update to SQL Server 2012 SP1 that will contain the divide function in VS 2010. You can also download VS for BI projects for VS 2012 here http://www.microsoft.com/en-us/download/details.aspx?id=36843

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.

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 🙂

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