Find the 5 max values in a column?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Any one know of a way to:

On a column: Formated as numeric

Return the Max(thats the easy part and the "max" function
will do that), and the next 4 values (ie max, the next
max if Max1 were removed from the search - etc)?
 
You want the LARGE function, LARGE(A1:A100,1) will return the largest,
LARGE(A1:A100,2) will return the 2nd largest, etc.

You can do this in 1 cell by joining the formulas with & and &"-"&

or you could use 5 cells such as B1:B5 with

=LARGE(A$1:A$100,ROW())

entered in each.
 
Thanks for the info, !!!

-----Original Message-----
You want the LARGE function, LARGE(A1:A100,1) will return the largest,
LARGE(A1:A100,2) will return the 2nd largest, etc.

You can do this in 1 cell by joining the formulas with & and &"-"&

or you could use 5 cells such as B1:B5 with

=LARGE(A$1:A$100,ROW())

entered in each.






.
Click to expand...
 
Let A2:A100 house the data of interest.

In B2 enter & copy down:

=LARGE($A$2:$B$100,ROW()-ROW($B$2)+1)

Note that the ROW($B$2) bit anchors the formula to the first cell it is
entered. This makes
the formula robust against inserting rows before the formula cell.
 
Back
Top