In Excel, you can use the LARGE function to find the K-th largest value from a dataset based on its relative standing.
Formula:
= LARGE(Array, K)
Explanations:
– The array is required; the data array or range which you need to determine the K-th largest value;
– K is required; the position from the largest in the data range.
Cautions:
K is in the range from 1 (largest) to n (smallest), where n is the number of data points. Otherwise, if k < 1 or if k is greater than the number of data points, the LARGE function returns the #NUM! error.
If the array is empty, the LARGE function returns the #NUM! error value.
Example 1: What is the 5th largest income?
= LARGE(C2:C9,5)
The 5th largest income is $70,256.
Example 2: What is the largest income?
= LARGE(C2:C9,1)
The largest income is $79,058.
Example 3: What is the 20th largest income?
= LARGE(C2:C9,20)
There are 8 persons in the example, so the 20th largest income does not exist and returns the #NUM! error.
