Solved

Round using Excel

Posted on 2011-02-15
1,062 Views
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?

0
Question by:koossa

LVL 10

Accepted Solution

John Claes earned 500 total points
ID: 34895877
I'm very sorry,  but There are some smaller issues here !

When i sum the first numbers: I get 1.000000001

When I sum the Second numbers I Get  =  100.01

But that's not the point.
And Easy way is to do is

=ROUND(D4*100,0) ==> this way all Digits are removed ==> 1.000000001
becomes 100

=ROUND(D4,0) ==> this way all Digits are removed ==> 100.01 becomes 100

0

LVL 18

Expert Comment

ID: 34895903
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.

0

LVL 10

Expert Comment

ID: 34895905
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).
0

LVL 6

Expert Comment

ID: 34895934
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>
0

LVL 18

Expert Comment

ID: 34895944
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

0

Author Closing Comment

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

1.000000000000000000000000000000
0

Featured Post

A Guide to the PMT, FV, IPMT and PPMT Functions In MS Excel we have the PMT, FV, IPMT and PPMT functions, which do a fantastic job for interest rate calculations.  But what if you don't have Excel ? This article is for programmers looking to re…
This is a research brief on the potential colonization of humans on Mars.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…