How can I rank / find - Top 10 staff without pivot table?

[MXMaster]

Hi all,
Whilst I can do basic excel code (Look-up etc) I'm struggling for a formula or method to be able to surface what I need from a data set that has been generated from a pivot table originally.

Goal: I want to have an area in the same Sheet that can pull the top 10 ranked staff from a list of data based on some criterial values.

The What: I want to be able to return the top staff who have had over 50 transactions & have achieved over 45% marketing capture.

The Why: Under 50 transactions the marketing % is too volatile.
I want to call out the great performances of those who have not only done an amazing job on transactions, but also converted the most in marketing % terms.
The more transactions you have done and the higher the marketing % the closer to number 1 spot you go!

I'm not sure on the best method to approach this, nor how to create the Look-Up so not only will it rank staff 1 to 10, but give me their region number, first name, last name, transactions, and marketing % all returned in the table.

Any help on approach - It needs to be versatile so when I update the master data, the top 10 also updates.

Thanks

Sample below... (I have covered surnames to protect)

 

[mart37]

Why are there in your top 10 items with lower then 45% of marketing rate?

 

[mart37]

A rank with two conditions needed a measure between the conditions. The measure can by a weight. So you can give a condition more value.
You can use different weights. Example the highest score gets 100 points etc.
You are comparing numbers with percentages. Maybe you can condert them to one type.

 

[Alex Blakenburg]

You have the luxury of having Microsoft 365, so you can use the Dyamic array Filter & Sort functions.
It got a bit complicated so I added in the LET function to help keep track,

The below might work for you. You can change the number to include (Top) where I have the input cell at the tope currently 10.
I am not using the Check Column for anything other than a visual check so you can delete it.
My table name is: ConversionRate
If you use cut and paste you should be able to move the results section to another sheet.

20210403 Rank by 2 Criteria v02.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Region	First Name	Surname	Total Transactions	Marketing conversion Rate	Check Ext		Top Conversion, Names & Region
2	359	Michael		36	42%	15.12		10	Top Trans * Conversion
3	359	Megan		46	43%	19.78
4	359	Dominic		32	53%	16.96
5	359	Paul		76	47%	35.72		Region	First Name	Surname	Total Transactions	Marketing conversion Rate	Extension
6	359	Maxine		27	44%	11.88		373	Daniel	0	162	59%	96
7	359	James		32	75%	24		359	Samuel	0	85	60%	51
8	359	Samuel		85	60%	51		444	Samuel	0	85	60%	51
9	359	Conor		54	46%	24.84		888	Samuel	0	85	60%	51
10	0					0		373	Thomas	0	92	50%	46
11	373	Steven		38	39%	14.82		359	Paul	0	76	47%	36
12	373	James		45	47%	21.15		444	Paul	0	76	47%	36
13	373	Stefan		21	38%	7.98		888	Paul	0	76	47%	36
14	373	Callum		54	48%	25.92		373	Callum	0	54	48%	26
15	373	Samuel		117	24%	28.08		359	Conor	0	54	46%	25
16	373	Thomas		92	50%	46
17	373	Jay		37	32%	11.84
18	373	Anieszka		33	64%	21.12
19	373	Daniel		162	59%	95.58
20	444	Megan		46	43%	19.78
21	444	Dominic		32	53%	16.96
22	444	Paul		76	47%	35.72
23	444	Maxine		27	44%	11.88
24	444	James		32	75%	24
25	444	Samuel		85	60%	51
26	888	Megan		46	43%	19.78
27	888	Dominic		32	53%	16.96
28	888	Paul		76	47%	35.72
29	888	Maxine		27	44%	11.88
30	888	James		32	75%	24
31	888	Samuel		85	60%	51
Data FINAL

Cell Formulas
Range	Formula
H6:M15	H6	=LET(EligibleVOLUME,(ConversionRate[Total Transactions]>50), EligiblePERCENT,(ConversionRate[Marketing conversion Rate]>45%), VOLxPERCENT,(ConversionRate[Total Transactions]*ConversionRate[Marketing conversion Rate]), LargeTHRESHOLD,LARGE((EligibleVOLUME*EligiblePERCENT)*VOLxPERCENT,$H$2), SORT(FILTER(ConversionRate,EligibleVOLUME*EligiblePERCENT*(VOLxPERCENT>=LargeTHRESHOLD)),6,-1))
F2:F31	F2	=[@[Total Transactions]]*[@[Marketing conversion Rate]]
Dynamic array formulas.

 

[MXMaster]

You have the luxury of having Microsoft 365, so you can use the Dyamic array Filter & Sort functions.
It got a bit complicated so I added in the LET function to help keep track,

The below might work for you. You can change the number to include (Top) where I have the input cell at the tope currently 10.
I am not using the Check Column for anything other than a visual check so you can delete it.
My table name is: ConversionRate
If you use cut and paste you should be able to move the results section to another sheet.

20210403 Rank by 2 Criteria v02.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Region	First Name	Surname	Total Transactions	Marketing conversion Rate	Check Ext		Top Conversion, Names & Region
2	359	Michael		36	42%	15.12		10	Top Trans * Conversion
3	359	Megan		46	43%	19.78
4	359	Dominic		32	53%	16.96
5	359	Paul		76	47%	35.72		Region	First Name	Surname	Total Transactions	Marketing conversion Rate	Extension
6	359	Maxine		27	44%	11.88		373	Daniel	0	162	59%	96
7	359	James		32	75%	24		359	Samuel	0	85	60%	51
8	359	Samuel		85	60%	51		444	Samuel	0	85	60%	51
9	359	Conor		54	46%	24.84		888	Samuel	0	85	60%	51
10	0					0		373	Thomas	0	92	50%	46
11	373	Steven		38	39%	14.82		359	Paul	0	76	47%	36
12	373	James		45	47%	21.15		444	Paul	0	76	47%	36
13	373	Stefan		21	38%	7.98		888	Paul	0	76	47%	36
14	373	Callum		54	48%	25.92		373	Callum	0	54	48%	26
15	373	Samuel		117	24%	28.08		359	Conor	0	54	46%	25
16	373	Thomas		92	50%	46
17	373	Jay		37	32%	11.84
18	373	Anieszka		33	64%	21.12
19	373	Daniel		162	59%	95.58
20	444	Megan		46	43%	19.78
21	444	Dominic		32	53%	16.96
22	444	Paul		76	47%	35.72
23	444	Maxine		27	44%	11.88
24	444	James		32	75%	24
25	444	Samuel		85	60%	51
26	888	Megan		46	43%	19.78
27	888	Dominic		32	53%	16.96
28	888	Paul		76	47%	35.72
29	888	Maxine		27	44%	11.88
30	888	James		32	75%	24
31	888	Samuel		85	60%	51
Data FINAL

Cell Formulas
Range	Formula
H6:M15	H6	=LET(EligibleVOLUME,(ConversionRate[Total Transactions]>50), EligiblePERCENT,(ConversionRate[Marketing conversion Rate]>45%), VOLxPERCENT,(ConversionRate[Total Transactions]*ConversionRate[Marketing conversion Rate]), LargeTHRESHOLD,LARGE((EligibleVOLUME*EligiblePERCENT)*VOLxPERCENT,$H$2), SORT(FILTER(ConversionRate,EligibleVOLUME*EligiblePERCENT*(VOLxPERCENT>=LargeTHRESHOLD)),6,-1))
F2:F31	F2	=[@[Total Transactions]]*[@[Marketing conversion Rate]]
Dynamic array formulas.

Brilliant - thank you for your support

 

[Alex Blakenburg]

Happy to help. Thanks for the feedback.

PS: oops I may have used the extension column for the sort