Finding the highest values

[bob135]

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.

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?

[JMB]

=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)


"bob135" wrote:

>
> 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: http://www.excelforum.com/member.php...o&userid=33388
> View this thread: http://www.excelforum.com/showthread...hreadid=532137
>
>

[Biff]

Hi!

Try this in A1 and copy down as needed:

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

Biff

"bob135" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...o&userid=33388
> View this thread: http://www.excelforum.com/showthread...hreadid=532137
>

[John James]

For a quick result, the autofilter option also has options for selecting the top or bottom "N" number of entries in a list.

[bob135]

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?

[Max]

"bob135" wrote:
> 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?

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))),"",
INDEX(X!$1:$1,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))

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

Put in C1:
=IF(INDEX(X!$2:$2,,ROW(A1))=0,"",INDEX(X!$2:$2,,ROW(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 & 2 in X (from left to right)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

Line:
> that these are with rows1 & 2 in X (from left to right)

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