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

Joined
Apr 6, 2023
Messages
9
Office Version
  1. 2021
Platform
  1. Windows
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:

NameJob TitleLocationTotal Hours workedCohort Ranking
AAnalystLondon10
BAssociate Milan20
CAnalystLondon40

Second dataset:

Job Title Location 1st quartile2nd quartile 3rd quartile 4th quartile
AnalystLondon10203040
Associate Milan5101520

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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Try this (adjust the lookup ranges to fit your actual data).

Book1
ABCDEF
1NameJob TitleLocationTotal Hours workedCohort Ranking
2AAnalystLondon101st quartile
3BAssociateMilan204th quartile
4CAnalystLondon404th quartile
5
6
7Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
8AnalystLondon10203040
9AssociateMilan5101520
Sheet1
Cell Formulas
RangeFormula
E2:E4E2=IFERROR(XLOOKUP(D2,FILTER($C$8:$F$9,($A$8:$A$9=B2)*($B$8:$B$9=C2),""),$C$7:$F$7),"")
 
Upvote 0
Try this (adjust the lookup ranges to fit your actual data).

Book1
ABCDEF
1NameJob TitleLocationTotal Hours workedCohort Ranking
2AAnalystLondon101st quartile
3BAssociateMilan204th quartile
4CAnalystLondon404th quartile
5
6
7Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
8AnalystLondon10203040
9AssociateMilan5101520
Sheet1
Cell Formulas
RangeFormula
E2:E4E2=IFERROR(XLOOKUP(D2,FILTER($C$8:$F$9,($A$8:$A$9=B2)*($B$8:$B$9=C2),""),$C$7:$F$7),"")
Thank you for this. However my formula is not working because I have several Job Titles and only 3 locations which keep repeating. So there could be 3 analyst positions in London, Milan and Amsterdam and so on. Not sure why it's not working :(
 
Upvote 0
my formula is not working
This tells me almost nothing. Is it returning the wrong result, or no result? Are you getting an error message in the cells? Is Excel crashing? It would be better if you could provide a more representative sample of your actual data using the XL2BB add in. In the meantime, I added some additional rows to post #2 and it still seems to be working for me. (I trust you are expanding the ranges in the formula to accommodate your extra rows?)

function.xlsm
ABCDEF
1NameJob TitleLocationTotal Hours workedCohort Ranking
2AAnalystLondon101st quartile
3BAssociateMilan204th quartile
4CAnalystLondon404th quartile
5DAnalystAmsterdam152nd quartile
6EAnalystAmsterdam101st quartile
7FAnalystAmsterdam254th quartile
8GAnalystAmsterdam203rd quartile
9
10
11
12Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
13AnalystLondon10203040
14AssociateMilan5101520
15AnalystAmsterdam10152025
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IFERROR(XLOOKUP(D2,FILTER($C$13:$F$15,($A$13:$A$15=B2)*($B$13:$B$15=C2),""),$C$12:$F$12),"")
 
Upvote 0
This tells me almost nothing. Is it returning the wrong result, or no result? Are you getting an error message in the cells? Is Excel crashing? It would be better if you could provide a more representative sample of your actual data using the XL2BB add in. In the meantime, I added some additional rows to post #2 and it still seems to be working for me. (I trust you are expanding the ranges in the formula to accommodate your extra rows?)

function.xlsm
ABCDEF
1NameJob TitleLocationTotal Hours workedCohort Ranking
2AAnalystLondon101st quartile
3BAssociateMilan204th quartile
4CAnalystLondon404th quartile
5DAnalystAmsterdam152nd quartile
6EAnalystAmsterdam101st quartile
7FAnalystAmsterdam254th quartile
8GAnalystAmsterdam203rd quartile
9
10
11
12Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
13AnalystLondon10203040
14AssociateMilan5101520
15AnalystAmsterdam10152025
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IFERROR(XLOOKUP(D2,FILTER($C$13:$F$15,($A$13:$A$15=B2)*($B$13:$B$15=C2),""),$C$12:$F$12),"")
Basically I’m still getting empty cells. There’s no error just getting empty cells. I will send you a sample sheet tomorrow morning. Thank you once again! I appreciate it
 
Upvote 0
This tells me almost nothing. Is it returning the wrong result, or no result? Are you getting an error message in the cells? Is Excel crashing? It would be better if you could provide a more representative sample of your actual data using the XL2BB add in. In the meantime, I added some additional rows to post #2 and it still seems to be working for me. (I trust you are expanding the ranges in the formula to accommodate your extra rows?)

function.xlsm
ABCDEF
1NameJob TitleLocationTotal Hours workedCohort Ranking
2AAnalystLondon101st quartile
3BAssociateMilan204th quartile
4CAnalystLondon404th quartile
5DAnalystAmsterdam152nd quartile
6EAnalystAmsterdam101st quartile
7FAnalystAmsterdam254th quartile
8GAnalystAmsterdam203rd quartile
9
10
11
12Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
13AnalystLondon10203040
14AssociateMilan5101520
15AnalystAmsterdam10152025
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IFERROR(XLOOKUP(D2,FILTER($C$13:$F$15,($A$13:$A$15=B2)*($B$13:$B$15=C2),""),$C$12:$F$12),"")
This tells me almost nothing. Is it returning the wrong result, or no result? Are you getting an error message in the cells? Is Excel crashing? It would be better if you could provide a more representative sample of your actual data using the XL2BB add in. In the meantime, I added some additional rows to post #2 and it still seems to be working for me. (I trust you are expanding the ranges in the formula to accommodate your extra rows?)

function.xlsm
ABCDEF
1NameJob TitleLocationTotal Hours workedCohort Ranking
2AAnalystLondon101st quartile
3BAssociateMilan204th quartile
4CAnalystLondon404th quartile
5DAnalystAmsterdam152nd quartile
6EAnalystAmsterdam101st quartile
7FAnalystAmsterdam254th quartile
8GAnalystAmsterdam203rd quartile
9
10
11
12Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
13AnalystLondon10203040
14AssociateMilan5101520
15AnalystAmsterdam10152025
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IFERROR(XLOOKUP(D2,FILTER($C$13:$F$15,($A$13:$A$15=B2)*($B$13:$B$15=C2),""),$C$12:$F$12),"")
 
Upvote 0
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:

NameJob TitleLocationTotal Hours workedCohort Ranking
AAnalystLondon10
BAssociateMilan20
CAnalystLondon40

Second dataset:

Job TitleLocation1st quartile2nd quartile3rd quartile4th quartile
AnalystLondon10203040
AssociateMilan5101520

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!
Basically I’m still getting empty cells. There’s no error just getting empty cells. I will send you a sample sheet tomorrow morning. Thank you once again! I appreciate it
Hi Kevin, I'm unable to attach the sheet here or download the Excel add-in. Is there a way I can email this to you please?

Best,
Ritu
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top