Calculating ranks based on a list of numbers

[elsenorjose]

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?

 

[Aladin Akyurek]

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

The formula in E2 should be copied to F2, not to G2.

In G2 should be entered:

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

 

[Aladin Akyurek]

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?

Just build the system I proposed and see how it behaves with test cases.

 

[Yogi Anand]

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?

Just build the system I proposed and see how it behaves with test cases.

Hi Aladin:

I am assuming you mean with the following test case ...

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

Here is my solution using this test data for Top8, Top6,Top4, and Top2 ...

y040227h1a.xls
	A	B	C	D	E	F
1			TopHowManyRanks	NumberOfRecords	NoOfRecords
2					inTopHowMany
3			4	8	2
4			IntermediaryColumns
5	REP	SALES	RANK	UniqueRank	SequentialRank	TopHowMany
6	RepA	10	8	8	5	Top6
7	RepB	20	5	5	4	Top4
8	RepC	20	5		4	Top4
9	RepD	20	5		4	Top4
10	RepE	23	3	3	3	Top4
11	RepF	23	3		3	Top4
12	RepG	40	2	2	2	Top2
13	RepH	60	1	1	1	Top2
Sheet4 (6)

Thanks for your review and your comments!

 

[Aladin Akyurek]

Pivot Tables appears to agree with my formula system...

Sample data:

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

First 3rd via Pivot Tables...

Show: Top 3

ranking elsenorjose X.xls
	A	B	C	D
2
3	SumofUnits
4	Rep	Total
5	E	23
6	F	23
7	G	40
8	H	60
9	GrandTotal	146
PT

Show: Top 6 (adds appropriate items that fall in the 2nd third)

Addition tan colored...

ranking elsenorjose X.xls
	A	B	C	D
2
3	SumofUnits
4	Rep	Total
5	C	20
6	D	20
7	E	23
8	F	23
9	G	40
10	H	60
11	GrandTotal	186
12
PT

Show all/top 8 (adds appropriate items that fall in the 3rd third)

Addition light green colored...

ranking elsenorjose X.xls
	A	B	C	D
2
3	SumofUnits
4	Rep	Total
5	A	10
6	B	12
7	C	20
8	D	20
9	E	23
10	F	23
11	G	40
12	H	60
13	GrandTotal	208
PT

And...

 

[Aladin Akyurek]

Pivot Tables appears to agree with my formula system...

Sample data:

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

...

And...

This is what we get from the formula system I proposed:

ranking elsenorjose X.xls
	A	B	C	D	E	F	G
1				0	1	0	0
2	8			0	3	6	8
3
4	Rep	Units	Rank		Top3	Top6	Top8
5	A	10	8		H	C	B
6	B	12	7		G	D	A
7	C	20	5		E
8	D	20	6		F
9	E	23	3
10	F	23	4
11	G	40	2
12	H	60	1
Aladin