Table of Contents
The SMALL IF function in Excel allows users to find the nth smallest value in a range of data that meets a specific criteria. This function is useful for analyzing data and identifying the top or bottom values that meet a certain condition. For example, it can be used to find the top 3 highest sales for a particular product or the bottom 5 lowest grades in a class. To use the SMALL IF function, users must specify the range of data, the nth smallest value to be returned, and the criteria to be met. Some examples of using the SMALL IF function include finding the smallest value in a range of data that is greater than a certain number, or finding the smallest value in a range of data that falls within a specific date range. This function is a useful tool for data analysis and can help users quickly identify important information within a large set of data.
Use SMALL IF Function in Excel (With Examples)
You can use the following formulas to perform a SMALL IF function in Excel:
Formula 1: SMALL IF with One Criteria
=SMALL(IF(A2:A16="A",C2:C16),2)
This formula finds the 2nd smallest value in C2:C16 where the value in A2:A16 is equal to “A”.
Formula 2: SMALL IF with Multiple Criteria
=SMALL(IF((A2:A16="A")*(B2:B16="Forward"),C2:C16),2)
This formula finds the 2nd smallest value in C2:C16 where the value in A2:A16 is equal to “A” and the value in B2:B16 is equal to “Forward”.
The following examples show how to use each formula in practice with the following dataset in Excel:
Example 1: SMALL IF with One Criteria
We can use the following formula to find the 2nd smallest value in C2:C16 where the value in A2:A16 is equal to “A”:
=SMALL(IF(A2:A16="A",C2:C16),2)
The following screenshot shows how to use this formula:
This tells us that the 2nd smallest points value among all players on team A is 5.
Example 2: SMALL IF with Multiple Criteria
We can use the following formula to find the 2nd smallest value in C2:C16 where the value in A2:A16 is equal to “A” and the value in B2:B16 is equal to “Forward”:
=SMALL(IF((A2:A16="A")*(B2:B16="Forward"),C2:C16),2)
The following screenshot shows how to use this formula:
This tells us that the 2nd smallest points value among all Forwards on team A is 10.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the SMALL IF function in Excel? Can you provide some examples?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-small-if-function-in-excel-can-you-provide-some-examples/
stats writer. "How can I use the SMALL IF function in Excel? Can you provide some examples?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-small-if-function-in-excel-can-you-provide-some-examples/.
stats writer. "How can I use the SMALL IF function in Excel? Can you provide some examples?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-small-if-function-in-excel-can-you-provide-some-examples/.
stats writer (2024) 'How can I use the SMALL IF function in Excel? Can you provide some examples?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-small-if-function-in-excel-can-you-provide-some-examples/.
[1] stats writer, "How can I use the SMALL IF function in Excel? Can you provide some examples?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SMALL IF function in Excel? Can you provide some examples?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.