Rounding Values to 100%

Amosbroker

New Member
Joined
Mar 26, 2018
Messages
32
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.

HealthyModerateHighUnknownAssessed100 Check
22%29%47%2%454100%
56% 39%5%454100%
24%40%27%9%454100%
66%26%8%1%454101%
79%9%11%1%454100%
70%21%7%2%454100%
24%40%27%9%454100%
63%27%9%2%454101%

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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.
 
Upvote 0
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
ROUND(BMI!E3,2)

<tbody>
</tbody>

Referenced Formula
COUNTIF(C:C,"healthy")/D1

<tbody>
</tbody>


<tbody>
</tbody>

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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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%
 
Upvote 0
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
 
Upvote 0
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%.)



Orig

Banker's
Rounded
16.50%
16.00%
8.50%
8.00%
4.50%
4.00%
4.50%
4.00%
14.50%
15.00%
12.50%
12.00%
16.50%
16.00%
8.50%
8.00%
6.50%
6.00%
7.50%
8.00%
100.00%
97.00%
SUM

<tbody>
</tbody>


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

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top