MAX

[Master]

Hi everyone!

How do i get the top 5 numbers in a range of cells?

example:

A B C
100
25
34
65
87
12
10
99

I was already able to get the highest number using the max function.
=max(a1:a8)
In another set of cells i want the formula to return the value of the 2nd to
the highest and so on and so forth.

Thanks for the help.

 

[Trevor Shuttleworth]

=LARGE(B11:J11,{1,2,3,4,5}) array entered in five cells

Adjust the range to suit your requirements.

Regards

Trevor

 

[Peo Sjoblom]

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

copy down

 

[Master]

Thanks Trevor!

I have another question though.. Actually i want to apply conditional
formatting on these 5 top numbers. On the example below, since cell A1 is the
highest, i want the cell color green; then cell A8 is the 2nd highest i want
a different color then; and so on (i have a legend for the colors). Since i
have 5 top numbers to color, i need 5 conditional formatting won't i? But i'm
using MS Excel 2000 and there's only 3 conditional formatting, is there any
other way to do it?

Thanks again.

 

[Dave R.]

Use the LARGE function -- and please get a hold of the posting procedure..