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]

 

[Amosbroker]

Thanks for the input, guys. I think I am going to propose using partial percentages moving forward. It makes more sense to have an accurate picture of data than to spend so much time manipulating for the sake of 2 and 1 digit numbers totaling 100%.

 

[RobVos]

I know this is a bit old, but I was thinking about this and have a reason to want whole numbers (rounded) but they need to total to 360.
Yes, I understand this is manipulating the actual data a bit - but that is the desire for whatever reason. What I did was this in columns:

• round each number down [round down]
• put the decimal part in the next column [remainder range]
• have a column with sequential index numbers (1, 2, 3, ...) [index]
• sum the rounded down column into a cell
• in another cell subtract the sum from my number (which was 360) [to add] (in the example, I put it in K2)
• use index-match with the large function =index(match(large([remainder range],[index]),[remainder range],0),1)
• in the next column, add 1 to the rounded down number if the sequential row is within a range of the top remainders the size of the number needed to add

The last column will have essentially rounded the numbers with the largest remainder back up, doing so the number of times to get to the required sum.

Top cell (Original numbers) is F2
G3 =ROUND(F3,0)
I3 =ROUNDDOWN(F3,0)
J3 =F3-TRUNC(F3)
K2 =360-I14
N3 =INDEX(MATCH(LARGE($J$3:$J$13,L3),$J$3:$J$13,0),1)
O3 =I3+COUNTIF(OFFSET($N$3,0,0,$K$2),L3)

 

[Sulprobil]

I suggest to search for "round summands to match a total".
TCRound is a commercial solution.
Gustav Brock offers a free VBA.

 

[Sulprobil]

Looking at RobVos's example, this would be a worksheet function approach (D1 has the value TRUE!):
RobVos is rounding the 9th number into the "wrong" direction, my functions do this for the 8th (because it is the first one of two with the same minimal absolute error)

MrExcel_Rounding_Values_to_100_Percent.xlsx
	A	B	C	D	E	F	G	H
1			bAbsSum	WAHR			Total objective
2			Decimals to round to:	0			Difference to rounded
3		Absolute values	Relative values	Rounded values	Error	Rank of	360,0000	Result
4	Total	360,0000	100,0000	361,0000		error	-1,0000	360,0000
5	1	106,9407	29,7057	107,0000	0,0593	6	0,0000	107,0000
6	2	52,3316	14,5366	52,0000	-0,3316	11	0,0000	52,0000
7	3	8,6925	2,4146	9,0000	0,3075	4	0,0000	9,0000
8	4	34,6657	9,6294	35,0000	0,3343	3	0,0000	35,0000
9	5	2,9754	0,8265	3,0000	0,0246	7	0,0000	3,0000
10	6	50,9349	14,1486	51,0000	0,0651	5	0,0000	51,0000
11	7	3,1477	0,8744	3,0000	-0,1477	9	0,0000	3,0000
12	8	9,5612	2,6559	10,0000	0,4388	1	-1,0000	9,0000
13	9	9,5612	2,6559	10,0000	0,4388	2	0,0000	10,0000
14	10	81,1892	22,5526	81,0000	-0,1892	10	0,0000	81,0000
15	11	0,0000	0,0000	0,0000	0,0000	8	0,0000	0,0000
RoundToSum

Cell Formulas
Range	Formula
G3	G3	=ROUND(IF($D$1,SUM(B5:B15),SUM(C5:C15)),$D$2)
H4,B4:D4	B4	=SUM(B5:B15)
G4	G4	=G3-D4
C5:C15	C5	=B5/$B$4%
D5:D15	D5	=ROUND(IF($D$1,B5,C5),$D$2)
E5:E15	E5	=D5-IF($D$1,B5,C5)
F5:F15	F5	=COUNTIF($E$5:$E$15,IF($G$4>0,"<",">")&$E5)+COUNTIF($E$5:$E5,$E5)
G5:G15	G5	=IF(F5<=ROUND(ABS($G$4*10^$D$2),0),SIGN($G$4)*10^-$D$2,0)
H5:H15	H5	=D5+G5

If you round not to integers but to 10 (not ROUND(x,0) but ROUND(x,-1)) you would need to round the 4th number into the "wrong" direction:

MrExcel_Rounding_Values_to_100_Percent.xlsx
	A	B	C	D	E	F	G	H
1			bAbsSum	WAHR			Total objective
2			Decimals to round to:	-1			Difference to rounded
3		Absolute values	Relative values	Rounded values	Error	Rank of	360,0000	Result
4	Total	360,0000	100,0000	350,0000		error	10,0000	360,0000
5	1	106,9407	29,7057	110,0000	3,0593	11	0,0000	110,0000
6	2	52,3316	14,5366	50,0000	-2,3316	4	0,0000	50,0000
7	3	8,6925	2,4146	10,0000	1,3075	10	0,0000	10,0000
8	4	34,6657	9,6294	30,0000	-4,6657	1	10,0000	40,0000
9	5	2,9754	0,8265	0,0000	-2,9754	3	0,0000	0,0000
10	6	50,9349	14,1486	50,0000	-0,9349	6	0,0000	50,0000
11	7	3,1477	0,8744	0,0000	-3,1477	2	0,0000	0,0000
12	8	9,5612	2,6559	10,0000	0,4388	8	0,0000	10,0000
13	9	9,5612	2,6559	10,0000	0,4388	9	0,0000	10,0000
14	10	81,1892	22,5526	80,0000	-1,1892	5	0,0000	80,0000
15	11	0,0000	0,0000	0,0000	0,0000	7	0,0000	0,0000
RoundToSum

Cell Formulas
Range	Formula
G3	G3	=ROUND(IF($D$1,SUM(B5:B15),SUM(C5:C15)),$D$2)
H4,B4:D4	B4	=SUM(B5:B15)
G4	G4	=G3-D4
C5:C15	C5	=B5/$B$4%
D5:D15	D5	=ROUND(IF($D$1,B5,C5),$D$2)
E5:E15	E5	=D5-IF($D$1,B5,C5)
F5:F15	F5	=COUNTIF($E$5:$E$15,IF($G$4>0,"<",">")&$E5)+COUNTIF($E$5:$E5,$E5)
G5:G15	G5	=IF(F5<=ROUND(ABS($G$4*10^$D$2),0),SIGN($G$4)*10^-$D$2,0)
H5:H15	H5	=D5+G5

 

[SpillerBD]

Thanks for the input, guys. I think I am going to propose using partial percentages moving forward. It makes more sense to have an accurate picture of data than to spend so much time manipulating for the sake of 2 and 1 digit numbers totaling 100%.

I had this pinned but never commented on till now...
The calculated percentages should not be rounded. They should be displayed to the desired decimal place.
The SUM of the percentages should be rounded. There can be a small variance thanks to the limited decimal places in memory for number storage.

 

[Sulprobil]

Sorry, no.
When rounded summands do not match their rounded sum, you need to round some of them into the "wrong" direction to make it match.
Try to give one Cent to a) two people or b) to three people. In the first case you would give 2 Cents if you round 0.5 to 1 for both summands (so you need to round one of them down "erroneously"), but in the latter you would not give anything if you round 0.3333... to 0 (unless you round one of them up to 1).

 

[SpillerBD]

Sorry, no.
When rounded summands do not match their rounded sum, you need to round some of them into the "wrong" direction to make it match.
Try to give one Cent to a) two people or b) to three people. In the first case you would give 2 Cents if you round 0.5 to 1 for both summands (so you need to round one of them down "erroneously"), but in the latter you would not give anything if you round 0.3333... to 0 (unless you round one of them up to 1).

I believe you misunderstand.
The individual calculations for the percentages are not rounded.
The Sum of the individual items would be rounded to 0 decimal places.
There would likely be errors in the raw sum. Something likely around 5E-11. Rounding that sum should maintain a 100%
Yes. An annotation must be made to explain that the displayed %'s may not appear to equal 100. That is because they are rounded in the displayed value to whatever desired decimal place.

A cent to more than one person? We don't cut our pennies up like in ye'old England!

 

[Sulprobil]

I beg to differ because I think the problem at hand could best be described as "rounding summands preserving their rounded sum".
If you google for that you will find several solutions, one of them from Gustav Brock.

 

[SpillerBD]

Its two different approaches. When we get to the pennies of a distribution, a solution like the one referenced is suitable or even necessary.
However, when looking at the percentages of referenced values, it may be inappropriate to say one received a percentage more than the other 2.
Column C is displayed to the one decimal place and shows each being an equal percentage. Yet column C has no rounding applied. Their sum equals 100% without any finagling.
Column D has rounding applied, which leads to the sum being off.
In either case, if the %'s are used to distribute dollar amount, the dollar amounts will likely be off.

So its a matter of the application to which method may be best. However I feel that the better answer to the OP's case is to not round the values except for the SUM amount to ensure certain base2 to base10 issues sometimes found with IEEE754 (and additional limitations with MS)

Book1
	A	B	C	D
1	3
2		=A3/$A$1	=B3	=ROUND(C3,3)
3	1	0.333333333333333	33.3%	33.3%
4	1	0.333333333333333	33.3%	33.3%
5	1	0.333333333333333	33.3%	33.3%
6			100.0%	99.9%
Sheet1

Cell Formulas
Range	Formula
B2:D2	C2	=FORMULATEXT(C3)
C3:C5	C3	=B3
D3:D5	D3	=ROUND(C3,3)
C6:D6	C6	=SUM(C3:C5)
B3:B5	B3	=A3/$A$1

 

[Sulprobil]

You won't value my suggested approach unless you need to book both inputs and outputs in your company's accounting system. But then you will regret a cent's difference because your accountant does.