Solved

Round using Excel

Posted on 2011-02-15
6
1,074 Views
Last Modified: 2012-05-11
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
Comment
Question by:koossa
6 Comments
 
LVL 10

Accepted Solution

by:
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

by:deighton
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

by:abbright
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 6

Expert Comment

by:akajohn
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

by:deighton
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

by:koossa
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
logic in c# 10 72
Standard Deviation 2 36
Error in calculation 2 79
Linear algebra 3 66
Article by: Nadia
Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
The greatest common divisor (gcd) of two positive integers is their largest common divisor. Let's consider two numbers 12 and 20. The divisors of 12 are 1, 2, 3, 4, 6, 12 The divisors of 20 are 1, 2, 4, 5, 10 20 The highest number among the c…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question