Solved

Round using Excel

Posted on 2011-02-15
6
1,081 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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
2k Power n formula 2 41
calendar source - options.. 10 62
Terminology ..percentile etc 4 91
Math equations 13 74
Introduction On a scale of 1 to 10, how would you rate our Product? Many of us have answered that question time and time again. But only a few of us have had the pleasure of receiving a stack of the filled out surveys and being asked to do somethi…
Complex Numbers are funny things.  Many people have a basic understanding of them, some a more advanced.  The confusion usually arises when that pesky i (or j for Electrical Engineers) appears and understanding the meaning of a square root of a nega…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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