Percentage Discrepencies

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:

Please Login or Register  to view this content.

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?

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

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.

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

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

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.

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

It seems very arbitary just adding the extra point onto the maximum value!

Try setting up my previous example but with the following in column D:
{=C1+IF(AND(B1-C1=MAX(B$1:B$5-C$1:C$5),C$6<100),1,0)}
: :
{=C5+IF(AND(B5-C5=MAX(B$1:B$5-C$1:C$5),C$6<100),1,0)}
=SUM(D1:D5)

Note that these are ARRAY formulas so use CTRL SHIFT ENTER when entering them.

This will add the odd 1 onto the value that is rounded down the farthest (in this case 25 becomes 26).

Mark.

I remain somewhat concerned about this arbitary adjustment of percentages to force the sum to be 100%
If you must arbitarialy adjust your percentages then I firmly recommend you make the minimum adjustment possible,
i.e. adjust the value that has been rounded furthest (in the appropriate direction).
This is best done by separating out the problem over several columns (these can be "hidden" later if required).
Thus if column A gives the numbers; Column B is the unrounded percentage; Column C is the rounded percentage; Column D is Column B-Column C;
C6 is the sum of the rounded percentages; then Column E becomes:
=IF(AND(C$6<100,D1=MAX(D$1:D$5)),C1+1,IF(AND(C$6>100,D1=MIN(D$1:D$5)),C1-1,C1))
Note this does not use an array formula (thanks to the addition of an extra column) and works for sums of 99 or 101.
Mark.

Well, I am used to adjusting the biggest percentage but I am duing it manually. The reasoning behind it is the biggest percentage gets largest amount of money so 1% up or down makes very little to no impact. Whereas 1% on a 4% user makes a big difference. In addition, I am always adjusting the big guy so over time he gets all positive and negative adjustments.

I am not good with arrays so I could not get your formula to work. It just displays the formula instead of result. But you did get me into thinking and this is the formula that I came up with to put in D1 and paste down. It seems to work in either direction.

=IF(C$6=100,C1,C1+IF((C1-MAX(C$1:C$5))=0,(100-C$6),0))

I thank you for your time and could not have done it without you.
Thanks again.