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.

 

[Dave Patton]

T202203a.xlsm
	D	E	F	G	H	I	J
7		RW Total	Emp A	Emp B	Emp C	Emp D	Diff - Should net to 0
8			22.00%	38.00%	20.00%	20.00%
9			yes	yes	No	Yes
10		155	44.43	69.23	0.00	41.33	0
11	Difference proportionately	155	42.625	73.625	0	38.75	0
12
1c

Cell Formulas
Range	Formula
F10:I10	F10	=(F9="yes")*(F8*$E10+(1-SUMPRODUCT(--($F$9:$I$9="yes"),$F$8:$I$8))*$E10/COUNTIF($F$9:$I$9,"yes"))
J10:J11	J10	=E10-SUM(F10:I10)
F11:I11	F11	=(F$9="yes")*F$8/SUMPRODUCT(--($F$9:$I$9="yes"),$F$8:$I$8)*$E$11