This article shows you that how to find nth largest value by using the LARGE function.
Syntax
=LARGE(absolute data range reference, nth)
Steps
- Start with =LARGE( function
- Select the range that contains the values $B$2:$G$7,
- Select the cell that contains nth value or you can manually enter the value I3
- Type ) to finish and complete the formula.
How
The LARGE function does exactly what the title of this article says: it returns the nth largest value in a data table. The LARGE function has two arguments that is array and n value which stand for the range of values and the position from the largest respectively.
=LARGE($B$2:$G$7,I3)
The function returns #NUM! under these circumstances:
- If array is empty
- If n is greater that the number of data points. For example; if the array has 5 items, however you try to get 10th item.
- If n <= 0. It should be 1 at least.
Also refer to a related article how to highlight the top values in a data set dynamically. This article also uses LARGE function to identify the largest values in data table and highlights them using conditional formatting.
Alternatively, you can use the MAX function to find the largest values.