Accounting for Duplicates in Rank

W

w00tsayer

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
 
G

Gary''s Student

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

Martin Austin

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







- Show quoted text -
Click to expand...

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
 
R

RagDyeR

See if this helps:

http://tinyurl.com/2tl9ar

--

HTH,

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

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







- Show quoted text -
Click to expand...

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
 
G

Gary''s Student

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
 
Top