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.

 

[Peter_SSs]

One more thing required

It would be better if you could include all the relevant circumstances & expected results when you first ask the question.

Should we have an example with only "M" rows to complete all possibilities?

 

[Cenin]

Hi Sir, I can understand. The requirement changes frequently that's why i am unable to give all the circumstances at once. please excuse

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	Expected
2	M	57	94997	94836	1,70%	1,53%
3	M	57	94997	94836	1,70%	1,53%
4	M	57	94997	94836	1,70%	1,53%
5	M	57	94997	94836	1,70%	1,53%
6	M	57	94997	94836	1,70%	1,53%
7	F	57	94997	96447	1,70%	1,53%
8	M	57	94997	94836	1,70%	1,53%
9	M	57	94997	94836	1,70%	1,53%
10	M	57	94997	94836	1,70%	1,53%
11	M	57	94997	94836	1,70%	1,53%
12	M	58	143171	143171	0,00%	0,00%
13	M	58	143171	143171	0,00%	0,00%
14	F	59	134378	118521	-13,50%	-11,80%
15	M	59	134378	137021	-13,50%	-11,80%
16	M	59	134378	137021	-13,50%	-11,80%
17	M	59	134378	137021	-13,50%	-11,80%
18	M	59	134378	137021	-13,50%	-11,80%
19	M	59	134378	137021	-13,50%	-11,80%
20	M	59	134378	137021	-13,50%	-11,80%
21	F	60	143173	143173	0,00%	0,00%
22	F	60	143173	143173	0,00%	0,00%
France Gender Gap bw M&F 1

 

[Peter_SSs]

The requirement changes frequently that's why i am unable to give all the circumstances at once.

Surely the possibilities are always that a PC group might contain ...

1. Both M and F entries
2. Only F entries
3. Only M entries

.. so any sample data should also contain each of those possibilities.

Cenin.xlsm
	A	B	C	D	E	F
1	Gender	PC	Total Average on PC.	Average on PC, Male & Female.	Expected	Expected
2	M	57	94997	94836	1.70%	1.53%
3	M	57	94997	94836	1.70%	1.53%
4	M	57	94997	94836	1.70%	1.53%
5	M	57	94997	94836	1.70%	1.53%
6	M	57	94997	94836	1.70%	1.53%
7	F	57	94997	96447	1.70%	1.53%
8	M	57	94997	94836	1.70%	1.53%
9	M	57	94997	94836	1.70%	1.53%
10	M	57	94997	94836	1.70%	1.53%
11	M	57	94997	94836	1.70%	1.53%
12	M	58	143171	143171	0.00%	0.00%
13	M	58	143171	143171	0.00%	0.00%
14	F	59	134378	118521	-13.50%	-11.80%
15	M	59	134378	137021	-13.50%	-11.80%
16	M	59	134378	137021	-13.50%	-11.80%
17	M	59	134378	137021	-13.50%	-11.80%
18	M	59	134378	137021	-13.50%	-11.80%
19	M	59	134378	137021	-13.50%	-11.80%
20	M	59	134378	137021	-13.50%	-11.80%
21	F	60	143173	143173	0.00%	0.00%
22	F	60	143173	143173	0.00%	0.00%
Sheet4

Cell Formulas
Range	Formula
E2:E22	E2	=IF(ROWS(UNIQUE(FILTER(A$2:A$22,B$2:B$22=B2)))=1,0,MAXIFS(D$2:D$22,B$2:B$22,B2,A$2:A$22,"F")/MAXIFS(D$2:D$22,B$2:B$22,B2,A$2:A$22,"M")-1)
F2:F22	F2	=IF(E2=0,0,MAXIFS(D$2:D$22,B$2:B$22,B2,A$2:A$22,"F")/C2-1)

 

[Cenin]

Hi Sir, the formula E2 do not work for me

 

[Cenin]

Sorry; it works; Thanks for the help

 

[Cenin]

Hi Sir
Could you please help me get the expected averages which is the FEMALE + MALE /2 for each country, example (5.80%+0.14%/2)

Eviosys Gender GAP - Copie.xlsx
	A	B	C	D	E
1	Gender	Country/Region	PC	FEMALE AVG ON MALE	Expected result
2	F	Belgium	49	5,80%	2,97%
3	F	Belgium	52	0,14%	2,97%
4	F	Belgium	52	0,14%	2,97%
5	M	Belgium	49	5,80%	2,97%
6	M	Belgium	52	0,14%	2,97%
7	F	UK	49	5,00%	5,50%
8	F	UK	52	5,00%	5,50%
9	F	UK	52	5,00%	5,50%
10	M	UK	49	6,00%	5,50%
11	M	UK	52	6,00%	5,50%
Sheet3

 

[Peter_SSs]

Surely the possibilities are always that a PC Country group might contain ...

1. Both M and F entries
2. Only F entries
3. Only M entries

.. so any sample data should also contain each of those possibilities.