HomeGoogle DocsSpreadsheetHow to Exclude 0 From MIN Function Result in Google Sheets

How to Exclude 0 From MIN Function Result in Google Sheets

Published on

If you ask me what is the solution to exclude 0 from MIN function result in Google Sheets, my answer would be either by using a Filter function or a QUERY function together with MIN. The MIN formula alone can’t perform this task.

Here in this quick Google Sheets tips, I’m detailing the above two different methods to return minimum value that without 0 in Google Doc Spreadsheets.

Steps to Exclude 0 From MIN Function Result in Google Sheets

As I told you above, there are two different options. Let’s begin with Filter with MIN.

Filter with MIN to Exclude Zero

When you have the values to test for MIN excluding zero is properly arranged in Columns, Rows or as a data range, you can use the FILTER function with MIN.

Filter with MIN to Exclude Zero

Obviously, the above MIN function is returning 0. So here is the formula using Filter to return MIN value excluding 0.

I’m going to filtered out 0 from each column and then join them vertically, i.e., data of one column under another, and use the MIN function as usual then. See that formula below.

=min({filter(A1:A8,A1:A8<>0);filter(B1:B8,B1:B8<>0);filter(C1:C8,C1:C8<>0)})

If there is only a single column, you can avoid using Curly Braces. One Single Filter formula is enough for them.

Query with MIN to Exclude Zero

When the values to test for MIN excluding 0 are scattered in different cells and columns, the possibly best option is to use Query with MIN.

Query with MIN to Exclude Zero

The above MIN formula would, of course, return the result 0. Below is the Query alternative to returning minimum value without 0 from the scattered values in Google Sheets.

=min(query({A1;A3;A5;A6;A8;A9;B2;B4;B7},"select * where Col1<>0"))

Formula Explanation:

The curly braces make the scattered values as an array means a single column. As you know, we can use a column in Query function as data to filter. Hope you can read the above formula as it’s a straightforward approach.

This way you can exclude 0 from MIN function result in Google Sheets. If you have any doubt about using the above functions, please do post in comments below. I will find time to answer your questions. That’s all for now.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

4 COMMENTS

  1. One other thing I left out. Another thing I need to do is, once the array finds the lowest value excluding zero, I need it to return the name of the lowest value. Same for the second, third, and lowest overall.

    Thank you,
    Nuria

    • Hi, Nuria Anderson,

      I think I can help you here.

      We can use Query or ArrayConstrain+Sort. It depends on your data.

      Can you prepare and share a sample sheet? So that I can write the formula and share the steps on the sheet.

      You can leave the URL via “Reply” to this comment (I won’t publish it).

  2. I found your answers to be very helpful when other places were of no help whatsoever. I need an additional step, however.

    My data is in every other column (think the description in one and pricing on the other, so the numbers are in every other column). I need to find the first lowest excluding zero, which I have done with the Query.

    Thank you for that, by the way!

    Now in the same array, I need to find the second-lowest, third lowest, and overall lowest number.

    How do I do that?

    I appreciate your insight on this.

    Thanks again!

LEAVE A REPLY

Please enter your comment!
Please enter your name here