Table of Contents
The MIN function in Google Sheets is a useful tool for finding the smallest value in a given range of cells. However, it may include zero values in its calculation, which can skew the results. To exclude zero values from the MIN function, you can use the following formula: “=MIN(range1, range2,…) / MIN(range1, range2,…,0)”. This will ensure that zero values are not considered in the calculation and the smallest non-zero value is returned. This can be particularly useful when working with data that includes a number of zero values, allowing for more accurate and relevant analysis.
Google Sheets: Use MIN Function and Exclude Zero
There are two common ways to use the MIN function in Google Sheets to find the minimum value in a range while excluding cells that are equal to zero:
Method 1: Use MIN & FILTER
=MIN(FILTER(B2:B15, B2:B15<>0))
Method 2: Use MINIFS
=MINIFS(B2:B15,B2:B15,"<>0")Both formulas find the minimum value in the cell range B2:B15, excluding any cells that are equal to zero.
The following examples show how to use each method in practice with the following dataset that shows the number of sales made by various employees at some company:
Example 1: Use MIN & FILTER
We can use the following formula with the MIN and FILTER functions to find the minimum value in the sales column while excluding all values equal to zero:
=MIN(FILTER(B2:B15, B2:B15<>0))
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.
This formula works by first filtering the range for values that are not equal to zero, then finding the minimum of the remaining values.
Example 2: Use MINIFS
We can use the following formula with the MINIFS functions to find the minimum value in the sales column while excluding all values equal to zero:
=MINIFS(B2:B15,B2:B15,"<>0")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.
This formula works by finding the minimum value in the range only if the value in the range is not equal to zero.
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I use the MIN function in Google Sheets while excluding zero values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-google-sheets-while-excluding-zero-values/
stats writer. "How can I use the MIN function in Google Sheets while excluding zero values?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-google-sheets-while-excluding-zero-values/.
stats writer. "How can I use the MIN function in Google Sheets while excluding zero values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-google-sheets-while-excluding-zero-values/.
stats writer (2024) 'How can I use the MIN function in Google Sheets while excluding zero values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-min-function-in-google-sheets-while-excluding-zero-values/.
[1] stats writer, "How can I use the MIN function in Google Sheets while excluding zero values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the MIN function in Google Sheets while excluding zero values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.