Adjusting Percentages in Excel 1

[hmm2]

Example of Problem:

COL-A COL-B
VALUE % SHARE
ROW1 16 21.05% [Formula: ROUND(B1/B4,4)]
ROW2 23 30.26%
ROW3 37 48.68%
ROW4 76 99.99% [SUM OF ROWS 1-3]

Question:
Is there any way of automatically adjusting percentages so that the total equals 100.00%? The Big-Wigs here refuse to accept foot-noted reports stating "Slight percent variances may occur due to rounding." They want the numbers that appear in the report to total 100.00%, and the only way I know to fix it is by manually determining which percentage is closest to the "rounding point" and adjusting it accordingly.

Using OfficeXP -hmm
-Using AccessXP

 

[onedtent]

try setting ur rounding to 5 digits and format ur cells to 2 digits. it worked in this case

 

[hmm2]

yes, your method of rounding to 5 digits causes the displayed value in the last cell to equal 100.00%, BUT when you manually add up the three percentages (21.05 + 30.26 + 48.68), the total is still 99.99%. (Excel sums up the actual values in each cell regardless of the 2-decimal display format.) I'm looking for a fix that will adjust one of those 3 percentages up by .01. -hmm
-Using AccessXP

 

[onedtent]

sorry, forgot to mention i used
=roundup(b1/b4,5)

 

[iGrant]

Keep the formula that you have in column B. Then in column C, against each row, copy the following formula:

=IF(B1=MAX($B$1:$B$3),B1+100%-$B$4,B1)

It will add the difference between your summed total (of 99.99 or whatever) and 100% to the largest percentage in the list, and thus not throw the results too much.

In row C4, sum the contents of C1 to C3.

Finally, hide column B.

I have to say that it's a bit dim of your higher ups not to realise that percentages to 2 decimal places may well not add up to 100%! But you probably know that already...

 

[hmm2]

Thanks a million, iGrant! Your tip will save me loads of time pacifying the 'powers that be'. I really appreciate your help. -hmm
-Using AccessXP

 

[iGrant]

Thanks for the star - appreciated.

Just had a thought, though - if you have two percentages in your list that are both the same, and the largest, then each will have 0.1% (or whatever difference) added to them and hence throw out the maths.

Is this eventuality likely?

 

[hmm2]

That's a possibility, but not very likely for the figures I work with. If/when that ever happens, it will be easy enough to spot, and I can adjust it manually.

Thanks again for your help. -hmm
-Using AccessXP

 

[LeKirk]

Well I know this was a while ago but I've only just seen it. I assume that the 76 comes from summing the rows above. I used the following formula in cell B1.
=A1/$A$4 which gave 21.05% just by formatting the cell as percentage to 2 decimal places. I then copied it down from B1 to B4. This automatically gives 100.00% BECAUSE you are dividing A4/A4 or 76/76 which will always be 100% exactly.

 

[hmm2]

I understand what you mean, LeKirk. Unfortunately, the 'powers that be' will not accept anything other than the final printed percentages actually adding up to 100.00% on the bottom line. -hmm
-Using AccessXP