Accounting for Duplicates in Rank
 

Good morning --

I am trying to account for duplicates in using the RANK worksheet
function. Basically, I have 43 categories that I am trying to rank,
but I am only showing the Top 5 / Bottom 5. Each of these sets of
five rarely ever have duplicates, but due to the fact that the mid
section of results typically do have duplicates, I cannot properly
account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5
performers).

The help documentation did not particularly help me in this
situation. Anyone out there with a solution?

Thanks,

Martin Austin

Accounting for Duplicates in Rank
 

Say in A1 thru A43 we have:
14
6
5
4
12
19
6
1
11
7
3
8
16
1
14
10
3
5
6
10
5
3
19
7
9
11
16
3
20
12
5
14
8
19
3
13
3
16
6
1
1
2
10

In B1 thru B5 enter:
=LARGE(A$1:A$43,ROW())
to see:
20
19
19
19
16

In C1 thru C5 enter:
=SMALL(A$1:A$43,ROW())
to see:
1
1
1
1
2


In this way duplicates are properly displayed. This is what you would see
if the data were sorted and you examines the top and bottom 5.

--
Gary''s Student - gsnu200726


" wrote:

Good morning --

I am trying to account for duplicates in using the RANK worksheet
function. Basically, I have 43 categories that I am trying to rank,
but I am only showing the Top 5 / Bottom 5. Each of these sets of
five rarely ever have duplicates, but due to the fact that the mid
section of results typically do have duplicates, I cannot properly
account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5
performers).

The help documentation did not particularly help me in this
situation. Anyone out there with a solution?

Thanks,

Martin Austin

Accounting for Duplicates in Rank
 

On May 31, 9:51 am, Gary''s Student
wrote:
Say in A1 thru A43 we have:
14
6
5
4
12
19
6
1
11
7
3
8
16
1
14
10
3
5
6
10
5
3
19
7
9
11
16
3
20
12
5
14
8
19
3
13
3
16
6
1
1
2
10

In B1 thru B5 enter:
=LARGE(A$1:A$43,ROW())
to see:
20
19
19
19
16

In C1 thru C5 enter:
=SMALL(A$1:A$43,ROW())
to see:
1
1
1
1
2

In this way duplicates are properly displayed. This is what you would see
if the data were sorted and you examines the top and bottom 5.

--
Gary''s Student - gsnu200726



" wrote:
Good morning --

I am trying to account for duplicates in using the RANK worksheet
function. Basically, I have 43 categories that I am trying to rank,
but I am only showing the Top 5 / Bottom 5. Each of these sets of
five rarely ever have duplicates, but due to the fact that the mid
section of results typically do have duplicates, I cannot properly
account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5
performers).

The help documentation did not particularly help me in this
situation. Anyone out there with a solution?

Thanks,

Martin Austin- Hide quoted text -

- Show quoted text -

Hi and sorry for the ambiguity in my request. What I was looking to
do is skip duplicates in the ranking. So I do not want to see 1, 2,
2, 2, 5. I want to see 1, 2, 3, 4, 5.

Even if I could use SMALL or LARGE and exclude items where the value
is zero, that would fix the problem I'm having.

Thanks,

Martin Austin

Accounting for Duplicates in Rank
 

See if this helps:

http://tinyurl.com/2tl9ar

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Martin Austin" wrote in message
oups.com...
On May 31, 9:51 am, Gary''s Student
wrote:
Say in A1 thru A43 we have:
14
6
5
4
12
19
6
1
11
7
3
8
16
1
14
10
3
5
6
10
5
3
19
7
9
11
16
3
20
12
5
14
8
19
3
13
3
16
6
1
1
2
10

In B1 thru B5 enter:
=LARGE(A$1:A$43,ROW())
to see:
20
19
19
19
16

In C1 thru C5 enter:
=SMALL(A$1:A$43,ROW())
to see:
1
1
1
1
2

In this way duplicates are properly displayed. This is what you would see
if the data were sorted and you examines the top and bottom 5.

--
Gary''s Student - gsnu200726



" wrote:
Good morning --

I am trying to account for duplicates in using the RANK worksheet
function. Basically, I have 43 categories that I am trying to rank,
but I am only showing the Top 5 / Bottom 5. Each of these sets of
five rarely ever have duplicates, but due to the fact that the mid
section of results typically do have duplicates, I cannot properly
account for categories ranked 39, 40, 41, 42 and 43 (Bottom 5
performers).

The help documentation did not particularly help me in this
situation. Anyone out there with a solution?

Thanks,

Martin Austin- Hide quoted text -

- Show quoted text -

Hi and sorry for the ambiguity in my request. What I was looking to
do is skip duplicates in the ranking. So I do not want to see 1, 2,
2, 2, 5. I want to see 1, 2, 3, 4, 5.

Even if I could use SMALL or LARGE and exclude items where the value
is zero, that would fix the problem I'm having.

Thanks,

Martin Austin

Accounting for Duplicates in Rank
 

Hi Martin:

How about two steps or two columns. The first column puts them in rank
order and the second column removes dups:

if the data is in A1 thru A43, then in B1 thru B43 put:
=LARGE(A:A,ROW())


In C1 put:
=B1

in C2 thru C43 put:
=IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$B$1:$B$43&""), 0)),"",INDEX(IF(ISBLANK($B$1:$B$43),"",$B$1:$B$43) ,MATCH(0,COUNTIF(C$1:C1,$B$1:$B$43&""),0)))
as an array formula (CNTRL-SHIFT-ENTER) and copy down.


Here is an example of what you might see:

21 39 39
39 38 38
13 38 35
5 38 34
30 35 32
8 34 31
32 32 30
29 32 29
27 32 28
32 31 27
19 30 25
38 29 24
32 28 23
8 27 21
31 27 20
25 25 19
14 25 18
16 25 17
4 24 16
18 24 15
27 23 14
24 23 13
34 23 11
28 23 8
35 21 5
25 21 4
17 20
16 19
23 18
23 17
11 16
38 16
16 16
23 15
23 14
38 14
24 13
4 11
21 8
14 8
25 5
20 4
15 4


--
Gary''s Student - gsnu200726