Calculating ranks based on a list of numbers

elsenorjose

New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

MrExcel MVP
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
Hi elsenorjose:

Welcome to mrExcel Board:

Here is one of the ways I can do ...
Book1
ABCDE
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"))

MrExcel MVP
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

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
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)

MrExcel MVP
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.

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
Aladin and Yogi, would like to get your opinion on this solution:

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

I recognize some elements here.

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?

MrExcel MVP
Ahnold said:
...would like to get your opinion on this solution:

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.

Replies
3
Views
158
Replies
4
Views
406
Replies
3
Views
416
Replies
5
Views
515
Replies
0
Views
32

1,186,808
Messages
5,959,933
Members
438,455
Latest member
Beverly Jarrell

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.

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

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