Large or Small, Max or Min

Location: http://www.mvps.org/dmcritchie/excel/large.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Order values using LARGE or SMALL (#large)

Based on a newsgroup reply by George Simms 2000-01-30.

	A	B	C	D	Formulas used in Column C
1	14		39		=LARGE($A$1:$A$13,ROW(1:1))
2	11		37		=LARGE($A$1:$A$13,ROW(2:2))
3	1		34		=LARGE($A$1:$A$13,ROW(3:3))
4	37		24		=LARGE($A$1:$A$13,ROW(4:4))
5	24		23		=LARGE($A$1:$A$13,ROW(5:5))

Use LARGE to find to largest numbers in a range, and SMALL to find the smallest numbers in a range.

In the example at the left ROW(1:1) is used in place of 1, the advantage is that it allows the formula to be replicated down using the fill-handle.

For more information on fill-handle see Fill-Handles and Replication.

If the 2nd and 3rd largest is the same and you want to see both results you can make numbers a little different so that you can see both in a reply posting by Denny Campbell 2000/03/01.

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).

This page was introduced on xxxxxx xx, 2004. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments

Large or Small, Max or Min

Order values using LARGE or SMALL (#large)

Related (#related)