I was goofing around in PowerPivot when I decided to divide a couple of columns. When reviewing the results I noticed that for some of my rows, PowerPivot gave me a strange value “Infinity”. This is because for these rows, I’m dividing a number by zero.

.

Apparently in PowerPivot, when you divide a number by zero, you get the value **Infinity**. Infinity means the resulting answer is too large to represent as a conventional floating-point value. This is tantamount to Excel’s **#DIV/0!** error.

If you somehow divide 0 by 0, PowerPivot will give you the value **NaN **(not a number) – meaning the answer can’t be mathematically defined.

.

This got me thinking.

We all take for granted that we’ll get an error when we divide by zero; any Excel analysts on the job more than a week knows this. But have you ever stopped to think about the mathematical reason why you can never divide by zero? While the reason is vaguely intuitive, it’s fun to break it down.

.

One way to think about it is to consider what happens when you divide a number by another.

Division is really nothing more than fancy subtraction.

For example, 10 divided by 2 is the same as starting with 10 and continuously subtracting 2 as many times as needed to get to zero. In this case, you would need to continuously subtract 2 five times. So 10/2 = 5.

Now if I tried to do this with 10 divided by 0, I would never get anywhere, because 10-0 is 10 all day long. I’d be sitting here subtracting 0 until my cheap East German calculator and I die (also known as infinity).

10-0 = 10

10-0 = 10

10-0 = 10

10-0 = 10

…… Infinity

.

But wait…it gets more complicated.

.

Consider the smallest non-zero number you can divide a number by.

Well, if I divide 1 by .01, I get 100.

If I divide 1 by .001, I get 1,000.

In fact, the smaller I make my denominator, the bigger my result will be.

No matter whatever candidate smallest number you think of, there is a number smaller than that. AND the closer you get to zero, the bigger the result….until Infinity.

.

Now here’s the wonky bit.

When you start from a negative denominator, the closer you get to zero, the smaller the result until -Infinity.

So a number divided by a very small denominator (close to zero) has the potential of being either close to *Infinity* or *Negative Infinity* (depending on whether it’s postive or negative). This is of course irrational. For this reason, mathematicians consider any number divided by zero to be **Undefined **(aka…we don’t know what the hell to do with this).

.

This is the reason why, Excel, PowerPivot, Access, SQL Server, and every other computational system freaks out when you try to divide by zero.

.

Luckily, there are ways to get around divide by zero. In standard Excel, you can write a simple if statement to check for a zero denominator:

**IF(B1=0,0,A1/B1)
**

.

In PowerPivot, we get a nifty DAX function called DIVIDE. The DIVIDE function essentially handles the divide by error for you:

**DIVIDE([Numerator],[Denominator],0)
**

.

Check out Rob Collie’s post to learn more about the DIVIDE function.

.

So now you know.

I’m not buying into the infinity argument. Rather than relate division to subtraction, try relating division to multiplication.

If I divide one number by another, I can multiply the result by the divisor to find the dividend. As in: 51 / 3 = 17 -> 17 x 3 = 51.

Now when talking about dividing by 0, that’s where this mathematical proof falls apart.

51 / 0 = ?? There is no number that you can multiply by 0 to get 51. Even infinity – multiply infinity x 0, what is your answer? If we are to believe ANY number multiplied by 0 returns 0, then that should include infinity, right?

Great read! I never thought of dividing as just simply counting subtractions. And as a side not, if I were single, I’d be using that pick up line at the end of your post ALL DAY LONG!

I had never thought of this either.

And Chris, if you were single and used that line ALL DAY LONG, I’d be able to predict your future.

You’d still be single.

Cool.

ok nice read but of course the “mathematical” reason is more like this:

you want the operation of division to be well defined and if you include 0 you cannot (as forall a: 0*a=a*0=0 and in a field 0 should not be equal to 1 – after all if 0=1 in a field F you would have F={0}, not that interesting)

lol and after seeing this there is this great blog post explaining the basics (and a bit more) about fields: http://jeremykun.com/2014/02/26/finite-fields-a-primer/

@OmarF Haha, luckily I don’t have to worry about that 🙂

In binary arithmetic it can be a bit shift. Rings, Fields and Ideals are Group Theory topics and interesting ones at that. Topological reasons and metrics provide other insights.

wow…

very cool read indeed. I love these posts that help you look behind the curtain instead of accepting that “it is like it is” (although you still have to…)

the image at the end of the post is great as well, but you know what kind of women you attract with statements like that, right? 🙂

My favorite algebra proof confirms that you cannot divide by 0…

1) a=b

2) ab=b^2

3) ab – a^2 = b^2 – a^2

4) a(b-a) = (b+a)(b-a)

5) a=b+a

6) a=a+a

7) a=2a

8) 1=2

The algebra is correct, but when you from line 4 to 5 you are dividing by 0. My 7th grade teacher would be so impressed.

Sar,

Let me give you a logic. Any number divided by itself is 1.

So if you consider zero as a number, then 0/0 should be 1.

This was told by the world famous mathematician Madam Siva Brinda Devi.

Have a good day

The subtraction thought was a fun read. In all reality, dividing simply means “to split”. The function is to show how many “splits” happen, not what is left.

For example, I’m standing here holding an apple. If I decide not to share it and eat it myself, the apple is still with me and me alone. 1/1 = 1

If I decide 2 co-workers can have some, I cut it with a knife and there is 3 splits. 1/3 = .33

If I’m holding the apple and say that I am going to split it 0 times, it can’t be done because no splitting is occurring. Yes…I still have the Apple but that’s not the answer I’m looking for. I want to know the number of splits. Well, if you’re splitting with nothing, you get no splits.

Many (and I really mean many!) years ago I had a summer job working for a state public utilities commission. Remember, this was before widespread use of electronic calculators.

A summer employee colleague and I, in a bout of boredom, questioned what would happen if we tried to divide by zero on an electrical mechanical calculator. I don’t remember what numerator we selected. w\We hit the equal key and the calculator started cranking and cranking and… When the device started smoking, we un plugged it. The gears appeared to weld themselves together because when we plugged it back in, nothing would move.

We confessed our “sin” to our supervisor. He said not to worry about it.

It can be said in 6 words, “Number Divided by Zero is Infinity”, but you put it in a neat, clear and awesome way!!!

I really liked the presentation. I need to take the whole life to achieve this. If I am not wrong. 😉