How To Use Excel S Small And Large Function

Use the Excel SMALL Function

The SMALL function in Excel returns k-th smallest value (where k- represents the position of the value, for example, first, second, or fifth) in a data set that you determine. You might want to know the first, third, or fifth smallest value. The purpose of this function is to return values with a particular relative standing in a data set. The SMALL function is written as SMALL(array, k) where array is the range of data you want to examine, and k is the user-defined point (for example, first, second, or fourteenth) that the function is searching for among that array of data.

Use the Excel LARGE Function

Conversely, the LARGE function in Excel returns the k-th largest value (where k- represents the position of the value, for example, first largest or fifth largest) that you determine in a data set. =SMALL(B2:D9,3) The LARGE function is written as LARGE(array, k) where array is the range of data you want to examine, and k is the user-defined point (for example, first, second, or fourteenth) the function is searching for among the data array.

Possible Errors in SMALL and LARGE Functions in Excel

Excel formulas have to be exactly right to work. If you encounter an error, here are some things to watch for: =LARGE(B2:D9,1)

If the array is empty, meaning you didn’t select cells that contain data, SMALL and LARGE functions return the #NUM! error.If k ≤ 0 or if k exceeds the number of data points within an array, SMALL and LARGE return the #NUM! error.