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

MXMaster

New Member
Joined
Apr 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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)

1617435138501.png
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Why are there in your top 10 items with lower then 45% of marketing rate?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
601
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLM
1RegionFirst NameSurnameTotal TransactionsMarketing conversion RateCheck ExtTop Conversion, Names & Region
2359Michael3642%15.1210Top Trans * Conversion
3359Megan4643%19.78
4359Dominic3253%16.96
5359Paul7647%35.72RegionFirst NameSurnameTotal TransactionsMarketing conversion RateExtension
6359Maxine2744%11.88373Daniel016259%96
7359James3275%24359Samuel08560%51
8359Samuel8560%51444Samuel08560%51
9359Conor5446%24.84888Samuel08560%51
1000373Thomas09250%46
11373Steven3839%14.82359Paul07647%36
12373James4547%21.15444Paul07647%36
13373Stefan2138%7.98888Paul07647%36
14373Callum5448%25.92373Callum05448%26
15373Samuel11724%28.08359Conor05446%25
16373Thomas9250%46
17373Jay3732%11.84
18373Anieszka3364%21.12
19373Daniel16259%95.58
20444Megan4643%19.78
21444Dominic3253%16.96
22444Paul7647%35.72
23444Maxine2744%11.88
24444James3275%24
25444Samuel8560%51
26888Megan4643%19.78
27888Dominic3253%16.96
28888Paul7647%35.72
29888Maxine2744%11.88
30888James3275%24
31888Samuel8560%51
Data FINAL
Cell Formulas
RangeFormula
H6:M15H6=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:F31F2=[@[Total Transactions]]*[@[Marketing conversion Rate]]
Dynamic array formulas.
 
Last edited:
Solution

MXMaster

New Member
Joined
Apr 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLM
1RegionFirst NameSurnameTotal TransactionsMarketing conversion RateCheck ExtTop Conversion, Names & Region
2359Michael3642%15.1210Top Trans * Conversion
3359Megan4643%19.78
4359Dominic3253%16.96
5359Paul7647%35.72RegionFirst NameSurnameTotal TransactionsMarketing conversion RateExtension
6359Maxine2744%11.88373Daniel016259%96
7359James3275%24359Samuel08560%51
8359Samuel8560%51444Samuel08560%51
9359Conor5446%24.84888Samuel08560%51
1000373Thomas09250%46
11373Steven3839%14.82359Paul07647%36
12373James4547%21.15444Paul07647%36
13373Stefan2138%7.98888Paul07647%36
14373Callum5448%25.92373Callum05448%26
15373Samuel11724%28.08359Conor05446%25
16373Thomas9250%46
17373Jay3732%11.84
18373Anieszka3364%21.12
19373Daniel16259%95.58
20444Megan4643%19.78
21444Dominic3253%16.96
22444Paul7647%35.72
23444Maxine2744%11.88
24444James3275%24
25444Samuel8560%51
26888Megan4643%19.78
27888Dominic3253%16.96
28888Paul7647%35.72
29888Maxine2744%11.88
30888James3275%24
31888Samuel8560%51
Data FINAL
Cell Formulas
RangeFormula
H6:M15H6=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:F31F2=[@[Total Transactions]]*[@[Marketing conversion Rate]]
Dynamic array formulas.
Brilliant - thank you for your support
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
601
Office Version
  1. 365
Platform
  1. Windows
Happy to help. Thanks for the feedback.

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

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,725
Members
417,108
Latest member
Thein Than

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
Top