Wednesday, September 21, 2005

Using the LARGE and SMALL functions in Excel

Excel has a number of statistical functions that are very powerful in analysing large chunks of data.
While the MAX and MIN functions find the largest and smallest values in a list respectively sometimes you need to find the 2nd largest or 14th smallest etc.

The syntax is =SMALL(array,n) where n is the nth smallest value to return. The syntax for LARGE is the same.
Of course n can be a reference to another cell or a function returning a value. The array is a range of cells Eg. A:A, R24:T34 etc.

Make sure that n isn't greater than the number of items in the list or Excel will return a #NUM! error.