Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 3 highest values
Hello,
I suggest this approach: http://sulprobil.com/html/rank.html It's similar to Mike's. Regards, Bernd |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 3 highest values
Welcome, Gilbo
-- Max Singapore ----- "Gilbo" wrote in message ... Thanks Max, it works as I want it to |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highest Values Comparison | Excel Worksheet Functions | |||
Average of Highest values | Excel Discussion (Misc queries) | |||
Using the highest 2 out of 3 values in a formula | Excel Discussion (Misc queries) | |||
3 highest values | New Users to Excel | |||
3 highest values indicated using formatting | Excel Worksheet Functions |