Solved

Round using Excel

Posted on 2011-02-15
6
1,064 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Okay. So what exactly is the problem here? How often have we come across situations where we need to know if two strings are 'similar' but not necessarily the same? I have, plenty of times. Until recently, I thought any functionality like that wo…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now