Rounding With Percentages Headache

[cameronb]

This is a really puzzling problem. Regardless of whether you are use to using VBA, Formula or generally adept using Excel, this difficulty in excel is a challenge.

Suppose in Column A, you have 100 cells with purely numeric data in each cell. At the bottom in A101, you have a total. Let us suppose they are large integers, and you wish to no know the percentage a1 is of the total, there are various methods of finding a solution.

If this percentage is displayed to two decimal places, the total of percentages should equal 100. If you wish this information displayed exactly to 2 decimal places there are equally various methods of formatting the data. If you then copy the percentages and past special and re total the percentages, they rarely return to 100, due to roundings.


I have tried many rounding formulas, that will give 2 decimal places and sum neatly to 100, there is normally manual changes required.

I have finally reach this formula to minimise the rounding errors:
Where the total is A620 and the percentage of cell A1 is calculated using the following:

MROUND(A1,($A$620*0.01/100)))/$A$620*100


The above is trying to round to multiples of 0.01% of the total (cell A620).

I have tried many many different formulas, but with a large amount of data there does not seem to be a solution.


Can anyone help?

 

[Scott Huish]

I have to assume that you are taking the percentage of A1 compared to the total, then A2 compared to the total, then A3 compared to the total, etc. and then adding up these percentages, because only A1 compared to the total would never equal 100% unless the rest of the values summed up to 0.

Not sure what you are using for your formula, but try this:

In B1:
=A1/SUM(A:A)

Format as percentage with 2 decimal places.

Copy down.

At the bottom:
=SUM(B1:B600)

came to 100.00% percent for me.