The Excel LARGE function returns the k-th largest value from a data set. For example, the LARGE function with a k-factor = 5 will return the 5th largest number from a list.
The LARGE function is useful in interactive Excel dashboards because it gives a quick way to create “top 10 lists” that dynamically adjust based on the data. A helpful feature of the LARGE function is that it works with unsorted data.
The LARGE function has a couple of other tricks: when used with the parameter 1 it will act like the Excel MAX function, when combined with the Excel COUNT function it can mimic the Excel MIN function and the LARGE function is also an array function so it can be used to perform some neat Excel array formula tricks.
Read More: How To Use The LEFT Function In Excel
Excel LARGE Function: How It Works
The Excel LARGE function has two parameters: a data set in the form of the array and the “k factor” which is where the item would appear in a sorted list. For example a k-factor of 1 would return the largest number in the data set, a k-factor of 2 would return the second largest number in the data set and so forth.
LARGE Function vs. MAX Function
The Excel LARGE function can be used to simulate the MAX function. By using a k-factor = 1 the function will return the largest value in the data set. In practice I would use the MAX function over the LARGE function however I present this as a way of thinking about the interchangeability of Excel functions and to provide a basis for using the large function to mimic the MIN function.
LARGE Function vs. MIN Function
The Excel LARGE function can be used to return the minimum value in a data set, simulating the MIN function. The trick is to use the Excel COUNT function to set the k-factor to be the number of rows in the data set.
