When a ZERO is not a ZERO !!

Hello everyone!

This is a rare scenario that I am going to write in this blog and this is utterly surprising if you encounter this in your life for the first time! I can tell you my experience: in my 12 years of career in Data & Analytics, I have encountered this only once.

To start with, let me ask you a question: Have you ever aggregated multiple decimal values and use the summation as the denominator in another division calculation? Of course you did this. But how did you handle division by zero?

For example, let’s consider following data in my source:

Now, suppose, I want to show Sales Value Mix % by each product. The formula I used to write in WebI is like:

=[Sales]/Sum([Sales] In Block)

Now, the above logic should give me #DIV/0 error with the above mentioned dataset – correct ? To handle this division by zero, I used to write the formula like:

=If(Sum([Sales] In Block) <> 0 ; [Sales]/Sum([Sales] In Block))

Looks good – right ?

Now, let’s see what I get after this:

Surprised ?? So what did I do wrong ?

The answer is: although the sum of sales values above should be zero, it is not exactly a zero. This is a very small number but not a zero. This behavior is same in MS Excel as well.

The reason is most of the softwares we work with, use IEEE 754 standard to represent decimal numbers for calculation and often there is no precise binary representation of a decimal number. Here is a KBA from Microsoft that I found: http://support.microsoft.com/kb/78113.

Then what should I do?

I use to round the decimal places of an aggregated decimal number before checking equality with zero. Hence, I use following formula instead of the previous one:

=If(Round(Sum([Sales] In Block); 8) <> 0;[Sales] / Sum([Sales] In Block))

This gives me the expected result:

I hope you like the post. Please comment and share how you check equality with zero in your case.

Thank you !!