Percentiles and dynamic data help

[EMY245]

Hello,

I have a large set of data. I am trying to calculate the median (and other percentiles) of a range of data conditional upon a number of factors.

So, i have:

A range of salaries - this is what i want the median of but only when certain conditions are met.

Conditions:

- Location
- Gender
- Job type
- Job role
etc


I know that i cannot do this by a pivot table but is there another dynamic way of getting this information so that i can get the percentiles of each group - i.e. median salaries for London-based male employees working as accountants. There are so many variations that i cannot do this manually!

Appreciate any help!

Thanks,
Emily

 

[ndsutherland]

This should work

=MEDIAN(IF(B2:B27=L13,IF(C2:C27=L14,IF(D2:D27=L15,IF(E2:E27=L16,F2:F27))))) confirmed with CTRL+SHIFT+ENTER (not just ENTER)

Here's a sample

 

[EMY245]

Thank you! Yes that's perfect