If you’ve ever wondered how to exclude 0 from the MIN function result in Google Sheets, you’re in luck! Today, I’m going to share with you two different methods that will allow you to return the minimum value without including 0 in your Google Doc Spreadsheets. Get ready to revolutionize your Google Sheets game!
Filter with MIN to Exclude Zero
The first method we’re going to explore is using the FILTER function in combination with MIN. This method is perfect when you have the values you want to test for MIN excluding zero properly arranged in columns, rows, or as a data range. Let me show you how it’s done:
As you can see in the image above, the MIN function alone returns 0. But fear not! We can use the FILTER function to exclude 0 and return the minimum value. Here’s the formula you need:
=MIN({FILTER(A1:A8, A1:A8<>0); FILTER(B1:B8, B1:B8<>0); FILTER(C1:C8, C1:C8<>0)})
If you only have a single column, you can simply use one FILTER formula. No curly braces needed!
Query with MIN to Exclude Zero
Now, let’s move on to the second method, which involves using the QUERY function with MIN. This method works best when the values you want to test for MIN excluding zero are scattered in different cells and columns. Check out the example below:
As you can see, the MIN formula alone returns 0 once again. But don’t fret! The QUERY function is here to save the day. Here’s the formula you need to return the minimum value without including 0:
=MIN(QUERY({A1;A3;A5;A6;A8;A9;B2;B4;B7}, "SELECT * WHERE Col1<>0"))
Formula Explanation:
The curly braces transform the scattered values into an array, essentially creating a single column. Since we can use a column in the QUERY function to filter data, this approach is straightforward and effective.
And there you have it! With these two methods, you can easily exclude 0 from the MIN function result in Google Sheets. If you have any doubts or questions about using these functions, feel free to leave a comment below. I’ll be more than happy to assist you. Happy Sheet-ing!
