Allocation (Rounding Issue)

[Adubb707]

Anyone who can assist would be my hero for life; this is a common problem I encounter at work and am hoping it can be handled formulaically.
I have totals (column B) that need to be fully allocated based on spreads based on Row 1 percentages; the values must be whole numbers. Because of rounding not all values in column B end up getting fully allocated across each of the months and I have to manually force-tie values. Additionally, the total for be month needs to tie back to the overall spread %'s.
Any help is greatly appreciated!

 

[SanjayGMusafir]

Can you show me some examples how you re-distribute values if there is a variance

Or Explain how you do that when there is any such situation.

Excel will do what we will tell it to do... So I should be clear what to tell Excel

 

[Adubb707]

Can you show me some examples how you re-distribute values if there is a variance

Or Explain how you do that when there is any such situation.

Excel will do what we will tell it to do... So I should be clear what to tell Excel

To address variances I look at the variances, by row, determine if there is also a variance by month, if both criteria is met I will add/subtract the variance from the row value until none exist.
IE: Cell C7:D7 I would add values of 1 in each in order to clear the variance by row and by column

 

[SanjayGMusafir]

Check this and revert -

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		7.83%	8.98%	7.86%	8.68%	8.27%	8.24%	8.27%	8.27%	8.49%	8.27%	8.65%	8.19%	100.00%
2														Check	Variance
3	339	27	30	27	29	28	28	28	28	29	28	29	28	339.00	-
4	204	15	18	16	18	17	17	17	17	17	17	18	17	204.00	-
5	181	14	16	14	16	15	15	15	15	15	15	16	15	181.00	-
6	66	6	7	5	6	5	5	5	5	6	5	6	5	66.00	-
7	33	2	2	2	3	3	3	3	3	3	3	3	3	33.00	-
8	40	4	5	4	3	3	3	3	3	3	3	3	3	40.00	-
9	4	1	1	1	1	-	-	-	-	-	-	-	-	4.00	-
Sheet1

Cell Formulas
Range	Formula
O1,O3:O9	O1	=SUM(C1:N1)
C3:N9	C3	=LET(Ttl,ROUND($B3*$C$1,0)+ROUND($B3*$D$1,0)+ROUND($B3*$E$1,0)+ROUND($B3*$F$1,0)+ROUND($B3*$G$1,0)+ROUND($B3*$H$1,0)+ROUND($B3*$I$1,0)+ROUND($B3*$J$1,0)+ROUND($B3*$K$1,0)+ROUND($B3*$L$1,0)+ROUND($B3*$M$1,0)+ROUND($B3*$N$1,0), Diff,$B3-Ttl, Clmn,COLUMN(C3)-COLUMN($B3), Adjs,IFS(AND(Diff>0,Diff>=Clmn),1,AND(Diff<0,-Diff>=Clmn),-1,TRUE,0), IFS(Ttl=$B3,ROUND($B3*C$1,0),TRUE,ROUND($B3*C$1,0)+Adjs))
P3:P9	P3	=B3-O3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O	Expression	=AND(B1<>"",O1<>B1)	text	NO

 

[jorismoerings]

Hi,

could something like this work for you:

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		7,83%	8,98%	7,86%	8,68%	8,27%	8,24%	8,27%	8,27%	8,49%	8,27%	8,65%	8,19%		100,00%
2	339	27	30	27	29	28	28	28	28	29	28	29	28		339
3	204	16	18	16	18	17	17	17	16	18	17	17	17		204
4	181	14	16	15	15	15	15	15	15	16	15	15	15		181
5	66	5	6	5	6	5	6	5	6	5	6	6	5		66
6	33	3	3	2	3	3	2	3	3	3	2	3	3		33
7	40	3	4	3	3	4	3	3	4	3	3	4	3		40
8	4	-	1	-	-	1	-	-	1	-	-	1	-		4
9	31	2	3	3	2	3	2	3	3	2	3	2	3		31
10	11	1	1	1	1	1	-	1	1	1	1	1	1		11
11	2	-	-	-	1	-	-	-	-	-	1	-	-		2
12	1	-	-	-	-	-	-	1	-	-	-	-	-		1
13	6	-	1	-	1	-	1	-	1	-	1	1	-		6
14	3	-	1	-	-	-	-	1	-	-	-	1	-		3
15	1	-	-	-	-	-	-	1	-	-	-	-	-		1
16	4	-	1	-	-	1	-	-	1	-	-	1	-		4
17	7	1	-	1	-	1	-	1	1	-	1	-	1		7
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=ROUND(SUM($C$1:C$1)*$B2,0)
D2:N17	D2	=ROUND(SUM($C$1:D$1)*$B2,0)-ROUND(SUM($C$1:C$1)*$B2,0)
P1	P1	=SUM(C1:N1)
P2:P17	P2	=SUM(C2:O2)

 

[Adubb707]

Check this and revert -

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		7.83%	8.98%	7.86%	8.68%	8.27%	8.24%	8.27%	8.27%	8.49%	8.27%	8.65%	8.19%	100.00%
2														Check	Variance
3	339	27	30	27	29	28	28	28	28	29	28	29	28	339.00	-
4	204	15	18	16	18	17	17	17	17	17	17	18	17	204.00	-
5	181	14	16	14	16	15	15	15	15	15	15	16	15	181.00	-
6	66	6	7	5	6	5	5	5	5	6	5	6	5	66.00	-
7	33	2	2	2	3	3	3	3	3	3	3	3	3	33.00	-
8	40	4	5	4	3	3	3	3	3	3	3	3	3	40.00	-
9	4	1	1	1	1	-	-	-	-	-	-	-	-	4.00	-
Sheet1

Cell Formulas
Range	Formula
O1,O3:O9	O1	=SUM(C1:N1)
C3:N9	C3	=LET(Ttl,ROUND($B3*$C$1,0)+ROUND($B3*$D$1,0)+ROUND($B3*$E$1,0)+ROUND($B3*$F$1,0)+ROUND($B3*$G$1,0)+ROUND($B3*$H$1,0)+ROUND($B3*$I$1,0)+ROUND($B3*$J$1,0)+ROUND($B3*$K$1,0)+ROUND($B3*$L$1,0)+ROUND($B3*$M$1,0)+ROUND($B3*$N$1,0), Diff,$B3-Ttl, Clmn,COLUMN(C3)-COLUMN($B3), Adjs,IFS(AND(Diff>0,Diff>=Clmn),1,AND(Diff<0,-Diff>=Clmn),-1,TRUE,0), IFS(Ttl=$B3,ROUND($B3*C$1,0),TRUE,ROUND($B3*C$1,0)+Adjs))
P3:P9	P3	=B3-O3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O	Expression	=AND(B1<>"",O1<>B1)	text	NO

This is genius work really; thank you for your input - where this does pose issues is that in aggregate, the monthly values (x-axis) becomes skewed-left as opposed to the intended overall distribution; this allocation is made up of many sections (all referencing the same percentages) but some with higher volumes, others with lower.

 

[SanjayGMusafir]

The number of items (Description 1 etc.) remain same or variable?

Also, the problem is system will choose to shift one of the axis. And how to tell excel which one should it choose?

 

[Adubb707]

The number of items (Description 1 etc.) remain same or variable?

The value of that particular cell would remain the static (hard-coded); the file has multiple sub-sections (groups of "Description" records) so I just re-used the headers with smaller amounts to better demonstrate the propensity of it having been allocated in the first half of the year.

 

[SanjayGMusafir]

the file has multiple sub-sections (groups of "Description" records)

With multiple sub-sections it becomes difficult to explain Excel how to adjust the variance.

It can go into endless loop or circular reference if we tell excel to adjust 2 directions simultaneously...

At times there are some limitations to machine and thus human overriding becomes essential/critical.