Top 3 highest values

[Gilbo]

I have managed to get the three top values in a list with
=INDEX($R$3:$R$92,MATCH(LARGE($S$3:$S$92,1),$S$3:$S$92,0)),
=INDEX($R$3:$R$92,MATCH(LARGE($S$3:$S$92,2),$S$3:$S$92,0)),
=INDEX($R$3:$R$92,MATCH(LARGE($S$3:$S$92,3),$S$3:$S$92,0)), but it does not
allow for duplicates. Any Help would be great. Your thanks in advance.

 

[מיכ×ל (מיקי) ×בידן]

Consider visiting Pearsons Site:
http://www.cpearson.com/Excel/DistinctValues.aspx
1) I would suggest to use an helper-column and to fill it with unique values
from the source range.
2) You can also use the "MOREFUNC" add-on built-in function: UniqueValues -
which
paralyzes the duplicates and sorts the range from the largest to smallest.
*** Use "google" to filn the "MOREFUNC" site/
Micky

 

[Max]

An alternative approach to incorporate tie-breakers to derive Top xx
Using your source set-up (gathered from your posted formulas),
you have names in col R, scores in col S

In U3: =IF(S3="","",S3-ROW()/10^10)
This is the tiebreaker criteria

Then in V3
=IF(ROWS($1:1)>COUNT($U$3:$U$92),"",INDEX(R$3:R$92,MATCH(LARGE($U$3:$U$92,ROWS($1:1)),$U$3:$U$92,0)))
Copy V3 to W3, then select & copy U3:W3 down to W92. Minimize/hide col U.
Cols V & W will return the required results, ie names/scores from col R in
descending order by scores in col S. Any names with tied scores will appear
in the same relative order as they are within the source. Read-off the top xx
in cols V & W as desired. Success? Celebrate it, hit the YES below.

 

[Mike H]

Hi,

Use a helper column to rank the numbers then reference this column. I used
column T and drag down. This will rank the highest numbers as 1 and the
second highest (even if it's a duplicate) as 2.

=RANK(S3,$S$3:$S$92)+COUNTIF($S$3:S3,S3)-1

You then change your formula to reference this ranking colimn and look for
the smallest number

=INDEX($R$3:$R$92,MATCH(SMALL($T$3:$T$92,1),$T$3:$T$92,0))
=INDEX($R$3:$R$92,MATCH(SMALL($T$3:$T$92,2),$T$3:$T$92,0))
=INDEX($R$3:$R$92,MATCH(SMALL($T$3:$T$92,3),$T$3:$T$92,0))

Mike

 

[Gilbo]

Thanks Max, it works as I want it to.

An alternative approach to incorporate tie-breakers to derive Top xx
Using your source set-up (gathered from your posted formulas),
you have names in col R, scores in col S

In U3: =IF(S3="","",S3-ROW()/10^10)
This is the tiebreaker criteria

Then in V3:
=IF(ROWS($1:1)>COUNT($U$3:$U$92),"",INDEX(R$3:R$92,MATCH(LARGE($U$3:$U$92,ROWS($1:1)),$U$3:$U$92,0)))
Copy V3 to W3, then select & copy U3:W3 down to W92. Minimize/hide col U.
Cols V & W will return the required results, ie names/scores from col R in
descending order by scores in col S. Any names with tied scores will appear
in the same relative order as they are within the source. Read-off the top xx
in cols V & W as desired. Success? Celebrate it, hit the YES below.

 

[Bernd P]

Hello,

I suggest this approach:
http://sulprobil.com/html/rank.html

It's similar to Mike's.

Regards,
Bernd

 

[Gilbo]

Thanks Mike, it works just as I want it to.

Hi,

Use a helper column to rank the numbers then reference this column. I used
column T and drag down. This will rank the highest numbers as 1 and the
second highest (even if it's a duplicate) as 2.

=RANK(S3,$S$3:$S$92)+COUNTIF($S$3:S3,S3)-1

You then change your formula to reference this ranking colimn and look for
the smallest number

=INDEX($R$3:$R$92,MATCH(SMALL($T$3:$T$92,1),$T$3:$T$92,0))
=INDEX($R$3:$R$92,MATCH(SMALL($T$3:$T$92,2),$T$3:$T$92,0))
=INDEX($R$3:$R$92,MATCH(SMALL($T$3:$T$92,3),$T$3:$T$92,0))

Mike

 

[Max]

Welcome, Gilbo