How to Use the LARGE Function, More Versatile than MAX

We know about the MAX function in Excel, to get the biggest value in a range of values.  But did you know that there is a function to give you the second biggest, third biggest, and so on?  It’s the LARGE function.  It can also give you the “first biggest”, replacing MAX.

Syntax: LARGE(array, k)

Arguments:

• Array (required): The range of cells or list of data for which to return the k-th largest value.
• K (required): The position (1 = largest) in “Array” to return.

1.     LARGE Function Examples

In this example, the cells A4:A13 are range named “Data”.

LARGE(array, 1) returns the largest value.

If array is empty, LARGE returns the #NUM! error value.

If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.

If you need, not the largest, but rather the smallest values in a range or list, use the function SMALL.  That saves having to know the number of values.

2.     Practical Applications

LARGE, SMALL, MAX and MIN are particularly useful in that you do not need to sort the list. This lets you find the number at a given rank in the list without messing up your data.

There are many applications.  One example is finding the top three scores in a contest. Another is getting the company’s top five expenditure items for the quarter.

In contrast, you may have a given score, expenditure, etc. and want to find where it ranks in the list.  Here, use the formula RANK(number, ref, [order]) instead.