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]

Welcome to the MrExcel board!

For the future, please consider this:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Is this what you want?

23 01 30.xlsm
	A	B	C	D
1			Diff	Who
2	Female	42437	180	Female
3	Male	42257
Compare

Cell Formulas
Range	Formula
C2	C2	=ABS(B2-B3)
D2	D2	=IF(B2>B3,A2,IF(B3>B2,A3,"Equal"))

 

[Cenin]

Thanks for the reply, sir.
Could you please take a look at this? In PC 47 I have 1 male and 5 female employees and I have averaged on gender on column D, and I need to see the differences of Males Vs Females in each cell and have to see who got higher in column F.

France Exec Gender Gap Analysis.xlsx
	A	B	C	D	E	F
1	Count	Gender	PC	Average on PC, Male & Female.	Difference	who
2	5	F	47	42437
3	5	F	47	42437
4	5	F	47	42437
5	1	M	47	42257
6	5	F	47	42437
7	5	F	47	42437
8	5	M	49	45277
9	1	F	49	45495
10	5	M	49	45277
11	5	M	49	45277
12	5	M	49	45277
13	5	M	49	45277
calc

Cell Formulas
Range	Formula
A2:A13	A2	=COUNTIFS($C$2:$C$13,C2,$B$2:$B$13,B2)

 

[Peter_SSs]

Just to be clear, could you please manually fill in the expected results and post the XL2BB sheet again?

 

[Cenin]

PFA

France Exec Gender Gap Analysis.xlsx
	A	B	C	D	E	F
1	Count	Gender	PC	Average on PC, Male & Female.	Difference	who
2	5	F	47	42437	180	F
3	5	F	47	42437	180	F
4	5	F	47	42437	180	F
5	1	M	47	42257	-180	F
6	5	F	47	42437	180	F
7	5	F	47	42437	180	F
8	5	M	49	45277	-218	M
9	1	F	49	45495	218	M
10	5	M	49	45277	-218	M
11	5	M	49	45277	-218	M
12	5	M	49	45277	-218	M
13	5	M	49	45277	-218	M
calc

Cell Formulas
Range	Formula
A2:A13	A2	=COUNTIFS($C$2:$C$13,C2,$B$2:$B$13,B2)

 

[Peter_SSs]

Thanks for the extra info.
Why are F8:F13 = M when cell D9 (Female) is greater than the Male values for PC 49?

 

[Cenin]

I'm extremely sorry sir. It was my mistake, yes you're right, it should be Female as well

 

[Peter_SSs]

What about this then?

BTW, is it possible that M & F values for a PC could be exactly equal? If so, what should happen in column F?

23 01 30.xlsm
	A	B	C	D	E	F
1	Count	Gender	PC	Average on PC, Male & Female.	Difference	who
2	5	F	47	42436.93	180.43	F
3	5	F	47	42436.93	180.43	F
4	5	F	47	42436.93	180.43	F
5	1	M	47	42256.5	-180.43	F
6	5	F	47	42436.93	180.43	F
7	5	F	47	42436.93	180.43	F
8	5	M	49	45276.99647	-218.2747794	F
9	1	F	49	45495.27125	218.2747794	F
10	5	M	49	45276.99647	-218.2747794	F
11	5	M	49	45276.99647	-218.2747794	F
12	5	M	49	45276.99647	-218.2747794	F
13	5	M	49	45276.99647	-218.2747794	F
Compare (2)

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

 

[Cenin]

Thanks, sir, and thanks for notifying that. Is it possible to add a condition to that, if both are equal then the value should be "EQUAL"?

 

[Peter_SSs]

Is it possible to add a condition to that, if both are equal then the value should be "EQUAL"?

Sure

23 01 30.xlsm
	B	C	D	E	F
1	Gender	PC	Average on PC, Male & Female.	Difference	who
2	F	47	42436.93	180.43	F
3	F	47	42436.93	180.43	F
4	F	47	42436.93	180.43	F
5	M	47	42256.5	-180.43	F
6	F	47	42436.93	180.43	F
7	F	47	42436.93	180.43	F
8	M	49	45276.99647	-218.2747794	F
9	F	49	45495.27125	218.2747794	F
10	M	49	45276.99647	-218.2747794	F
11	M	49	45276.99647	-218.2747794	F
12	M	49	45276.99647	-218.2747794	F
13	M	49	45276.99647	-218.2747794	F
14	F	20	1234	0	EQUAL
15	F	20	1234	0	EQUAL
16	M	20	1234	0	EQUAL
Compare (2)

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