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,
I managed to streamline it further.

In Cells S31
=IF(ROUND(ABS($Q31-(SUM($R31:R31)+$Q31*(S$27/$O31))),2)>0.01,ROUND($Q31*(S$27/$O31),2),ROUND($M31-SUM($R31:R31),2))

Any more suggestions would be greatly appreciated.

Kind Regards

Biz

 

[Biz]

Revised Formulas are listed below.

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($Q31-(SUM($R31:R31)+$Q31*(S$27/$O31))),2)>0.01,ROUND($Q31*(S$27/$O31),2),ROUND($M31-SUM($R31:R31),2))
W31:W36	W31	=SUM(S31:U31)
X31:X36	X31	=NOT(Q31=W31)

 

[Biz]

My final streamline solution
=IF(ROUND(ABS($Q31*(1-(S$27/$O31))-(SUM($R31:R31))),2)>0.01,ROUND($Q31*(S$27/$O31),2),ROUND($Q31-SUM($R31:R31),2))


Explanation
=If(Round(Total ($)*(1-(P1(%)/Total (%)))- Sum(Allocated Amounts,2)>0.01,ROUND(Total($)*(P1(%)/Total (%)),2),ROUND(Total ($)-SUM(Allocated Amounts),2)

Where P1 would refer to cells S27:U27 depending which cells the formulas are.

Hope this helps someone with a similar question in the future.

Biz

 

[Peter_SSs]

Would you consider this more streamlined? I have used two formulas instead of one, but they are much simpler.
You haven't explained the logic of what you are doing but this produces the same results for that sample data.

22 08 08.xlsm
	O	P	Q	R	S	T	U
26
27					10.70%	4.00%	35.30%
28	Total (%)	M1 (%)	Total ($)		1	2	3
29
30
31	50%	100%	13,545.00		2,898.63	1,083.60	9,562.77
32	50%	100%	202,634.17		43,363.71	16,210.73	143,059.73
33	50%	100%	10,000.00		2,140.00	800.00	7,060.00
34	50%	100%	18,450.00		3,948.30	1,476.00	13,025.70
35	50%	100%	127,368.50		27,256.86	10,189.48	89,922.16
36	50%	100%	47,500.00		10,165.00	3,800.00	33,535.00
Biz

Cell Formulas
Range	Formula
S31:T36	S31	=ROUND($Q31*(S$27/$O31),2)
U31:U36	U31	=Q31-SUM(S31:T31)

 

[hicksi]

Rounding is, and can be, quite complex if you are avoiding rounding errors.
In simplistic terms, the splitting is best done by round(Sum-so-far/Total) - Sum(Rounded-prior-so-far), but even this can result in strange errors when performed month-to-month.
Ie, 1 split in 7ths as 1, 2, 4 is:
Round(1/7) = 0.14
Round((1+2)/7) - 0.14 = 0.43 - 0.14 = 0.29
Round((1+2+4)/7) - (0.14+0.29) = 1 - 0.43 = 0.57.

Next month, the same split produces the same result, as does the third month, etc. Leading to total rounded amounts after 7 months of 0.98, 2.03, 3.99 instead of 1, 2, 4.
Thus, in the second month, you need to apply YTD and differences
Round((1+1)/7) - 0.14 = 0.29 - 0.14 = 0.15
Round(((1+2)+(1+2))/7) - (0.14+0.29) - 0.15 = 0.86 - 0.58 = 0.28
Round(((1+2+4)+(1+2+4))/7) - (0.14+0.29+0.57) - (0.15+0.28) = 2 - 1 - 0.43 = 0.57

Followed by Month 3:
0.14, 0.29, 0.57
Month 4:
0.14, 0.28, 0.58
Month 5:
0.14, 0.29, 0.57
Month 6:
0.15, 0.28, 0.57
Month 7:
0.14, 0.29, 0.57

Which, if you add it all up gives the exact value:
0.14*5 + 0.15*2 = 0.70 + 0.30 = 1
0.29*4 + 0.28*3 = 1.16 + 0.84 = 2
0.57*6 + 0.58*1 = 3.42 + 0.58 = 4

Whether all that can be simplified or not, I'm not sure.
BTW, that method also works if the portioning is 1,2,4, then 4,1,2, then 2,4,1, which gives each of them a total value of 1.

And if you think that's complex, try having the occasional zero, and ensuring that the rounded ytd less last month's ytd doesn't go negative.

 

[Biz]

Would you consider this more streamlined? I have used two formulas instead of one, but they are much simpler.
You haven't explained the logic of what you are doing but this produces the same results for that sample data.

22 08 08.xlsm
	O	P	Q	R	S	T	U
26
27					10.70%	4.00%	35.30%
28	Total (%)	M1 (%)	Total ($)		1	2	3
29
30
31	50%	100%	13,545.00		2,898.63	1,083.60	9,562.77
32	50%	100%	202,634.17		43,363.71	16,210.73	143,059.73
33	50%	100%	10,000.00		2,140.00	800.00	7,060.00
34	50%	100%	18,450.00		3,948.30	1,476.00	13,025.70
35	50%	100%	127,368.50		27,256.86	10,189.48	89,922.16
36	50%	100%	47,500.00		10,165.00	3,800.00	33,535.00
Biz

Cell Formulas
Range	Formula
S31:T36	S31	=ROUND($Q31*(S$27/$O31),2)
U31:U36	U31	=Q31-SUM(S31:T31)

Hi Peter,
Your formulas work, but I'm after one formula to do allocation.

Kind Regards

Biz

 

[Peter_SSs]

but I'm after one formula to do allocation.

A lot more calculation required ... but your choice.

 

[hicksi]

Biz...
So long as you are concerned ONLY with this period's values, and NOT having any comparison with this-period against periods-to-date, then it is possible to simplify.
If you read my prior missive, (which, BTW, calculates the portion, not the value - oops), then...
In fact, you need 4 formulae (or 3), though each one is simpler.

*** WHILE ALL THIS WORKS, I have some issue with your methodology, made more confusing by the fact that 50% produces the same values in columns M and Q.
And, given that the sum of S27:U27 is also 50%, I must assume that the value in column O must be a constant (in fact, 50%).

If you are applying an adjustment to M to produce Q, then the 'logic' says that you are allocating Q over the portions S:U, which implies that they must add up to 1.
And, if you look at the equations involving S27 through U27, then they always involve an adjustment by the column O value.
So, you could simplify the whole set by making S27:U27 add to 1.
-----------------------

Row 1, Column 1:
is the rounded value of relative row value
S31 =ROUND(Q31*S27/O31,2)

Row 1, Column *:
is the rounded sum-value less the sum of previous rounded values in this row
T31 =ROUND($Q31*SUM($S$27:T$27)/$O$31,2)-SUM($S31:S31)
which can be copied to U31

Row *, Column 1:
is the rounded sum-value less the sum of previous rounded values in this column
S32 =ROUND(SUM($Q$31:$Q32)*$S$27/$O$31,2)-SUM(S$31:S31)
which can be copied to S33 through S36

Row *, Column *:
You have two choices here, depending on your concept of priority.
Because your row-total is the priority, you can therefore copy T31:U31 down.

 

[hicksi]

OH, and one last thing. The calculation in the final column will always be the rounded total less all the previous rounded values - Hence ALWAYS adding up correctly.
Ditto that the final row is the total less the sum of all previous

And a PPS...
If you prioritise the columns, then the calculations may result in a cent-or-two difference in individual cells, but once again, they will ALWAYS add up. Because of that previous sentence.