Need formula the rounds values, and ensures rounded value sum still equals original value sum

[tommyk203]

I cant figure out how to write a formula that will round a group of numbers (original data) that equal a whole number (10 in this case), and have the rounded numbers still equal the original sum. You can see the the rounded data equals 9, when I would like it to equal 10. I've tried round up, down, etc, on larger sets of data, but it inevitably ends up being wrong. Any thoughts on how to fix this?
sample spreadsheet - Rounding Problem

original data	rounded data	corrected rounded data
1.454545455	1
4.04784689	4
3.330143541	3
1.167464115	1
10	9	10

 

[jasonb75]

I think that you might struggle to find an answer for this. It looks like you need to use something known as 'Bankers rounding'.

The most accurate methods for this all use vba, which is something that can not be used with google sheets. You might be able to find something by searching but so far I have not seen anything that I would recommend as being useful.

 

[tommyk203]

=ArrayFormula({"Reallocated shots to level 1";IF(A3:A="",,INT(A3:A/$A$2*$A$1)+IF(VLOOKUP(ROUND(A3:A/$A$2*$A$1,3),{SORT(ROUND(A3:A/$A$2*$A$1,3),(A3:A/$A$2*$A$1)-INT(A3:A/$A$2*$A$1),0),SEQUENCE(ROWS(A3:A),1)},2,FALSE)<=($A$1-SUM(INT(A3:A/$A$2*$A$1))),1,0))})

 

[Sulprobil]

A different approach which I found convenient because it minimizes the absolute error:

sbRoundToSum.xlsm
	A	B	C	D	E	F	G	H
1			bAbsSum	TRUE			Total objective
2			Decimals to round to:	0			Difference to rounded
3		Absolute values	Relative values	Rounded values	Error	Rank of	10,0000	Result
4	Total	10,0000	100,0000	9,0000		error	1,0000	10,0000
5	1	1,454545455	14,5455	1,0000	-0,4545	1	1,0000	2,0000
6	2	4,04784689	40,4785	4,0000	-0,0478	4	0,0000	4,0000
7	3	3,330143541	33,3014	3,0000	-0,3301	2	0,0000	3,0000
8	4	1,167464115	11,6746	1,0000	-0,1675	3	0,0000	1,0000
RoundToSum

Cell Formulas
Range	Formula
G3	G3	=ROUND(IF($D$1,SUM(B5:B8),SUM(C5:C8)),$D$2)
H4,B4:D4	B4	=SUM(B5:B8)
G4	G4	=G3-D4
C5:C8	C5	=B5/$B$4%
D5:D8	D5	=ROUND(IF($D$1,B5,C5),$D$2)
E5:E8	E5	=D5-IF($D$1,B5,C5)
F5:F8	F5	=COUNTIF($E$5:$E$8,IF($G$4>0,"<",">")&$E5)+COUNTIF($E$5:$E5,$E5)
G5:G8	G5	=IF(F5<=ROUND(ABS($G$4*10^$D$2),0),SIGN($G$4)*10^-$D$2,0)
H5:H8	H5	=D5+G5

 

[tommyk203]

A different approach which I found convenient because it minimizes the absolute error:

sbRoundToSum.xlsm
	A	B	C	D	E	F	G	H
1			bAbsSum	TRUE			Total objective
2			Decimals to round to:	0			Difference to rounded
3		Absolute values	Relative values	Rounded values	Error	Rank of	10,0000	Result
4	Total	10,0000	100,0000	9,0000		error	1,0000	10,0000
5	1	1,454545455	14,5455	1,0000	-0,4545	1	1,0000	2,0000
6	2	4,04784689	40,4785	4,0000	-0,0478	4	0,0000	4,0000
7	3	3,330143541	33,3014	3,0000	-0,3301	2	0,0000	3,0000
8	4	1,167464115	11,6746	1,0000	-0,1675	3	0,0000	1,0000
RoundToSum

Cell Formulas
Range	Formula
G3	G3	=ROUND(IF($D$1,SUM(B5:B8),SUM(C5:C8)),$D$2)
H4,B4:D4	B4	=SUM(B5:B8)
G4	G4	=G3-D4
C5:C8	C5	=B5/$B$4%
D5:D8	D5	=ROUND(IF($D$1,B5,C5),$D$2)
E5:E8	E5	=D5-IF($D$1,B5,C5)
F5:F8	F5	=COUNTIF($E$5:$E$8,IF($G$4>0,"<",">")&$E5)+COUNTIF($E$5:$E5,$E5)
G5:G8	G5	=IF(F5<=ROUND(ABS($G$4*10^$D$2),0),SIGN($G$4)*10^-$D$2,0)
H5:H8	H5	=D5+G5

Thanks. Anyway to get all of that in a single column so we dont need so many helper columns?

 

[Sulprobil]

Google for excel round summands to rounded sum, or for vba round summands to rounded sum.

TCRound is a commercial approach, Gustav Brock's solution is free of charge, as is mine.