Rounding Values to 100%

[Amosbroker]

I extract data and pull in to graphs. Quite often, I have 99% or 101% with the data that I am assessing. How do I tell Excel to look at my values and round those that make the most sense so that when added together equal 100%. I spend so much time looking at these partial percentages determining which should be changed in order to equal a whole 100% chart.

Below is a snapshot of the table I use. I have another table that looks at movement and has 8 categories, this one really has issues with equaling 100%. For each formula, I round the percentages to 2 digits. Currently, I have the cell conditional formatted to turn red when it does not equal 100% so I can do the manual leg work to figure out which cell needs to change to total 100%. I would prefer a formula or even VBA do this for me.

[TABLE="width: 493"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Healthy[/TD]
[TD]Moderate[/TD]
[TD]High[/TD]
[TD]Unknown[/TD]
[TD]Assessed[/TD]
[TD]100 Check[/TD]
[/TR]
[TR]
[TD]22%[/TD]
[TD]29%[/TD]
[TD]47%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]56%[/TD]
[TD] [/TD]
[TD]39%[/TD]
[TD]5%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[TD]40%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]66%[/TD]
[TD]26%[/TD]
[TD]8%[/TD]
[TD]1%[/TD]
[TD]454[/TD]
[TD]101%[/TD]
[/TR]
[TR]
[TD]79%[/TD]
[TD]9%[/TD]
[TD]11%[/TD]
[TD]1%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]70%[/TD]
[TD]21%[/TD]
[TD]7%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[TD]40%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]63%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]101%[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hmm. Do you have the data that the original percentages are composed from? You really shouldnt be rounding those as thats whats causing your problem. Other than that make people aware it may not add to 100% because of rounding.

 

[SpillerBD]

Part of the problem originates with the ROUND function in Excel. It is not Banker's Rounding, which is used in Access Rounds function and the VBA round function. This contributes to the issue can be adding the results of rounded values.
If you can calculate the "100 Check" with the un-rounded values, you should be better off.

 

[Amosbroker]

Hi Steve. I do have the data the original percentages are composed from, the table up above actually contains the following formulas:

Formula Under Healthy - all other formulas are formatted the same way, rounded reference to data on another tab
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ROUND(BMI!E3,2)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]
Referenced Formula
COUNTIF(C:C,"healthy")/D1[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

I really don't have the option to make people aware the percentages will not equal 100%. I am expected to sort through the data and provide charts/tables that equal 100% without using partial percentages.

 

[Amosbroker]

Hi Spiller -

When I remove the round function, the 100 Check column does not calculate correctly. The partial percentages do not allow for an accurate 100% check. Is my brain just stuck this morning - is there a possible workaround?

 

[steve the fish]

If you choose to round it then you will get percentages that add up to 99, 100, 101 etc. You could just format to make it look like it was whole numbers in terms of percentage but the underlying number was the correct number. Other than that how would you possibly choose the correct number to change to make your percentage add up to 100%? If you collect data then changing it doesnt really make a great deal of sense.

 

[shg]

Everyone that looks at numbers understands that percentages as shown rarely add to 100%. USA Today, which writes at a sixth-grade level, explains this about random graphics without expecting funny looks from readers.

It's possible to show rounded values that always total 100%, but it comes at the expense of manipulating data, which is, IMO, much more misleading.

 

[SpillerBD]

Hi Spiller -

When I remove the round function, the 100 Check column does not calculate correctly. The partial percentages do not allow for an accurate 100% check. Is my brain just stuck this morning - is there a possible workaround?

Use MIN to set the ceiling to 100%

 

[West Man]

Perhaps in this case it would be acceptable to adjust the "unknown" percentage so as to allow the total to be 100%. 1- the sum of the other 3 percentages

 

[joeu2004]

Part of the problem originates with the ROUND function in Excel. It is not Banker's Rounding, which is used in Access Rounds function and the VBA round function. This contributes to the issue can be adding the results of rounded values.

It might or might not. Banker's rounding is no panacea. Consider the following example. The unrounded sum is 100%. The sum of the banker's-rounded values is 97%.

(The sum of the normally-rounded values is 105%.)


[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]

Orig​

[/TD]
[TD="width: 64, bgcolor: transparent"]

Banker's
Rounded​

[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16.50%
[/TD]
[TD="bgcolor: transparent, align: right"]16.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8.50%
[/TD]
[TD="bgcolor: transparent, align: right"]8.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4.50%
[/TD]
[TD="bgcolor: transparent, align: right"]4.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4.50%
[/TD]
[TD="bgcolor: transparent, align: right"]4.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14.50%
[/TD]
[TD="bgcolor: transparent, align: right"]15.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12.50%
[/TD]
[TD="bgcolor: transparent, align: right"]12.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16.50%
[/TD]
[TD="bgcolor: transparent, align: right"]16.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8.50%
[/TD]
[TD="bgcolor: transparent, align: right"]8.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6.50%
[/TD]
[TD="bgcolor: transparent, align: right"]6.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7.50%
[/TD]
[TD="bgcolor: transparent, align: right"]8.00%
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]100.00%
[/TD]
[TD="bgcolor: transparent, align: right"]97.00%
[/TD]
[TD="bgcolor: transparent, align: right"]SUM
[/TD]
[/TR]
</tbody>[/TABLE]


The success of any rounding scheme to equal the sum of the unrounded values without arbitrarily and artificially manipulating the data depends on the random characteristics of the original numbers.