Calculating ranks based on a list of numbers

elsenorjose

New Member
Joined
Feb 27, 2004
Messages
1
I have a list of numbers (unit production per sales person) and I want to identify which sales people fall in the top 33%, the middle 33%, and the bottom 33%. Given this example:

Rep A - 10 units
Rep B - 10 units
Rep C - 15 units
Rep D - 18 units
Rep E - 20 units
Rep F - 23 unites

I can identify that Reps A and B are in the bottom 3rd, Reps C and D are in the middle 3rd, and Reps E and F are in the top 3rd.

Of course my list is not so simple. I have tried the rank and percentile tool in the analysis toolpak and it's not quite what I need. I have also created a formula which calculates the median average and then based on that average gives me values above or below the median but this doesn't quite work because given value X as my median, x-1 goes to the low range and x+1 goes into the high range but of course that wouldn't be true because in real life, one unit in either direction won't affect your score that much.

Has anyone solved a similar problem?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I must say up front that what follows attempts to apply my recent system of formulas for the Top N class of problems to your particular question. All this might be overwhelming, but, I hope, worth testing...
ranking elsenorjose.xls
ABCDEFG
10000
260246
3
4RepUnitsRankTop2Top4Top6
5A105FDA
6B106ECB
7C154   
8D183   
9E202   
10F231   
Sheet1


A2:

=COUNT(B5:B10)

Counts the data points we deal with.

C5, which is copied down:

=RANK(B5,$B$5:$B$10)+COUNTIF($B$5:B5,B5)-1

Ranks units produced.

D1:D2 must house 0's.

E1:

=MAX(IF(INDEX($B$5:$B$10,MATCH(E2,$C$5:$C$10,0))=$B$5:$B$10,$C$5:$C$10))-E2

which must be confirmed with control+shift+enter instead of just with enter.

This formula is copied across to G1.

E2:

=ROUNDUP(($A$2*33%),0)+D2

which is copied across to G2.

E4:

="Top "&E2

which is copied to G4.

E5:

=IF(ROW()-ROW(E$5)+1+D$1+D$2<=E$2+E$1,INDEX($A$5:$A$10,MATCH(ROW()-ROW(E$5)+1+D$1+D$2,$C$5:$C$10,0)),"")

which is copied to G5 then down.

Try the following...

1] Change D's units from 18 to 20 & observe the results.

2] Change also B's units from 10 to 15 & observe the results.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi elsenorjose:

Welcome to mrExcel Board:

Here is one of the ways I can do ...
Book1
ABCDE
1REPSALESRANKAdjustedRANK6
2RepA1055Bottom3rd
3RepB1056Bottom3rd
4RepC1544MiddleThird
5RepD1833MiddleThird
6RepE2022Top3rd
7RepF2311Top3rd
Sheet4


Please note I have use two intermediary columns to keep calculations simple and transparent.

The formula in cell C2 is ... =RANK(B2,$B:$B,0)

formula in cell D2 is ... =C2+COUNTIF($C$2:C2,C2)-1

and formula in cell E2 is ... =IF(D2<=$E$1*1/3,"Top 3rd",IF(D2<=$E$1*2/3,"Middle Third","Bottom 3rd"))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Yogi Anand said:
...

Here is one of the ways I can do ...
Please note I have use two intermediary columns to keep calculations simple and transparent.

The formula in cell C2 is ... =RANK(B2,$B:$B,0)

formula in cell D2 is ... =C2+COUNTIF($C$2:C2,C2)-1

and formula in cell E2 is ... =IF(D2<=$E$1*1/3,"Top 3rd",IF(D2<=$E$1*2/3,"Middle Third","Bottom 3rd"))

Just change B5 from 18 to 20.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi Aladin:

I changed B5 from 18 to 20 -- and it did adversely impact the result. Thanks for pointing out that the solution I proposed needs fixing.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Here is another shot at it ...
Book1
ABCDEF
1TopHowManyNumberOfRecordsNoOfRecords
2inTopHowMany
3362
4IntermediaryColumns
5REPSALESRANKUniqueRankSequentialRankTopHowMany
6RepA10555Top6
7RepB105 5Top6
8RepC15444Top4
9RepD18333Top4
10RepE20222Top2
11RepF23111Top2
Sheet4 (4)


Formula in cell C6 is ... =RANK(B6,$B:$B,0)

Formula in cell D6 is ... =IF(COUNTIF($C$6:C6,C6)=1,C6,"")

Formula in cell E6 is ... =IF(D6="",E5,RANK(D6,$D$6:$D$11,1))

Formula in cell F6 is ... ="Top "&MIN(ROUNDUP(E6/2,0)*$E$3,$D$3)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Yogi Anand said:
Here is another shot at it ...

Formula in cell C6 is ... =RANK(B6,$B:$B,0)

Formula in cell D6 is ... =IF(COUNTIF($C$6:C6,C6)=1,C6,"")

Formula in cell E6 is ... =IF(D6="",E5,RANK(D6,$D$6:$D$11,1))

Formula in cell F6 is ... ="Top "&MIN(ROUNDUP(E6/2,0)*$E$3,$D$3)

I recognize some elements here. :LOL:

IMHO, it still does not handle the test cases I put up in my first post as it should, even the case of changing D's 18 to 20.
 

Ahnold

Well-known Member
Joined
Feb 20, 2004
Messages
636
Aladin and Yogi, would like to get your opinion on this solution:

Yogi, using your first worksheet:
D1 = =COUNTA(A:A)-1
C2 = =RANK(B2,$B$2:$B$7,0)
D2 = =C2/$D$1
E2 = =IF(D2>=0.67,"BOTTOM THIRD",IF(D2>=0.34,"MIDDLE THIRD","TOP THIRD"))
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Aladin Akyurek said:

I recognize some elements here. :LOL:
Hi Aladin:

I am sure you recognize all of them -- I have used common functions!

IMHO, it still does not handle the test cases I put up in my first post as it should, even the case of changing D's 18 to 20.
The following illustration shows changing the value 18 to 20 ...
y040227h1a.xls
ABCDEF
1TopHowManyNumberOfRecordsNoOfRecords
2inTopHowMany
3362
4IntermediaryColumns
5REPSALESRANKUniqueRankSequentialRankTopHowMany
6RepA10554Top4
7RepB105 4Top4
8RepC15443Top4
9RepD20222Top2
10RepE202 2Top2
11RepF23111Top2
Sheet4 (5)


What do you think? Are the results not right? I would not be surprised if they do not pass every test . However, in this case if they are not, what do you think they should be?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Ahnold said:
...would like to get your opinion on this solution:

Yogi, using your first worksheet:
D1 = =COUNTA(A:A)-1
C2 = =RANK(B2,$B$2:$B$7,0)
D2 = =C2/$D$1
E2 = =IF(D2>=0.67,"BOTTOM THIRD",IF(D2>=0.34,"MIDDLE THIRD","TOP THIRD"))

That's interesting. But try it on the following sample:

{"Rep","Units";"A",10;"B",20;"C",20;"D",20;"E",23;"F",23;"G",40;"H",60}

By the way, this helped me to see I overlooked a point of attention:

The formula in G2 in my sytem

=ROUNDUP(($A$2*33%),0)+F2

should be changed to:

=MIN(A2,ROUNDUP(($A$2*33%),0)+F2)

My system gives with this new sample Vs yours:

Top 3 (TOP THIRD)

{"H";"G";"E";"F"} vs {"G";"H"}

Top 6 (MIDDLE THIRD)

{"B";"C";"D"} vs {"B";"C";"D";"E";"F"}

Top 8 (BOTTOM THIRD)

A vs A

Note the system I proposed assumes cuts the data in 3, but it also adapts itself to what goes in each cut. Ties can pre-empt next cuts.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,681
Members
425,229
Latest member
Rashid mahmood

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