Link to home
Start Free TrialLog in
Avatar of koossa
koossa

asked on

Round using Excel

I've got 4 numbers in MS Excel.


0.010989011
0.21978022
0.32967033
0.43956044

When I sum them, I get 1


To get them to %, I multiply them by 100 and Round them by 2 to display for the user using something like =ROUND(D4*100,2)

1.1
21.98
32.97
43.96


When I sum that I get 100.01


I'm just using excel to demonstrate my problem.  I'm using the same method in one of my applications to display the % in ingredients, but the total is 100.01% and must be 100%.

What can I do to get the correct total?



ASKER CERTIFIED SOLUTION
Avatar of John Claes
John Claes
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if you round the individual values for display, then there is a risk the rounded values will not all add up to 100% - that is not an error, it is just a fact of life.  Sometimes statistical displays print a disclaimer saying that percentages may not add up to 100% due to rounding.  I think that is the best bet here.




In your example you could round the sum as well. But this is not a general solution.
The longer answer: As you round each number you change the sum. This can easily be seen when you look at the following numbers:
0,6 rounded: 1
0,6 rounded: 1
0,3 rounded: 0

The sum is 1.5 but the sum of the rounded numbers is 2. This is bigger than the original sum as you add 2*0.4 to the rounded sum and only once decrease it by 0.3. This adds up to an additional 0.5.
In general it depends on the numbers you are looking at: If you have x numbers which are rounded "up" your sum can be at most x*0.5 bigger than the original sum (if you round to whole integers).
Avatar of Anil
As previous people have said , rounding introduces errors and errors add-up (they rarely cancel each other) so you will have to round  up and down accordingly to make the total 100.

A>
lets say you have 3 salesmen, and they all sell $1,000,000 and you want to give % of sales per salesman

it is 33.3% each when rounded right?

well that can only add up to 99.9% - the missing .1% is in the rounding

lets say that salesmen are A,B & C - so you decide to make C a 'balancing item' and give him 33.4%

well then you r figures read

A 33.3%
B 33.3%
C 33.4%

adds up to 100, but the figures are wrong, C did not do 33.4%, why would he deserve that

sometimes rounded %'s will not add to 100

Avatar of koossa
koossa

ASKER

This must be a bug in MS Excel, because if I make my decimal places more, it does not show the ...1


1.000000000000000000000000000000