Working with Quartiles

[AbleAndy_UK]

Good Morning all. I am trying to find out if it is possible to work with data in quartiles. So, if I had a list of salary data attached to Male and Female employees I know I can find out what each of the Quartiles are, but is there a way to say in the top quartile there are 55% Male and 45% Female?
Thank you in advance

 

[barry houdini]

Hello AbleAndy_UK, welcome to MrExcel

Say you have salaries in B2:B20 and C2:C20 shows "F" or "M" then you could get the % of Females in the top quartile with this array formula

=AVERAGE(IF(B2:B20>=QUARTILE(B2:B20,3),IF(C2:C20="F",1,0)))

confirmed with CTRL+SHIFT+ENTER

 

[AbleAndy_UK]

Barry, thank you for your response that was very quick It works great, can I confirm that to do the same for each quartile I amend the IF(C2:C20="F",1,0) to 2,0 / 3, 0 / 4,0 etc?
Thanks again

 

[AbleAndy_UK]

Barry, I found the way to change the quartile (QUARTILE(B2:B20,3) but the numbers I get don't match what I would expect:

Thanks, Andy.

 

[AbleAndy_UK]

[/URL][/IMG]

 

[barry houdini]

Sorry, it's easier for the top one, for the next quartile down you need add an "IF" so that it's >=2 but <3, i.e.

=AVERAGE(IF(B2:B20>=QUARTILE(B2:B20,2),IF(B2:B20< QUARTILE(B2:B20,2),IF(C2:C20="F",1,0))))
<quartile(b2:b20,3),if(c2:c20="f",1,0))))

You can do the same for >= 1 and < 2

and for the bottom quartile it can be simply < 1</quartile(b2:b20,3),if(c2:c20="f",1,0))))