MrExcel Publishing
Your One Stop for Excel Tips & Solutions

finding the "N" highest values in a range

Posted by Roger on August 19, 2001 11:40 AM

Hi, I wondering if it is possible to find the 3 highest values from a set
of data. I was using the Max command but it just wasn't doing it right. Is
it possible to find "N" highest values in a row of data.

I am using Excel 97

Posted by Andrew on August 19, 2001 12:47 PM

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.

Posted by Roger on August 19, 2001 12:52 PM

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


Posted by Aladin Akyurek on August 19, 2001 1:44 PM

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


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

Copy down this as far as needed.


Posted by Mark W. on August 19, 2001 3:20 PM

Suppose that A1:A11 contains...


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


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

Posted by Andrew on August 19, 2001 10:17 PM

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.