In statistics the MAX function is not enough. The need to calculate, for example, the third largest value of a range of cells, is very common.
This is what the LARGE function calculates. It returns the n-th largest value from an array of numeric values.
We have put together a Tutorial on how to use the functions LARGE and SMALL.
The syntax of the LARGE function is the following
LARGE(array, n)
array: the range of data for which you want to determine the n-th largest value
n: it will determine the position from the largest value in the array
If the array is empty a #NUM! error value is returned.
If the n value is greater than the number of data in the array or if it less than or equal to zero then a #NUM! error value is returned.
The LARGE(array, 1) returns the maximum number in the array, so it is equal to the MAX(array) function.
If the total number of data points in the array are k then the function LARGE(array, k) returns the minimum value of the array so it is equal with the MIN(array) function.
Click on the button to practice using this function, with the help of our Online Assessment Tool:
Here are some examples of the possible uses of LARGE:
In the G5 cell of the SALES sheet, calculate the sum of the three largest values of column E. You will have to use an array constant.
In the cell F5 calculate the price of the third most expensive product.