Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MAX
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. |
#2
|
|||
|
|||
=LARGE(B11:J11,{1,2,3,4,5}) array entered in five cells
Adjust the range to suit your requirements. Regards Trevor "Master" wrote in message ... 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. |
#3
|
|||
|
|||
=LARGE($A$1:$A$8,ROW(1:1))
copy down -- Regards, Peo Sjoblom "Master" wrote in message ... 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. |
#4
|
|||
|
|||
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. "Trevor Shuttleworth" wrote: =LARGE(B11:J11,{1,2,3,4,5}) array entered in five cells Adjust the range to suit your requirements. Regards Trevor "Master" wrote in message ... 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. |
#5
|
|||
|
|||
Use the LARGE function -- and please get a hold of the posting procedure..
"Master" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|