Home 
Search 
Today's Posts 
#1




Large / Small
I have a scenario where I need to rank data according to size, top 3 and
bottom 3. A B C (Large) D 1 20 Car 1 20  Car 2 15 Dog 2 15  Dog 3 5 New 3 15  Are 4 15 Are 5 10 Err Column A and B are my data, I use column C as reference in the Large and Small formula and then concatenate with D as the result. But my problem is when I have 2 similar result as in this example no.2 and 3. What should I do so that the 3rd highest is correctly reference? 
#2




Large / Small
This is what I have at the moment
=CONCATENATE(ROUND(LARGE(Region!F:F,C4)*100,2),"%" ,"  ",INDEX(Region!B:B,MATCH(LARGE(Region!F:F,C4),Regi on!$F:$F,0))) "" wrote: I have a scenario where I need to rank data according to size, top 3 and bottom 3. A B C (Large) D 1 20 Car 1 20  Car 2 15 Dog 2 15  Dog 3 5 New 3 15  Are 4 15 Are 5 10 Err Column A and B are my data, I use column C as reference in the Large and Small formula and then concatenate with D as the result. But my problem is when I have 2 similar result as in this example no.2 and 3. What should I do so that the 3rd highest is correctly reference? 
#3




Large / Small
Add a new column C with this in C1
=A1+COUNTIF(A1:$A$100,A1)/100 Copy down the column after adjusting the range to suit your needs Use column E for the 1,2,3 that were in column C In G1 enter this and adjust the ranges as needed =INDEX($A$1:$A$100,MATCH(LARGE($C$1:$C$100,E1),$C$ 1:$C$100,0))&""&INDEX($B$1:$B$100,MATCH(LARGE($C$1:$C$100,E1),$C $1:$C$100,0)) Copy down the column This correctly gives 1 20Car 2 15Dog 3 15Are Use column D for you small values with =A1COUNTIF(A1:$A$100,A1)/100 You could put the C (and D) columns off the the right and, if required, hide them best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "" wrote in message ... I have a scenario where I need to rank data according to size, top 3 and bottom 3. A B C (Large) D 1 20 Car 1 20  Car 2 15 Dog 2 15  Dog 3 5 New 3 15  Are 4 15 Are 5 10 Err Column A and B are my data, I use column C as reference in the Large and Small formula and then concatenate with D as the result. But my problem is when I have 2 similar result as in this example no.2 and 3. What should I do so that the 3rd highest is correctly reference? 
#4




Large / Small
Here's an easy nonarray formulas approach using tiebreakers to set it up for
a full autodescending/ascending sort Assume your source data as posted within B1:C10 where B1:B10 contains scores (ie real numbers), C1:C10 are the names/items AutoTop xx (Full descending sort) In E1: =IF(B1="","",B1ROW()/10^10) This is the tiebreaker criteria for descending sort In F1: =IF(ROWS($1:1)COUNT($E$1:$E$10),"",INDEX(B$1:B$10 ,MATCH(LARGE($E$1:$E$10,ROWS($1:1)),$E$1:$E$10,0)) ) Copy F1 to G1. Select & copy E1:G1 down to G10. Minimize/hide col E. Cols F & G will return the required results, ie scores/names in descending order by scores in col B. Any names with tied scores will appear in the same relative order as they are within the source. Readoff the top xx in cols F & G as desired. You need the full descending sort to readoff since there could be multiple ties (you could have say, a 2 way tie for 1st, a 3 way tie for 2nd, & a 2 way tie for 3rd. aw!) AutoBottom xx (Full ascending sort) In I1: =IF(B1="","",B1+ROW()/10^10) This is the tiebreaker criteria for ascending sort. Exactly the same as the earlier, except for: + (instead of: ) In J1: =IF(ROWS($1:1)COUNT($I$1:$I$10),"",INDEX(B$1:B$10 ,MATCH(SMALL($I$1:$I$10,ROWS($1:1)),$I$1:$I$10,0)) ) Copy J1 to K1. Select & copy I1:K1 down to K10. Minimize/hide col I. Cols J & K will return the required results, ie scores/names in ascending order by scores in col B. Any names with tied scores will appear in the same relative order as they are within the source. Readoff the bottom xx in cols J & K as desired. Again, you'd need the full ascending sort to readoff since there could be multiple ties. The formula is v.similar to the earlier, except for the use of SMALL (instead of LARGE) and the point to the criteria col I. Success? Celebrate it, hit the YES below  Max Singapore  "" wrote: This is what I have at the moment =CONCATENATE(ROUND(LARGE(Region!F:F,C4)*100,2),"%" ,"  ",INDEX(Region!B:B,MATCH(LARGE(Region!F:F,C4),Regi on!$F:$F,0))) "" wrote: I have a scenario where I need to rank data according to size, top 3 and bottom 3. A B C (Large) D 1 20 Car 1 20  Car 2 15 Dog 2 15  Dog 3 5 New 3 15  Are 4 15 Are 5 10 Err Column A and B are my data, I use column C as reference in the Large and Small formula and then concatenate with D as the result. But my problem is when I have 2 similar result as in this example no.2 and 3. What should I do so that the 3rd highest is correctly reference? 
#5




Large / Small
Thank you Max and Bernard for your solution. Manage to solve the issue.
However now I have another scenario. Will your solution be achievable if instead of finding the largest value which is a whole number, it is use to find the largest or smallest value which are in percentages. And the percentages are derived from a formula (a+b)/b... "Bernard Liengme" wrote: Add a new column C with this in C1 =A1+COUNTIF(A1:$A$100,A1)/100 Copy down the column after adjusting the range to suit your needs Use column E for the 1,2,3 that were in column C In G1 enter this and adjust the ranges as needed =INDEX($A$1:$A$100,MATCH(LARGE($C$1:$C$100,E1),$C$ 1:$C$100,0))&""&INDEX($B$1:$B$100,MATCH(LARGE($C$1:$C$100,E1),$C $1:$C$100,0)) Copy down the column This correctly gives 1 20Car 2 15Dog 3 15Are Use column D for you small values with =A1COUNTIF(A1:$A$100,A1)/100 You could put the C (and D) columns off the the right and, if required, hide them best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "" wrote in message ... I have a scenario where I need to rank data according to size, top 3 and bottom 3. A B C (Large) D 1 20 Car 1 20  Car 2 15 Dog 2 15  Dog 3 5 New 3 15  Are 4 15 Are 5 10 Err Column A and B are my data, I use column C as reference in the Large and Small formula and then concatenate with D as the result. But my problem is when I have 2 similar result as in this example no.2 and 3. What should I do so that the 3rd highest is correctly reference? . 
#6




Large / Small
My suggestion has no restriction on the source numbers being whole numbers.
Should work just as well with fractions. Why not try it out for yourself? Just ensure that the formula derives all real numbers in that source col (no error returns, no text, etc)  Max Singapore "" wrote in message ... Thank you Max and Bernard for your solution. Manage to solve the issue. However now I have another scenario. Will your solution be achievable if instead of finding the largest value which is a whole number, it is use to find the largest or smallest value which are in percentages. And the percentages are derived from a formula (a+b)/b... 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Large and small values  Charts and Charting in Excel  
SMALL/LARGE and text  Excel Discussion (Misc queries)  
Charting small and large percentages  Charts and Charting in Excel  
Sorting numbers in a row from small to large  Excel Discussion (Misc queries)  
SMALL and LARGE  Excel Discussion (Misc queries) 