Percentage Discrepencies

[Oriana]

I have a problem in Excel where it is drawing data and creating percentages, but the percentages are adding up incorrectly. One set is adding up to 99% and the other set is adding up to 101% (the two should not be crossing "paths," so I think it just a coincidence that one is 101 and one is 99).

The percentages, per request, are being rounded up to have no decimal point values, so I cannot set them to show to the tenth value or anything to make them more precise.
------------------------------------
Here is the data I'm working with:

1         A                 B
2 Total Responses	   165
3 Strongly Disagree - 1	   42
4 Rating 2	           8
5 Rating 3	           25
6 Rating 4	           32
7 Strongly Agree - 5	   58
8
9 Strongly Disagree - 1	   25%
10 Rating 2	           5%
11 Rating 3	           15%
12 Rating 4	           19%
13 Strongly Agree - 5	   35%

Formulas:
B9: =IF(B3=0,"0%",B3/B2)
B10: =IF(B4=0,"0%",B4/B2)
B11: =IF(B5=0,"0%",B5/B2)
B12: =IF(B6=0,"0%",B6/B2)
B13: =IF(B7=0,"0%",B7/B2)

Cells B3-B7 are drawing off of a separate raw data sheet, which I have checked over and over for errors. I can't find any, so those numbers should be correct. Cell B2 simply has the formula: =SUM(B3:B7) in it.

If you'll notice, 25+5+15+19+35 = 99. I have a bar graph that then pulls from the data shown here. Why is Excel only showing 99%, not 100%?

I have another situation exactly like this one, except all of the percentages are adding up to 101%. Any ideas?

[Mark@Work]

This is just an artifice produced by the rounding of the fractional part of the percentage.

if you increase the number of decimal places shown you will see that your percentages are more acurately given as:
25.45%
4.85%
15.15%
19.39%
35.15%
add these up and you get 99.99%, with even more decimal places you will get even closer to 100%.

You will note that 4 of the 5 numbers round down, only one rounds up hence the sum you get is less than 100%.
For your other case you will find that the majority of the numbers round up.

Mark

[Oriana]

I realize this, but as I stated in my post, I cannot show the fractional part of the numbers, only the integral.

Is there a way to make these integers add up to 100% (and visually show numbers that add up to 100%)? The client I'm working with wants to see this if it's possible.

[BigBas]

Try setting up your formulas liek this, and report back if it works:

IF(B3=0,"0%",ROUND(B3/B2,2))

[panter.8]

I have the same issue in my several processes in Accounting and huge amount of spreadsheets doing this. I'll just explain the same thing in different words.

Without ROUND function, it totals to 100% but the line item detail does not add up on calculator because of rounding.

With ROUND function, it does not total 100% which I need. But the items add up on the calculator though.

I need a formula that will look at the bottom total and ask is it 100%. If it is 100% do not do anything but if it's not adjust the biggest detail number up or down to make the total 100%.

This may not be possible but thank you for looking at it.

In the example above it adds up to 99% so the biggest number of 35% needs to be adjusted to 36% in order to make it balance.

Thanks again.

[Mark@Work]

To test this out I set Column A to
42
8
25
32
58
=SUM(A1:A5)
Column B to
=100*A1/A$6
: :
=100*A5/A$6

Column C to
=ROUND(B1,0)
: :
=ROUND(B5,0)
=SUM(C1:C5)

Column D to
=C1+IF(AND(C1=MAX(C$1:C$5),C$6<100),1,0)
: : :
=C5+IF(AND(C5=MAX(C$1:C$5),C$6<100),1,0)
=SUM(D1:D5)

It would be easier if I could get the uploads working...