Listing top 3 values

[Anthony Slater]

If I have 20 different values(numbers, not text) in column
A and column B contains names, What formula do I need to
show the top 3 values?

I know the formula MAX will list the highest value but I
can't seem to work out how to display the 2nd highest, 3rd
highest ect...

Thanks in advance

Ant

 

[Paul B]

Anthony, look at large in help

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Chip Pearson]

Ant,

The LARGE function will return the Nth largest item in a list of values.
E.g.,

=LARGE(A1:A10,2)

See also the SMALL function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Ron de Bruin]

You can use this Array formula

=LARGE($A$1:$A$20,ROW(INDIRECT("1:3")))

Select 3 empty cells
copy this formula in the formula bar and press Ctrl-Shift-Enter instead of Enter

 

[Chris Kroening]

If you don't want to use a function you can use
autofilter, which will have a choice for Top 10, you can
change the value to show bottom or top (N) you choose the
value.