Analytics: Rounding Off Problem

If you are into analytics then you must have witnessed this many times in your life that sum of all those numbers that you rounded off for each category is not equal to 100% when you see the grand total. To some beginners, it sounds like a problem but once you understand it then you start taking it as a natural behavior. Let us see this by an example –

Suppose you have following numbers that you may want to present as percentage –

CategoryActual calculation with no rounding offRounding off to 4 decimal placesRounding off to 2 decimal placesRounding off to zero decimal places

First column contains actual result of a calculation with NO rounding off and if you sum it, the total is exactly one or if you want to present it as percentage, you can say it’s 100%.

Next, let’s say, you multiply this by 100 and then round it off to 4 decimal digits as shown in the second column and the total is still 100 (percent).

Further, if you round it off to 2 decimal digits in above scenario and sum it, then grand total becomes 101 (percent) which may sound like an incorrect behavior.

Lastly, if you round if off to 0 decimal places and keep the whole part or say without multiplying the number by 100, simply round it off to 2 decimal place and then present it in percentage format, it still shows 1 or say 100%

In all these scenarios. one thing is certain that it’s difficult to predict for all the calculations that you might be doing and identify when it would become 100% or 101%.

As a solution, you need to understand one mathematical principle that sum of rounded elements is not necessarily equal to the rounded sum of elements….and this is not an incorrect result or behavior but truly an impact of mathematical calculation. However, from reporting standpoint, you may need to deal with it so that it doesn’t look incorrect to the end users.

There can be different solutions for different scenarios to manage this like handle the least fraction as part of some manipulation by subtracting it either from the largest number contributing the largest proportion to 100 for which 0.001, 0.0001, 0.00001 etc. doesn’t matter much or subtracting it from the smallest number.

There are NO blanket recommendations…it all depends on the scenario and immune of business needs in terms of how exactly they want to handle this and accordingly this can be programmed into the calculations. Sometimes, this can be handled by the visuals too.

Leave a Reply