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]

Try this (adjust the lookup ranges to fit your actual data).

Book1
	A	B	C	D	E	F
1	Name	Job Title	Location	Total Hours worked	Cohort Ranking
2	A	Analyst	London	10	1st quartile
3	B	Associate	Milan	20	4th quartile
4	C	Analyst	London	40	4th quartile
5
6
7	Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
8	Analyst	London	10	20	30	40
9	Associate	Milan	5	10	15	20
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=IFERROR(XLOOKUP(D2,FILTER($C$8:$F$9,($A$8:$A$9=B2)*($B$8:$B$9=C2),""),$C$7:$F$7),"")

 

[bankerturnedconsultant]

Try this (adjust the lookup ranges to fit your actual data).

Book1
	A	B	C	D	E	F
1	Name	Job Title	Location	Total Hours worked	Cohort Ranking
2	A	Analyst	London	10	1st quartile
3	B	Associate	Milan	20	4th quartile
4	C	Analyst	London	40	4th quartile
5
6
7	Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
8	Analyst	London	10	20	30	40
9	Associate	Milan	5	10	15	20
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=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

 

[kevin9999]

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
	A	B	C	D	E	F
1	Name	Job Title	Location	Total Hours worked	Cohort Ranking
2	A	Analyst	London	10	1st quartile
3	B	Associate	Milan	20	4th quartile
4	C	Analyst	London	40	4th quartile
5	D	Analyst	Amsterdam	15	2nd quartile
6	E	Analyst	Amsterdam	10	1st quartile
7	F	Analyst	Amsterdam	25	4th quartile
8	G	Analyst	Amsterdam	20	3rd quartile
9
10
11
12	Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
13	Analyst	London	10	20	30	40
14	Associate	Milan	5	10	15	20
15	Analyst	Amsterdam	10	15	20	25
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(XLOOKUP(D2,FILTER($C$13:$F$15,($A$13:$A$15=B2)*($B$13:$B$15=C2),""),$C$12:$F$12),"")

 

[bankerturnedconsultant]

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
	A	B	C	D	E	F
1	Name	Job Title	Location	Total Hours worked	Cohort Ranking
2	A	Analyst	London	10	1st quartile
3	B	Associate	Milan	20	4th quartile
4	C	Analyst	London	40	4th quartile
5	D	Analyst	Amsterdam	15	2nd quartile
6	E	Analyst	Amsterdam	10	1st quartile
7	F	Analyst	Amsterdam	25	4th quartile
8	G	Analyst	Amsterdam	20	3rd quartile
9
10
11
12	Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
13	Analyst	London	10	20	30	40
14	Associate	Milan	5	10	15	20
15	Analyst	Amsterdam	10	15	20	25
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=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

 

[bankerturnedconsultant]

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
	A	B	C	D	E	F
1	Name	Job Title	Location	Total Hours worked	Cohort Ranking
2	A	Analyst	London	10	1st quartile
3	B	Associate	Milan	20	4th quartile
4	C	Analyst	London	40	4th quartile
5	D	Analyst	Amsterdam	15	2nd quartile
6	E	Analyst	Amsterdam	10	1st quartile
7	F	Analyst	Amsterdam	25	4th quartile
8	G	Analyst	Amsterdam	20	3rd quartile
9
10
11
12	Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
13	Analyst	London	10	20	30	40
14	Associate	Milan	5	10	15	20
15	Analyst	Amsterdam	10	15	20	25
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=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
	A	B	C	D	E	F
1	Name	Job Title	Location	Total Hours worked	Cohort Ranking
2	A	Analyst	London	10	1st quartile
3	B	Associate	Milan	20	4th quartile
4	C	Analyst	London	40	4th quartile
5	D	Analyst	Amsterdam	15	2nd quartile
6	E	Analyst	Amsterdam	10	1st quartile
7	F	Analyst	Amsterdam	25	4th quartile
8	G	Analyst	Amsterdam	20	3rd quartile
9
10
11
12	Job Title	Location	1st quartile	2nd quartile	3rd quartile	4th quartile
13	Analyst	London	10	20	30	40
14	Associate	Milan	5	10	15	20
15	Analyst	Amsterdam	10	15	20	25
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(XLOOKUP(D2,FILTER($C$13:$F$15,($A$13:$A$15=B2)*($B$13:$B$15=C2),""),$C$12:$F$12),"")

 

[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!

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

 

[kevin9999]

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

How about sharing your file via Google Drive, Dropbox or something similar?

 

[bankerturnedconsultant]

mock.xlsx

Output Q1,Q2,Q3,Q4 HR ID,companyABC Email,First Name,Office,Core/Expert Track,CT Cohort,ECT Cohort,Contribution (Y/N),Role 1 Hours,Role 2 Hours,Role 3 Hours,Role 1 Hours,Role 2 Hours,Role 3 Hours,Role 1 Hours,Role 2 Hours,Role 3 Hours,Role 1 Hours,Role 2 Hours,Role 3 Hours test1@companyABC.com,...

docs.google.com

 

[bankerturnedconsultant]

mock.xlsx

Output Q1,Q2,Q3,Q4 HR ID,companyABC Email,First Name,Office,Core/Expert Track,CT Cohort,ECT Cohort,Contribution (Y/N),Role 1 Hours,Role 2 Hours,Role 3 Hours,Role 1 Hours,Role 2 Hours,Role 3 Hours,Role 1 Hours,Role 2 Hours,Role 3 Hours,Role 1 Hours,Role 2 Hours,Role 3 Hours test1@companyABC.com,...

docs.google.com

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