Find 'Smallest', 'Largest', 'K-th Smallest' and 'K-th Largest' Numbers in a Range with Excel Functions
Related Links:
---------------------------------------------------------------------------------------------------------------------------
Find Smallest Number or K-th Smallest Number in a Range
Find Smallest Number in a Range, using the MIN Function
=MIN(A2:A11)
Using the MIN function returns the smallest number (which is -4) in the range A2:A11. (Refer Table 1)
Find Smallest Number in a Range, using the SMALL Function
=SMALL(A2:A11, 1)
Using the SMALL function to return the smallest number (which is -4) in the range A2:A11. (Refer Table 2)
SMALL function returns the k-th smallest value in a data set. Using the k-th value as 1, the function returns the smallest number.
Find Smallest Number in a Range, Omitting a Particular Number
{=MIN(IF(A2:A11<>-4, A2:A11))}
Using the combination of MIN and IF functions, to return the smallest number in a set of values (range A2:A11), ignoring the number -4. Formula returns the smallest number as 0. (Refer Table 3)
Enter =MIN(IF(A2:A11<>-4, A2:A11)) as an array formula. Type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
Please note that you can also omit a particular number with the DMIN function as below.
Find Smallest Number in a Range, per Specified Criteria, using DMIN Function
=DMIN(A1:A11,1, B1:B2)
Formula uses the DMIN function, to return the smallest number that is greater than 8 in the range A2:A11. Formula returns the number 41. (Refer Table 4)
A2:A11 is the range of cells that makes up the database from which the smallest value is determined. The first row of the database contains label for the column.
"1" in the formula, indicates which column is used in the function. It represents the position of the column within the database.
Criteria, given in range B1:B2 in the formula, is the range of cells that contains the conditions that you specify and includes at least one column label. In this formula, the column A label "Numbers" is copied exactly to cell B1.
Find the K-th Smallest Value in a Range, using SMALL Function
=SMALL(A2:A11,6)
Formula uses the SMALL function, and returns the 6th position (from the smallest) in the range A2:A11. It returns the value 3. (Refer Table 5)
Please note that in case of multiple equal values in the range, each is counted separately to determine the position. In this example, the first five smallest numbers are: -4, 0, 2, 2 & 3, and the 6th smallest is also 3 (same as 5th smallest), hence the formula returns 3.
=SMALL({22,19, 14,5,8,44, 24,4},4)
Formula returns the value 14, which is the 4th smallest value in the array.
--------------------------------------------------------------------------------------------------------------------------
Find Largest Number or K-th Largest Number in a Range
Find Largest Number in a Range, using the LARGE Function
=LARGE(A2:A11, 1)
Using the LARGE function to return the largest number (which is 44) in the range A2:A11. (Refer Table 6)
LARGE function returns the k-th largest value in a data set. Using the k-th value as 1, the function returns the largest number.
Find Largest Number in a Range, using the MAX Function
=MAX(A2:A11)
Using the MAX function returns the largest number (which is 44) in the range A2:A11.
Find Largest Number in a Range, Omitting a Particular Number
{=MAX(IF(A2:A11<>44, A2:A11))}
Using the combination of MAX and IF functions, to return the largest number in a set of values (range A2:A11), ignoring the number 44. Formula returns the largest number as 41. (Refer Table 7)
Enter =MAX(IF(A2:A11<>44, A2:A11)) as an array formula. Type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
Please note that you can also omit a particular number with the DMAX function as below.
Find Largest Number in a Range, per Specified Criteria, using DMAX Function
=DMAX(A1:A11,1,B1:B2)
Formula uses the DMAX function, to return the largest number that is less than 42 in the range A2:A11. Formula returns the number 41. (Refer Table 8)
A2:A11 is the range of cells that makes up the database from which the smallest value is determined. The first row of the database contains label for the column.
"1" in the formula, indicates which column is used in the function. It represents the position of the column within the database.
Criteria, given in range B1:B2 in the formula, is the range of cells that contains the conditions that you specify and includes at least one column label. In this formula, the column A label "Numbers" is copied exactly to cell B1.
Find the K-th Largest Value in a Range, using LARGE Function
=LARGE(A2:A11, 6)
Formula uses the LARGE function, and returns the 6th position (from the largest) in the range A2:A11. It returns the value 3. (Refer Table 9)
Please note that in case of multiple equal values in the range, each is counted separately to determine the position. In this example, the first five largest numbers are: 44, 41, 41, 8 & 3, and the 6th largest is also 3 (same as 5th largest), hence the formula returns 3.
=LARGE({22, 19,14,5, 8,44,24,4}, 4)
Formula returns the value 19, which is the 4th largest value in the array.
--------------------------------------------------------------------------------------------------------------------------
N Smallest Values and N Largest Values in a range
Find the n smallest values in a range
{=SMALL(A2:A11, {1;2;3})}
Formula returns an array of 3 smallest values in the range A2:A11. The 3 smallest values returned are: -4,0,2. (Refer Table 10)
Enter =SMALL(A2:A11, {1;2;3}) as an array formula. Select 3 adjoining cells in a column and type the formula (in the Formula Bar), then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
Changing the formula to =SMALL(A2:A11, {3;4;5}) will return the 3 smallest values, starting from 3rd smallest (ie. ignoring 2 smallest values of -4 and 0). Formula will return: 2,2,3.
Sum of the n smallest values in a range
=SUM(SMALL(A2:A11, {1;2;3}))
Formula returns the value -2, which is the sum of 3 smallest values in the range A2:A11 viz. (-4 + 0 + 2). (Refer Table 11)
Average of the n smallest values in a range
=AVERAGE(SMALL(A2:A11, {1,2,3}))
Formula returns the value -0.67, which is the average of 3 smallest values in the range A2:A11 viz. (-4 + 0 + 2)/3.
Find the n largest values in a range
{=LARGE(A2:A11, {1;2;3})}
Formula returns an array of 3 largest values in the range A2:A11. The 3 largest values returned are: 44, 41 & 41. (Refer Table 12)
Enter =LARGE(A2:A11, {1;2;3}) as an array formula. Select 3 adjoining cells in a column and type the formula (in the Formula Bar), then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
Changing the formula to =LARGE(A2:A11, {3;4;5}) will return the 3 largest values, starting from 3rd largest (ie. ignoring 2 largest values of 44 and 41). Formula will return: 41,8,3.
{=LARGE(A2:A11, ROW( INDIRECT("1:3")))}
Formula returns an array of 3 largest values in the range A2:A11. The 3 largest values returned are: 44, 41 & 41. (Refer Table 13)
Enter =LARGE(A2:A11, ROW(INDIRECT("1:3"))) as an array formula. Select 3 adjoining cells in a column and type the formula (in the Formula Bar), then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces {}.
ROW function creates an array of consecutive integers. Entering =ROW(1:3) as an array formula will return an array of {1,2,3} in 3 consecutive cells of a column. However, this will present a problem if a row is inserted above the referenced range. Excel will then adjust the row references, and the formula will generate integers from 2 to 4. To address this problem, the INDIRECT function is used.
If you always want to refer to the specific array {1,2,3}, use the following syntax: =INDIRECT("1:3"). INDIRECT function returns a set of text values 1 to 3. Text values are not changed in excel if rows or columns are inserted or deleted.
Find n largest values, working with entire column
{=LARGE(A:A, ROW(INDIRECT("1:"& COUNTA(A:A))))}
=COUNTA(A:A)) counts the number of cells that are not empty. Therefore, the function INDIRECT("1:"& COUNTA(A:A)) will behave as INDIRECT("1:10") in this formula.
Enter =LARGE(A:A,ROW(INDIRECT("1:"& COUNTA(A:A)))) as an array formula, by selecting same number of cells in a column as the required number of largest values.
Formula returns the largest 3 values (ie. 150,112,111), because 3 cells in the column were selected for the array formula. (Refer Table 14)
{=LARGE(A:A, ROW(INDIRECT("3:"& COUNTA(A:A))))}
Formula is similar to above, except that in this case Largest values start from 3rd Largest.
Enter =LARGE(A:A,ROW(INDIRECT("3:"&COUNTA(A:A)))) as an array formula, by selecting same number of cells in a column as the required number of largest values.
Formula returns the largest 3 values starting from the 3rd largest (ie. 111, 100,99). 3 cells in the column were selected for the array formula. (Refer Table 15)
Find n largest values, using criteria
{=LARGE(IF(B1:B10="Red" , A1:A10,""), {1;2;3})}
Formula returns Largest values in a range using criteria. Referenced range is values in A1:A10 only those where corresponding cells in column B contain "Red" (viz. 3,5,112,150).
Enter =LARGE(IF(B1:B10="Red", A1:A10,""), {1;2;3}) as an array formula, by selecting 3 cells in a column to return the 3 largest values.
Formula returns the 3 largest values in column A viz. 150,112,5, considering only those column A values wherein the corresponding values in column B are "Red". (Refer Table 16)
Average of n largest values
{=AVERAGE(LARGE(A:A, ROW(INDIRECT("1:"& COUNTA(A:A)-2))))}
Formula returns Average of largest values in entire column A, excluding 2 smallest values ie. meaning average of 8 largest values. Returned average is 97.13. (Refer Table 17)
Enter =AVERAGE(LARGE(A:A, ROW(INDIRECT("1:"& COUNTA(A:A)-2)))) as an array formula in a single cell.
{=AVERAGE(LARGE( A1:A10, ROW(INDIRECT("1:8"))))}
Formula returns Average of The Largest N Numbers In A Range. N=8 in this example. Returned average of the largest 8 values in range A1:A10 is 97.13.
Enter =AVERAGE(LARGE( A1:A10, ROW(INDIRECT("1:8")))) as an array formula in a single cell.
To find the Average of The Smallest N Numbers In A Range replace "LARGE" with "SMALL": {=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:8"))))}.
{=AVERAGE(LARGE(A:A, ROW(INDIRECT("1:" & COUNTA(A:A)/4))))}
Formula returns Average of Largest 25% values in column A. Ensure that =COUNTA(A:A)/4 returns an integer, else formula will give an error. Returned average is 167.67.
Enter =AVERAGE(LARGE(A:A, ROW(INDIRECT("1:" & COUNTA(A:A)/4)))) as an array formula in a single cell.
To find the average of largest 50% of values in a range, use: {=AVERAGE(LARGE(A:A, ROW(INDIRECT("1:" & COUNTA(A:A)/2))))}.
{=AVERAGE(LARGE(A:A, ROW(INDIRECT("1:"& COUNTA(A:A)))))}
Formula returns Average of largest values in column A ie. meaning average of values in entire column. Returned average value is 78.5.
Enter =AVERAGE(LARGE(A:A, ROW(INDIRECT("1:"& COUNTA(A:A))))) as an array formula in a single cell.
--------------------------------------------------------------------------------------------------------------------------
Cell Address of Largest or N Largest Values in a Range
Cell Address of Largest or Maximum Value in a Range
=CELL("ADDRESS", INDEX(A:A, MATCH(MAX(A:A), A:A,0)))
Formula returns Cell Address of the Largest Value in column A, which is $A$10. In case of more than one largest value, cell address of the largest value appearing first in the column is returned. (Refer Table 18)
Formula Parts explained:
=MAX(A:A): This part of the formula returns the largest value in column A, which is 150.
=MATCH(MAX(A:A), A:A,0): This part of the formula returns the row number (ie. 10) of the largest value in column A.
=INDEX(A:A,MATCH(MAX(A:A), A:A,0)): This part of the formula returns the value in the row number determined above.
=CELL("ADDRESS", INDEX(A:A, MATCH(MAX(A:A), A:A, 0))): The cell function returns information about the address of the cell in reference. The entire formula returns cell Address of the Largest Value in column A, which is $A$10.
=ADDRESS(MATCH(MAX(A:A), A:A,0), 1,4)
Formula returns Cell Address of the Largest Value in column A, which is A10. In case of more than one largest value, cell address of the largest value appearing first in the column is returned. (Refer Table 19)
Formula Parts explained:
=MAX(A:A): This part of the formula returns the largest value in column A, which is 150.
=MATCH(MAX(A:A), A:A,0): This part of the formula returns the row number (ie. 10) of the largest value in column A.
=ADDRESS(MATCH(MAX(A:A), A:A,0), 1,4): The full formula returns cell Address of the Largest Value in column A, which is $A$10. It takes the row number as calculated by the MATCH function above ie. 10. The column number is entered as "1".
Please NOTE, that the column number will be entered as "2" if the range is B:B, and "3" if the range is "C:C", and so on. The value "4" in the formula indicates relative reference will be returned by the ADDRESS function (ie. formula will return "A10" and not "$A$10".
Cell Address of n Largest Values in a Range
{=CELL("ADDRESS", INDEX(A1:A10, MATCH(LARGE(A1:A10, {1;2;3}), A1:A10, 0)))}
Formula returns Cell Address of the 3 Largest Values in range A1:A10 (which are $A$10, $A$9, $A$1). (Refer Table 20)
Enter =CELL("ADDRESS", INDEX(A1:A10, MATCH(LARGE(A1:A10, {1;2;3}), A1:A10,0))) as an array formula, by selecting 3 cells in a column to return the 3 largest values.
Formula Parts are similar to the above formula(s), wherein they have been explained.
{=CELL("ADDRESS", INDEX(A1:A10, MATCH(LARGE(A1:A10, ROW(INDIRECT("1:3"))), A1:A10, 0)))}
Formula returns Cell Address of the 3 Largest Values in range A1:A10 (which are $A$10, $A$9, $A$1). (Refer Table 21)
Enter =CELL("ADDRESS", INDEX(A1:A10, MATCH(LARGE(A1:A10, ROW(INDIRECT("1:3"))), A1:A10, 0))) as an array formula, by selecting 3 cells in a column to return the 3 largest values.
Formula Parts are similar to the above formula(s), wherein they have been explained.
