#1   Report Post  
Old January 4th 10, 02:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 19
Default 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   Report Post  
Old January 4th 10, 02:57 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 19
Default 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   Report Post  
Old January 4th 10, 03:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default 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 20-Car
2 15-Dog
3 15-Are

Use column D for you small values with =A1-COUNTIF(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   Report Post  
Old January 4th 10, 03:36 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Large / Small

Here's an easy non-array formulas approach using tiebreakers to set it up for
a full auto-descending/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

Auto-Top xx (Full descending sort)
In E1: =IF(B1="","",B1-ROW()/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. Read-off the top xx in cols F & G as
desired. You need the full descending sort to read-off 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!)

Auto-Bottom 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. Read-off the bottom xx in cols J & K as
desired. Again, you'd need the full ascending sort to read-off 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   Report Post  
Old January 6th 10, 08:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 19
Default 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 20-Car
2 15-Dog
3 15-Are

Use column D for you small values with =A1-COUNTIF(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   Report Post  
Old January 6th 10, 02:08 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Large and small values Kevin OBrien Charts and Charting in Excel 2 September 19th 08 08:11 PM
SMALL/LARGE and text FiluDlidu Excel Discussion (Misc queries) 4 March 29th 08 05:50 PM
Charting small and large percentages JRForm Charts and Charting in Excel 1 November 8th 07 07:13 PM
Sorting numbers in a row from small to large Peter Herman Excel Discussion (Misc queries) 10 August 10th 07 08:10 PM
SMALL and LARGE Tonto Excel Discussion (Misc queries) 6 October 27th 05 07:02 AM


All times are GMT +1. The time now is 05:54 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017