Excel Formula to Adjust Difference equally amoung rest

[Jalal Kasmani]

Hi Formula Champions,

Book1.xlsx
	E	F	G	H	I	J	K	L	M	N
1		22%		38%		20%		20%
2	RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0
3	100	22	Yes	0	No	22	Yes	22	Yes	-34
4	100	0	No	0	No	22	Yes	22	Yes	-56
Sheet1

Cell Formulas
Range	Formula
L3:L4,J3:J4,H3,F3:F4	F3	=IF(G3="No",0,$E$3*$F$1)
H4	H4	=IF(I4="No",0,$E$3*H2)
N3:N4	N3	=(F3+H3+J3+L3)-E3

I want to adjust the difference of column N equally amoung rest Employees wherever it is yes. Please help i tried multiple things still no luck , its giving cell reference error.

 

[Jalal Kasmani]

Below is the correct thread and the desired output in cell J11 and L11.

Book1.xlsx
	E	F	G	H	I	J	K	L	M	N
1		22%		38%		20%		20%
2	RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0
3	100	22	Yes	0	No	20	Yes	20	Yes	-38
4	100	0	No	0	No	20	Yes	20	Yes	-60
5
6	Desired Output
7
8		22%		38%		20%		20%
9	RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0
10	100	22	Yes	0	No	20	Yes	20	Yes	-38
11	100	0	No	0	No	50	Yes	50	Yes	0
Sheet1

Cell Formulas
Range	Formula
H11,F10:F11,L4,J4,H4,F3:F4	F3	=IF(G3="No",0,$E3*F$1)
H3,L10,J10,H10,L3,J3	H3	=IF(I3="No",0,$E$3*H$1)
N10:N11,N3:N4	N3	=(F3+H3+J3+L3)-E3
J11	J11	=IF(K11="No",0,$E11*J$1) + 30
L11	L11	=IF(M11="No",0,$E11*L$1) +30

 

[Jalal Kasmani]

Can anyone please help ?

 

[jtakw]

Hi,

Does this do what you need:

Book3.xlsx
	E	F	G	H	I	J	K	L	M	N
1		22%		38%		20%		20%
2	RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0
3	100	33.33	Yes	0	No	33.33	Yes	33.33	Yes	0
4	100	0	No	0	No	50	Yes	50	Yes	0
Sheet1033

Cell Formulas
Range	Formula
L3:L4,J3:J4,H3:H4,F3:F4	F3	=IF(G3="No",0,IF(SUM($G3="Yes",$I3="Yes",$K3="Yes",$M3="Yes")=4,$E3*F$1,$E3/SUM($G3="Yes",$I3="Yes",$K3="Yes",$M3="Yes")))
N3:N4	N3	=(F3+H3+J3+L3)-E3

 

[Jalal Kasmani]

You are almost close to the solution, your solution ignores the % in row number 1. It has to first allocate basis the % in row number 1 and than whatever is left in column N that should get divided.

For ex:- when you go to my example in row number 11(ignore row 10). I have first allocated on the basis of % there in row 1, and than whatever is left ie 60 . i have manually entered 30 each (60 divided by 2 as there are 2 Yes), - infact this should come with a formula. I get circular reference error when I do that with a formula.

I have one other idea as well, instead of applying formula in col N to get the difference and than dividing equally(which gives circular ref error). We can rather keep adding amount using row number 1 percentage wherever It's No, and than divide that amount equally amoung the people wherever it's Yes. Thumb rule is to first consider the % in row 1 and than add.

 

[Dave Patton]

You can edit the formulas if you want negative numbers

T202203a.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1		0.22		0.38		0.2		0.2
2	RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0
3	100	0	No	0	No	50	Yes	50	Yes	0	0.4
4
1c

Cell Formulas
Range	Formula
F3	F3	=(G3="yes")*F1/O3*E3
H3	H3	=(I3="yes")*H1/O3*E3
J3	J3	=(K3="yes")*J1/O3*E3
L3	L3	=(M3="yes")*L1/O3*E3
N3	N3	=E3-SUM(F3:M3)
O3	O3	=((G3="yes")*$F$1+(I3="yes")*$H$1+(K3="yes")*$J$1+(M3="yes")*$L$1)

 

[Dave Patton]

or you consider either of the following

T202203a.xlsm
	E	F	G	H	I	J	K
7	RW Total	Emp A	Emp B	Emp C	Emp D	Diff - Should net to 0
8		0.22	0.38	0.2	0.2
9		No	No	Yes	Yes
10	100	0	0	50	50	0	0.4
11	150	0	0	75	75	0
1c

Cell Formulas
Range	Formula
F10:I10	F10	=(F$9="yes")*F$8/$K$10*$E$10
J10:J11	J10	=E10-SUM(F10:I10)
K10	K10	=SUMPRODUCT(--($F$9:$I$9="yes"),$F$8:$I$8)
F11:I11	F11	=(F$9="yes")*F$8/SUMPRODUCT(--($F$9:$I$9="yes"),$F$8:$I$8)*$E$11

 

[jtakw]

You are almost close to the solution, your solution ignores the % in row number 1. It has to first allocate basis the % in row number 1 and than whatever is left in column N that should get divided.

For ex:- when you go to my example in row number 11(ignore row 10). I have first allocated on the basis of % there in row 1, and than whatever is left ie 60 . i have manually entered 30 each (60 divided by 2 as there are 2 Yes), - infact this should come with a formula. I get circular reference error when I do that with a formula.

I have one other idea as well, instead of applying formula in col N to get the difference and than dividing equally(which gives circular ref error). We can rather keep adding amount using row number 1 percentage wherever It's No, and than divide that amount equally amoung the people wherever it's Yes. Thumb rule is to first consider the % in row 1 and than add.

My formula First test the % allocations, whatever is left, gets Equally divided amongst the Yes Emp.
If it doesn't work as intended, please post sample where it doesn't.

 

[Jalal Kasmani]

Hi,

Below your formula and what i require in simple 2 steps, your formula does the partial trick and gives the difference as 0. But not sure how the difference amount is adjusted.

Book1.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1				0.22		0.38		0.18		0.22
2			RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0	Check
3		Allocation with your formula	155	41.59	Yes	71.83	Yes	-	No	41.59	Yes	0	0.82
4				7.49	Please justify the logic why the allocation + 7.49	12.93	Please justify the logic why the allocation + 12.93			7.49	Please justify the logic why the allocation + 7.49	27.90
5		Allocation below with my logic with 2 steps
6	Step 1	Simple Allocation	155	34.10		58.9				34.1			27.90
7	Step 2	Final allocation with diff adjusted equally		43.40		68.20				43.40		-
8
9												9.30
Sheet2

Cell Formulas
Range	Formula
J3	J3	=(K3="yes")*J1/O3*E3
O3	O3	=((G3="yes")*$F$1+(I3="yes")*$H$1+(K3="yes")*$J$1+(M3="yes")*$L$1)
F3	F3	=(G3="yes")*F1/O3*E3
F4,L4,H4	F4	=F3-F6
H3	H3	=(I3="yes")*H1/O3*E3
L3	L3	=(M3="yes")*L1/O3*E3
N3	N3	=E3-SUM(F3:M3)
N4	N4	=SUM(F4:L4)
O6	O6	=E6-SUM(F6:N6)
F6	F6	=E3*F1
F7	F7	=F6+N9
H6	H6	=E6*H1
H7	H7	=H6+N9
L6	L6	=E6*L1
L7	L7	=L6+N9
N7	N7	=E6-SUM(F7:M7)
N9	N9	=O6/3

 

[jtakw]

This should do what you want.

Book3.xlsx
	E	F	G	H	I	J	K	L	M	N
1		22%		38%		20%		20%
2	RW Total	Emp A	Emp A Yes/No	Emp B	Emp B Yes/No	Emp C	Emp C Yes/No	Emp D	Emp D Yes/No	Diff - Should net to 0
3	100	35.48	Yes	0	No	32.26	Yes	32.26	Yes	0
4	100	0	No	48.72	Yes	25.64	Yes	25.64	Yes	0
Sheet1033

Cell Formulas
Range	Formula
L3:L4,J3:J4,H3:H4,F3:F4	F3	=IF(G3="No",0,IF(AND($G3="Yes",$I3="Yes",$K3="Yes",$M3="Yes"),$E3*F$1,F$1/(($G3="Yes")*$F$1+($I3="Yes")*$H$1+($K3="Yes")*$J$1+($M3="Yes")*$L$1)*$E3))
N3:N4	N3	=(F3+H3+J3+L3)-E3