The following article introduces you to the LARGE function - one of the functions in the statistical function group is very popular in Excel.
Description: The function returns the kth value in the dataset. Use the function to determine the position of the value in a dataset.
Syntax: LARGE (array, k)
Inside:
- array : Array or range of data containing the kth largest value in it.
- k: What is the largest position (the largest ad is 1) in the array to return.
Attention:
- If the argument in the array is a blank -> the function returns the #NUM! Error value .
- If k ≤ 0 or k is greater than the number of data points in the array -> the function returns the #NUM! Error value
- If n is the number of data points of the array, then:
+ LARGE (array, 1) -> returns the largest value in the array array.
+ LARGE (array, n) -> returns the smallest value in the array array.
For example:
Find the kth largest value as described in the data table below:
1. Calculate the 3rd and smallest largest values in array1
- Calculate the 3rd largest value in array1. In a cell to calculate enter the formula : = LARGE (C6: C10,3)
- Press Enter -> the 3rd largest value in array1 is:
- Calculate the smallest value in array1. Using the LARGE up the smallest value in the array is the largest value in the array n where n is the total number of elements in the array -> enter the formula: = LARGE (C6: C10,5) ( array array1 5 parts death) -> press Enter -> minimum values are:
2. Calculate the 2nd and 3rd largest values in array2.
- Because array2 has 2 equal elements of 6. Two equal values but the function still ranks them in 2 different positions:
- Calculate the second largest value in the array -> enter the formula: = LARGE (D6: D10,2)
- Press Enter -> the second largest value is:
- 3rd largest value in the array -> enter the formula: = LARGE (D6: D10,3) -> press Enter -> 3rd largest value is:
Thus the 2nd and 3rd largest values in the same array are 6.
Above are instructions and some specific examples when using the LARGE function in Excel.
Good luck!