software

How do you find the 1st, 2nd, .... nth highest value of a given row or
column of cells filled with numbers? Say I have a bunch of numbers in
A1 through Z1. I want the biggest 5 to appear on another sheet as A1,
A2, A3, A4, and A5.--
bob135
------------------------------------------------------------------------
bob135's Profile: www.excelforum.com/member.php...oamp;userid=33388
View this thread: www.excelforum.com/showthread...hreadid=532137=LARGE(Sheet1!$A$1:$Z$1, 1)
=LARGE(Sheet1!$A$1:$Z$1, 2)
=LARGE(Sheet1!$A$1:$Z$1, 3)
=LARGE(Sheet1!$A$1:$Z$1, 4)
=LARGE(Sheet1!$A$1:$Z$1, 5)quot;bob135quot; wrote:

gt;
gt; How do you find the 1st, 2nd, .... nth highest value of a given row or
gt; column of cells filled with numbers? Say I have a bunch of numbers in
gt; A1 through Z1. I want the biggest 5 to appear on another sheet as A1,
gt; A2, A3, A4, and A5.
gt;
gt;
gt; --
gt; bob135
gt; ------------------------------------------------------------------------
gt; bob135's Profile: www.excelforum.com/member.php...oamp;userid=33388
gt; View this thread: www.excelforum.com/showthread...hreadid=532137
gt;
gt;

Hi!

Try this in A1 and copy down as needed:

=LARGE(Sheet1!A$1:Z$1,ROWS($1:1))

Biff

quot;bob135quot; gt; wrote in
message ...
gt;
gt; How do you find the 1st, 2nd, .... nth highest value of a given row or
gt; column of cells filled with numbers? Say I have a bunch of numbers in
gt; A1 through Z1. I want the biggest 5 to appear on another sheet as A1,
gt; A2, A3, A4, and A5.
gt;
gt;
gt; --
gt; bob135
gt; ------------------------------------------------------------------------
gt; bob135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33388
gt; View this thread: www.excelforum.com/showthread...hreadid=532137
gt;

For a quick result, the autofilter option also has options for selecting
the top or bottom quot;Nquot; number of entries in a list.--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=532137
Cool, thanks. Now I have each item labeled. Say the labels are in row 1
and the values i want to compare are in row 2. How do I get the top 5
items to appear along with their labels?--
bob135
------------------------------------------------------------------------
bob135's Profile: www.excelforum.com/member.php...oamp;userid=33388
View this thread: www.excelforum.com/showthread...hreadid=532137
I got it to work for 1 sheet using MATCH. However, it doesn't seem that
MATCH will work if i want to compare using LARGE and MATCH with
multiple sheets. Is there a way to do this while keeping the sheets
apart?--
bob135
------------------------------------------------------------------------
bob135's Profile: www.excelforum.com/member.php...oamp;userid=33388
View this thread: www.excelforum.com/showthread...hreadid=532137quot;bob135quot; wrote:
gt; Now I have each item labeled. Say the labels are in row 1
gt; and the values i want to compare are in row 2.
gt; How do I get the top 5 items to appear along with their labels?

Try this construct which caters for the larger ambit,
i.e. the possibility of ties (or multiple ties)
occurring within the values in row2

Assume source table is in sheet: X
from col A across, labels in row1, values in row2

In a new sheet,

Put in A1:
=IF(ISERROR(LARGE($C:$C,ROW(A1))),quot;quot;,
INDEX(X!$1:$1,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)) )

Put in B1:
=IF(ISERROR(LARGE($C:$C,ROW(A1))),quot;quot;,
INDEX(X!$2:$2,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)) )

Put in C1:
=IF(INDEX(X!$2:$2,,ROW(A1))=0,quot;quot;,INDEX(X!$2:$2,,RO W(A1))-ROW()/10^10)

Select A1:C1, fill down to cover
the max expected extent of the source table in X
(fill down by as many rows as there are columns of data expected in X)

Cols A and B will auto-return the full descending sort from X,
with values in col B, corresponding labels in col A.
(Just read off the top x as desired)

Labels with tied values, if any, will appear in the same relative order
that these are with rows1 amp; 2 in X (from left to right)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Line:
gt; that these are with rows1 amp; 2 in X (from left to right)

should read as:
gt; that these are within rows1 amp; 2 in X (from left to right)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---