Allocate Amounts without Rounding Errors

[Biz]

Hi,

I have got formulas working, but hoping if there's more streamline method.
The formulas that need to be streamlined are in Range(S31:U36).

Allocating Amounts.xlsx
	M	N	O	P	Q	R	S	T	U	V	W	X
27							10.70%	4%	35%
28			Total (%)	M1 (%)	Total ($)		1	2	3		Total	Checks Required
29
30
31	13,545.00		50%	100%	13,545.00		2,898.63	1,083.60	9,562.77		13,545.00	FALSE
32	202,634.17		50%	100%	202,634.17		43,363.71	16,210.73	143,059.73		202,634.17	FALSE
33	10,000.00		50%	100%	10,000.00		2,140.00	800.00	7,060.00		10,000.00	FALSE
34	18,450.00		50%	100%	18,450.00		3,948.30	1,476.00	13,025.70		18,450.00	FALSE
35	127,368.50		50%	100%	127,368.50		27,256.86	10,189.48	89,922.16		127,368.50	FALSE
36	47,500.00		50%	100%	47,500.00		10,165.00	3,800.00	33,535.00		47,500.00	FALSE
Sheet1

Cell Formulas
Range	Formula
P31:P36	P31	=O31/(1-O31)
Q31:Q36	Q31	=(M31*(1+P31))*O31
S31:U36	S31	=IF(ROUND(ABS(SUM($R31:R31)+$Q31*(S$27/$O31)-$Q31),2)>0.01,ROUND($Q31*(S$27/$O31),2),ROUND($Q31*(S$27/$O31)+$M31-($Q31*(S$27/$O31)+SUM($R31:R31)),2))
W31:W36	W31	=SUM(S31:U31)
X31:X36	X31	=NOT(Q31=W31)

Your help would be greatly appreciated.

Kind Regards

Biz

 

[Biz]

Hi Hicksi,

I looked at your approach and it works with small rounding error but it adds in total.
I modified your formula
Copy S31 to U36
=IF(SUM($R31:R31)=0,ROUND(Q31*S$27/O31,2),ROUND($Q31*SUM($S$27:S$27)/$O31,2)-SUM(R31:$S31))

Kind Regards

Biz

 

[hicksi]

Just be careful with that...
The logic of the formula says that you are subtracting the 'previous'. For row 1 and column 1 (in your case 31 and S) there is only an implied zero sum. It only works because you have nothing in column R and row 30. Otherwise, it'll blow up big-time.

 

[hicksi]

HOLY SH!T.
That Works... No IF statement needed.
Because column R is empty (zero), each cell can be set with
T31 =ROUND($Q31*SUM($S$27:T$27)/$O$31,2)-SUM($R31:S31)

And yes, you WILL get some differences in individual cells. You are trying to round each individual value, whereas I am rounding the sum-of-the-values-so-far.
Mine (by definition) will always total correctly.