finding the "N" highest values in a range

Re: finding the

Highlight the range including the column heading. Go to Data > Filter > Auto Filter.
Click the drop down list and Select "Top 10". You can change the settings to suit your requirements.

Re: finding the

thanks for the answer
but I was looking for a solution using the max()
because the range is not always in a column

Roger

Supposing that you have the following in A from A1 on.

{Numbers;
1;
3;
5;
7;
8;
2;
1}

In B2 enter: =LARGE($A$2:$A$8,ROW()-1)

Copy down this as far as needed.

Aladin

Suppose that A1:A11 contains...

{"Field1";2;3;4;8;5;6;9;7;1;10}

You could enter {1;2;3;4;5} in cells C1:C5 and
the array formula...

{=OFFSET($A$1,MATCH(C1,RANK($A$2:$A$11,$A$2:$A$11),0),)}

into cell D1 and Fill Down to cell D5. Please
note that array formulas are entered using the
Control+Shift+Enter key combination, and that the
braces, {}, are not typed by you -- they're
supplied by Excel in recognition of a properly
entered array formula.

Now, a bit about the RANK worksheet function...
duplicates will share the same ranking. So, if
there were two 10s in the above list cell D2 will
show #N/A -- there isn't a 2nd highest. I've
shown the top 5 in case your interested in the
top 3 *unique* values. That would allow for three
10s and still show the 4th and 5th ranked values,
9 and 8 respectively. If there aren't any
duplicate values then such allowances won't be
necessary.

Re: finding the

Try this

LARGE(A1:D1,1) returns the largest value in the range.

LARGE(A1:D1,2) Returns the second largest value in the range.

..... and so on.