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?

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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).

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).

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>

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

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

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

1.000000000000000000000000