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]

It's okay sir, i got it, thanks

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

Cell Formulas
Range	Formula
F2:F14	F2	=D2/MAXIFS($C$2:$C$14,$B$2:$B$14,B2)

 

[Cenin]

Sir, Could you please help me with this ?

Chart Data table content need deletion​

 

[Peter_SSs]

No, not my field of expertise.

 

[Cenin]

Thanks for the response

 

[Cenin]

Hi Sir, need help. Column E refers to the Difference between Males and Females in a PC. I want to get the percentage of the difference compared with the absolute Female value in the PC.

France Exec Gender Gap Analysis.xlsx
	A	B		D	E	F	G	H
1	Gender	PC		Average on PC, Male & Female.	DIFF IN EUROS	PC HIGHEST GENDER	% DIFF B/W M&F on Highest AVG on PC	Expected result
2	F	59		118521	-18500	M	-13,50%	-15,61%
3	M	59		137021	18500	M	13,50%	15,61%
4	M	59		137021	18500	M	13,50%	15,61%
5	M	59		137021	18500	M	13,50%	15,61%
6	M	59		137021	18500	M	13,50%	15,61%
7	M	59		137021	18500	M	13,50%	15,61%
8	M	59		137021	18500	M	13,50%	15,61%
9	F	60		124333	-15001	M	-10,77%	-12,06%
10	F	60		124333	-15001	M	-10,77%	-12,06%
11	F	60		124333	-15001	M	-10,77%	-12,06%
12	M	60		139333	15001	M	10,77%	12,06%
13	M	60		139333	15001	M	10,77%	12,06%
14	M	60		139333	15001	M	10,77%	12,06%
Sheet5

Cell Formulas
Range	Formula
H2,H9	H2	=E2/D2
H3,H10	H3	=E3/D2
H4,H11	H4	=E4/D2
H5,H12	H5	=E5/D2
H6,H13	H6	=E6/D2
H7,H14	H7	=E7/D2
H8	H8	=E8/D2

 

[Peter_SSs]

Would this be acceptable?

Cenin.xlsm
	A	B	C	D	E	F	G	H
1	Gender	PC		Average on PC, Male & Female.	DIFF IN EUROS	PC HIGHEST GENDER	% DIFF B/W M&F on Highest AVG on PC	Expected result
2	F	59		118521	-18499.67167	M	-13.50%	-15.61%
3	M	59		137020.6717	18499.67167	M	13.50%	15.61%
4	M	59		137020.6717	18499.67167	M	13.50%	15.61%
5	M	59		137020.6717	18499.67167	M	13.50%	15.61%
6	M	59		137020.6717	18499.67167	M	13.50%	15.61%
7	M	59		137020.6717	18499.67167	M	13.50%	15.61%
8	M	59		137020.6717	18499.67167	M	13.50%	15.61%
9	F	60		124332.6933	-15000.65667	M	-10.77%	-12.06%
10	F	60		124332.6933	-15000.65667	M	-10.77%	-12.06%
11	F	60		124332.6933	-15000.65667	M	-10.77%	-12.06%
12	M	60		139333.35	15000.65667	M	10.77%	12.06%
13	M	60		139333.35	15000.65667	M	10.77%	12.06%
14	M	60		139333.35	15000.65667	M	10.77%	12.06%
15	F	61		100000	0	F	0.00%	0.00%
16	F	61		100000	0	F	0.00%	0.00%
17	M	62		99999	0	M	0.00%	#N/A
18	M	62		99999	0	M	0.00%	#N/A
Sheet1

Cell Formulas
Range	Formula
H2:H18	H2	=IFERROR(E2/MAXIFS(D$2:D$18,A$2:A$18,"F",B$2:B$18,B2),NA())

 

[Cenin]

Thanks sir

 

[Cenin]

Hi Sir, Need a help; From this data, I am expecting the highlighted data, which are E= D columns average of PC on Female/male-1 (67751/52586-1), and F column requires F= D columns average of PC On Female / Total average on PC (C) which is (67751/57641-1) in regards to PC and Gender. Thanks

France Exec Gender Gap Analysis.xlsx
	A	B	C	D	E	F
1	Gender	PC	Total Average on PC.	Average on PC, Male & Female.	Expected results	Expected results
2	F	52	57641	67751	28,84%	17,54%
3	F	52	57641	67751	28,84%	17,54%
4	F	52	57641	67751	28,84%	17,54%
5	F	52	57641	67751	28,84%	17,54%
6	F	52	57641	67751	28,84%	17,54%
7	F	52	57641	67751	28,84%	17,54%
8	F	52	57641	67751	28,84%	17,54%
9	M	52	57641	52586	28,84%	17,54%
10	M	52	57641	52586	28,84%	17,54%
11	M	52	57641	52586	28,84%	17,54%
12	M	52	57641	52586	28,84%	17,54%
13	M	52	57641	52586	28,84%	17,54%
14	M	52	57641	52586	28,84%	17,54%
15	M	52	57641	52586	28,84%	17,54%
16	M	52	57641	52586	28,84%	17,54%
17	M	52	57641	52586	28,84%	17,54%
18	M	52	57641	52586	28,84%	17,54%
19	M	52	57641	52586	28,84%	17,54%
20	M	52	57641	52586	28,84%	17,54%
21	M	52	57641	52586	28,84%	17,54%
22	M	52	57641	52586	28,84%	17,54%
23	M	53	57641	52586	28,84%	17,54%
24	M	53	57641	52586	28,84%	17,54%
25	M	53	57641	52586	28,84%	17,54%
26	F	53	57641	67751	28,84%	17,54%
27	F	53	57641	67751	28,84%	17,54%
Sheet16

 

[Peter_SSs]

Like this?

Cenin.xlsm
	A	B	C	D	E	F
1	Gender	PC	Total Average on PC.	Average on PC, Male & Female.	Expected results	Expected results
2	F	52	57641	67751	28.84%	17.54%
3	F	52	57641	67751	28.84%	17.54%
4	F	52	57641	67751	28.84%	17.54%
5	F	52	57641	67751	28.84%	17.54%
6	F	52	57641	67751	28.84%	17.54%
7	F	52	57641	67751	28.84%	17.54%
8	F	52	57641	67751	28.84%	17.54%
9	M	52	57641	52586	28.84%	17.54%
10	M	52	57641	52586	28.84%	17.54%
11	M	52	57641	52586	28.84%	17.54%
12	M	52	57641	52586	28.84%	17.54%
13	M	52	57641	52586	28.84%	17.54%
14	M	52	57641	52586	28.84%	17.54%
15	M	52	57641	52586	28.84%	17.54%
16	M	52	57641	52586	28.84%	17.54%
17	M	52	57641	52586	28.84%	17.54%
18	M	52	57641	52586	28.84%	17.54%
19	M	52	57641	52586	28.84%	17.54%
20	M	52	57641	52586	28.84%	17.54%
21	M	52	57641	52586	28.84%	17.54%
22	M	52	57641	52586	28.84%	17.54%
23	M	53	57641	52586	28.84%	17.54%
24	M	53	57641	52586	28.84%	17.54%
25	M	53	57641	52586	28.84%	17.54%
26	F	53	57641	67751	28.84%	17.54%
27	F	53	57641	67751	28.84%	17.54%
Sheet3

Cell Formulas
Range	Formula
E2:E27	E2	=MAXIFS(D$2:D$27,B$2:B$27,B2,A$2:A$27,"F")/MAXIFS(D$2:D$27,B$2:B$27,B2,A$2:A$27,"M")-1
F2:F27	F2	=MAXIFS(D$2:D$27,B$2:B$27,B2,A$2:A$27,"F")/C2-1

 

[Cenin]

Hi Sir, Thanks for the solution. One more thing required is that in regards of if it is male and male, Male and Female or Female and Female is a PC, if there is no difference between the Averages between them, I'd like to get the result as 0

France Exec Gender Gap Analysis.xlsx
	A	B	C	D	E	F
1	Gender	PC	Total Average on PC.	Average on PC, Male & Female.	Expected results	Expected results
2	F	53	67751	67751	0,00%	0,00%
3	F	53	67751	67751	0,00%	0,00%
4	F	52	67751	67751	0,00%	0,00%
5	M	52	67751	67751	0,00%	0,00%
Sheet2