Condition required for difference

[Cenin]

Hi, From this data, I would like to get the difference between columns 42437-42257 in accordance with the Gender Male or Female and show whether it is male or female is higher.

 

[Cenin]

Thanks for the help sir

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.

 

[Cenin]

Could you please take a look at this? I am getting error on this..
is it showing an error because it has only one line on the PC for 48, or it has only one gender in the PCs ?

Book1
	B	C	D	E	F
1	Gender	PC	Average on PC, Male & Female.	DIFF IN EUROS	PC HIGHEST GENDER
2	M	46	40560	#CALC!	#CALC!
3	M	58	143171	#CALC!	#CALC!
4	M	58	143171	#CALC!	#CALC!
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=D2-INDEX(FILTER(D$2:D$4,(B$2:B$4=IF(B2="F","M","F"))*(C$2:C$4=C2)),1)
F2:F4	F2	=IF(E2>0,B2,IF(E2=0,"EQUAL",IF(B2="F","M","F")))

 

[Peter_SSs]

is it showing an error because it has only one line on the PC for 48, or it has only one gender in the PCs ?

In that circumstance, what result do you want to appear in column E since there is no way to calculate an actual 'difference'?

 

[Cenin]

value 0 sir. The genders (if it is M&M, M&F, only M, only F, in a PC, and their values do not have any difference, the column E should produce the result as a 0 value

 

[Peter_SSs]

value 0 sir.

Hmm, that seems a bit odd. IF M =100 and F = 100 then clearly the difference is 0 but if M = 100 and there are no F values, putting 0 seems to me to imply there is no difference between M & F

Anyway, if you do want 0 you can have it, but does that also mean that you want "EQUAL" in the next column?

 

[Cenin]

You're right, but in this case, it would be better to have it as 0 and EQUAL in next column

 

[Peter_SSs]

In that case, try this

23 01 30.xlsm
	A	B	C	D	E	F
1	Count	Gender	PC	Average on PC, Male & Female.	Difference	who
2	2	F	60	42436.93	0	EQUAL
3	2	F	60	42436.93	0	EQUAL
4	3	F	47	42436.93	180.43	F
5	1	M	47	42256.5	-180.43	F
6	3	F	47	42436.93	180.43	F
7	3	F	47	42436.93	180.43	F
Compare (2)

Cell Formulas
Range	Formula
E2:E7	E2	=D2-INDEX(FILTER(D$2:D$16,(B$2:B$16=IF(B2="F","M","F"))*(C$2:C$16=C2),D2),1)
F2:F7	F2	=IF(E2>0,B2,IF(E2=0,"EQUAL",IF(B2="F","M","F")))
A2:A7	A2	=COUNTIFS($C$2:$C$13,C2,$B$2:$B$13,B2)

 

[Cenin]

Thank you, sir. It works as expected

 

[Cenin]

Hi Sire, one more query
Colum D represents the difference between males and females on PC, and I would like to get the percentage difference of that difference from the highest value of the PC. Could you please help?

France Exec Gender Gap Analysis.xlsx
	A	B	C	D	E
1	GENDER	PC	Average on PC, Male & Female.	DIFF IN EUROS	Expected results
2	M	59	137021	18500	13,50%
3	M	59	137021	18500	13,50%
4	F	59	118521	-18500	-13,50%
5	M	59	137021	18500	13,50%
6	M	59	137021	18500	13,50%
7	M	59	137021	18500	13,50%
8	M	59	137021	18500	13,50%
9	M	60	139333	15001	10,77%
10	F	60	124333	-15001	-10,77%
11	F	60	124333	-15001	-10,77%
12	M	60	139333	15001	10,77%
13	F	60	124333	-15001	-10,77%
14	M	60	139333	15001	10,77%
Sheet7