Table of Contents
The MIN function in Excel allows users to find the smallest value in a range of cells. This can be useful when analyzing data or finding the minimum value in a set of numbers. However, if the range of cells includes cells that contain a zero, the MIN function will include these cells in the calculation. To avoid this, users can use the MIN function in combination with the IF function to exclude any cells that contain a zero. By setting a condition for the IF function to only include cells with a value greater than zero, the MIN function will only consider these cells in finding the smallest value. This feature can be beneficial in data analysis, as it allows for a more accurate calculation of the minimum value without considering any irrelevant zero values.
Excel: Use MIN Function and Exclude Zero
You can use the following basic syntax to use the MIN function in Excel to find the minimum value in a range while excluding cells that are equal to zero:
=SMALL(B2:B15,COUNTIF($B$2:$B$15,0)+1)
This particular formula finds the minimum value in the cell range B2:B15, excluding any cells that are equal to zero.
This formula works by first using COUNTIF() to count the number of zeros and adding one, then using the SMALL() function to return the kth smallest value not equal to zero.
The following example shows how to use this formula in practice.
Example: How to Use MIN Function and Exclude Zero in Excel
Suppose we have the following dataset in Excel that shows the number of sales made by various employees at some company:
We can use the following formula to find the minimum value in the sales column while excluding all values equal to zero:
=SMALL(B2:B15,COUNTIF($B$2:$B$15,0)+1)
We’ll type this formula into cell D2 and then press Enter:
From the output we can see that the minimum value in the sales column (excluding all values equal to zero) is 2.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the MIN function in Excel to find the smallest value in a range of cells, excluding any cells that contain a zero?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-excel-to-find-the-smallest-value-in-a-range-of-cells-excluding-any-cells-that-contain-a-zero/
stats writer. "How can I use the MIN function in Excel to find the smallest value in a range of cells, excluding any cells that contain a zero?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-excel-to-find-the-smallest-value-in-a-range-of-cells-excluding-any-cells-that-contain-a-zero/.
stats writer. "How can I use the MIN function in Excel to find the smallest value in a range of cells, excluding any cells that contain a zero?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-excel-to-find-the-smallest-value-in-a-range-of-cells-excluding-any-cells-that-contain-a-zero/.
stats writer (2024) 'How can I use the MIN function in Excel to find the smallest value in a range of cells, excluding any cells that contain a zero?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-excel-to-find-the-smallest-value-in-a-range-of-cells-excluding-any-cells-that-contain-a-zero/.
[1] stats writer, "How can I use the MIN function in Excel to find the smallest value in a range of cells, excluding any cells that contain a zero?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the MIN function in Excel to find the smallest value in a range of cells, excluding any cells that contain a zero?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.