If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Top 3 highest values
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. |
#2
|
|||
|
|||
Top 3 highest values
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 "Gilbo" wrote: 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. |
#3
|
|||
|
|||
Top 3 highest values
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. -- Max Singapore --- "Gilbo" wrote: 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. |
#4
|
|||
|
|||
Top 3 highest values
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" wrote: 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. |
#5
|
|||
|
|||
Top 3 highest values
Thanks Max, it works as I want it to.
"Max" wrote: 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. -- Max Singapore --- "Gilbo" wrote: 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. |
#6
|
|||
|
|||
Top 3 highest values
Hello,
I suggest this approach: http://sulprobil.com/html/rank.html It's similar to Mike's. Regards, Bernd |
#7
|
|||
|
|||
Top 3 highest values
Thanks Mike, it works just as I want it to.
"Mike H" wrote: 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" wrote: 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. |
#8
|
|||
|
|||
Top 3 highest values
Welcome, Gilbo
-- Max Singapore ----- "Gilbo" wrote in message ... Thanks Max, it works as I want it to |
Thread Tools | |
Display Modes | |
|
|