Help needed with Finding which quartile does an employee lie in.

[bankerturnedconsultant]

Hi there, I need some help! I have two datasets:

First dataset and I need help with calculating the last column i.e. the cohort ranking:

Name	Job Title	Location	Total Hours worked	Cohort Ranking
A	Analyst	London	10
B	Associate	Milan	20
C	Analyst	London	40

Second dataset:

Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
Analyst	London	10	20	30	40
Associate	Milan	5	10	15	20

My question:
In the first table last column (Cohort Ranking), how do I calculate that for Employee A, based on the job title, location and total hours worked, they fall in X quartile?

So for example for Employee A it should say 1st quartile, Employee B should say 4th quartile and so on.

Can you please help me? Thank you!

 

[kevin9999]

let me know if this works? The yellow column (V) is the one that needs the correct formula

Thank you - that worked. Leave it with me.

 

[kevin9999]

OK, couple of slight errors in your formula.

Change:

=IFERROR(XLOOKUP(U4, FILTER('Hours Analysis by Cohort'!$E$4:$J$39, ('Hours Analysis by Cohort'!$D$4:$D$39=Output!F4)*('Hours Analysis by Cohort'!$B$4:$B39=Output!D4)*('Hours Analysis by Cohort'!$C$4:$C$39=Output!E4), ""), 'Hours Analysis by Cohort'!$G$3:$J$3), "")

To:

=IFERROR(XLOOKUP(U4, FILTER('Hours Analysis by Cohort'!$G$4:$J$39, ('Hours Analysis by Cohort'!$D$4:$D$39=Output!F4)*('Hours Analysis by Cohort'!$B$4:$B$39=Output!D4)*('Hours Analysis by Cohort'!$C$4:$C$39=Output!E4), ""), 'Hours Analysis by Cohort'!$G$3:$J$3), "")

and copy down column V. It doesn't pick up a quartile for every case, because not all cases match all conditions.

 

[bankerturnedconsultant]

OK, couple of slight errors in your formula.

Change:

=IFERROR(XLOOKUP(U4, FILTER('Hours Analysis by Cohort'!$E$4:$J$39, ('Hours Analysis by Cohort'!$D$4:$D$39=Output!F4)*('Hours Analysis by Cohort'!$B$4:$B39=Output!D4)*('Hours Analysis by Cohort'!$C$4:$C$39=Output!E4), ""), 'Hours Analysis by Cohort'!$G$3:$J$3), "")

To:

=IFERROR(XLOOKUP(U4, FILTER('Hours Analysis by Cohort'!$G$4:$J$39, ('Hours Analysis by Cohort'!$D$4:$D$39=Output!F4)*('Hours Analysis by Cohort'!$B$4:$B$39=Output!D4)*('Hours Analysis by Cohort'!$C$4:$C$39=Output!E4), ""), 'Hours Analysis by Cohort'!$G$3:$J$3), "")

and copy down column V. It doesn't pick up a quartile for every case, because not all cases match all conditions.

Thank you! This is amazing. I really appreciate it

 

[kevin9999]

Thank you! This is amazing. I really appreciate it

Happy to help, and thanks for the feedback