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.

 

[Guest]

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

 

[Biff]

Hi!

Try this in A1 and copy down as needed:

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

Biff

 

[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?

 

[bob135]

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?

 

[Max]

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]

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)

---