Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use MIN Function to Exclude Zero in Excel (5 Easy Ways)

Occasionally, we need to find the minimum value in a dataset or an array. For this purpose, we can easily insert the MIN function in the formula. But, in some cases, we have to or want to neglect the zero values. Naturally, this function will return zero from a dataset consisting of positive numbers, that’s the smallest of all. So, it becomes a bit tricky to hide zero values while using this function. Here, in this article, we’ll demonstrate 5 easy and quick ways on how to use the MIN function to exclude zero in Excel. So, let’s go through the entire article to understand the topic properly.

how to use min function in excel exclude zero


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


5 Methods to Use MIN Function to Exclude Zero in Excel

For ease of understanding, we are going to use an “Inventory List of ABC Technology”. This dataset includes the Product ID, Product Name, and Units Remaining in columns B, C, and D respectively.

Dataset of how to use min function in excel exclude zero

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll use this dataset to find the minimum value excluding zero using the MIN function in Excel. And we’ll do it in a variety of ways. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


1. Using MIN and IF Functions

The first one should be a little easier, right? For this reason, we are starting with the simplest yet most effective way to find the minimum value excluding zero. So, allow me to demonstrate the process below.

📌 Steps:

  • At the very beginning, create an output field in cells in the B16:D16 range.

Creating output range in Excel

  • Then, select cell D16 and enter the following formula.
=MIN(IF(D5:D14<>0,D5:D14))

Formula Breakdown

  • IF(D5:D14<>0,D5:D14) → the IF function checks whether a condition is met, and returns one value if TRUE, and another one if FALSE. Here, the logical_test is D5:D14<>0 and the value_if_true argument is D5:D14. So, it means that if the value of each cell in the D5:D14 range doesn’t equal to 0, then the function will return the value. Otherwise, it will return FALSE.
    • Output → {8, 10, 6, FALSE, 5, 2, 8, 3, FALSE, 5}
  • MIN(IF(D5:D14<>0,D5:D14)) becomes MIN(8, 10, 6, FALSE, 5, 2, 8, 3, FALSE, 5).
  • MIN(8, 10, 6, FALSE, 5, 2, 8, 3, FALSE, 5) → the MIN function extracts the lowest or smallest value from a range of cells or cell references.
    • Output → 2

  • After that, press the ENTER key.

using MIN and IF functions to exclude zero in excel

Read More: How to Use Combined MIN and IF Function in Excel (2 Easy Ways)


2. Utilizing MIN, IF, and MAX Functions

In this method, we’ll do the same as the previous one but add another new function here. So, just follow these simple steps properly.

📌 Steps:

  • Firstly, go to cell D16 and insert the bit complicated formula below.
=MIN(IF(D5:D14=0,MAX(D5:D14),D5:D14))

Formula Breakdown

  • MAX(D5:D14) → the MAX function returns the largest value in a given list of arguments.
    • Output → 10
  • IF(D5:D14=0,MAX(D5:D14),D5:D14) becomes IF(D5:D14=0,10,D5:D14).
  • IF(D5:D14=0,10,D5:D14) → here, the IF function returns 10, where a cell value is equal to 0. Otherwise, it returns the same value.
    • Output → {8, 10, 6, 10, 5, 2, 8, 3, 10, 5}
  • MIN(IF(D5:D14=0,MAX(D5:D14),D5:D14)) becomes MIN(8, 10, 6, 10, 5, 2, 8, 3, 10, 5).
  • MIN(8, 10, 6, 10, 5, 2, 8, 3, 10, 5) → it returns the smallest among all the numbers here.
    • Output → 2

  • Later, hit ENTER.

Utilizing MIN, IF, and MAX Functions to exclude zero in excel

Read More: Min Max Inventory Calculation in Excel (with Easy Steps)


3. Incorporating MIN, NOT, and MAX Functions

We have used the IF function in the previous two methods. This time, we won’t use that. Rather, we’ll add another common function to our formula. Let’s see it in action.

📌 Steps:

  • At first, select cell D16 and write down the exact formula given below.
=MIN(NOT(D5:D14)*MAX(D5:D14)+D5:D14)

Formula Breakdown

  • MAX(D5:D14) → the MAX function returns the largest value in a given list of arguments.
    • Output → 10
  • NOT(D5:D14) → the NOT function reverses (opposite of) a Boolean or logical value. In simple terms, if you enter TRUE, the function returns FALSE, and vice versa.
    • Output → {FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}
  • NOT(D5:D14)*MAX(D5:D14) becomes {FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}*10
    • Output → {0, 0, 0, 10, 0, 0, 0, 0, 10, 0}
  • NOT(D5:D14)*MAX(D5:D14)+D5:D14 becomes {0, 0, 0, 10, 0, 0, 0, 0, 10, 0}+{8, 10, 6, 0, 5, 2, 8, 3, 0, 5}.
    • Output → {8, 10, 6, 10, 5, 2, 8, 3, 10, 5}
  • MIN(NOT(D5:D14)*MAX(D5:D14)+D5:D14) becomes MIN(8, 10, 6, 10, 5, 2, 8, 3, 10, 5).
    • Output → 2

  • Next, tap ENTER.

Incorporating MIN, NOT, and MAX Functions to exclude zero in Excel

Read More: Difference Between MAX and MIN Function in Excel


4. Applying MIN with FILTER Function

At this time, we’ll combine the MIN and FILTER functions within a formula. But, for your kind information, the FILTER function is only available in Excel 365 and Excel 2021 versions. So, you can only enjoy this feature if these versions are available to you. Now, let’s start the procedure.

📌 Steps:

  • Firstly, go to cell D16 and write down the formula below.
=MIN(FILTER(D5:D14,D5:D14<>0))

The FILTER function filters particular cells or values according to the requirements. Here, it is: the cell couldn’t be equal to zero. The remaining part of this formula is just like the formula we used in Method 1.

  • Secondly, press the ENTER key.

Applying MIN with FILTER Function to exclude zero in Excel


5. Implementing INDEX-MATCH Formula with MIN Function

The formula in this method is the toughest in this article, we feel. But we have tried to make it as straightforward as possible through the proper explanation. So, let’s begin.

📌 Steps:

  • Primarily, go to cell D16 and put down the following formula.
=INDEX(D5:D14,MATCH(MIN(IF(D5:D14<>0,D5:D14)),D5:D14,0))

Formula Breakdown

  • MIN(IF(D5:D14<>0,D5:D14)) → this part is same like the Method 1.
    • Output → 2
  • MATCH(MIN(IF(D5:D14<>0,D5:D14)),D5:D14,0)) becomes MATCH(2,D5:D14,0)).
  • MATCH(2,D5:D14,0)) → the MATCH function returns the lookup_value’s relative position. 2 is the lookup_value, D5:D14 is the lookup_array, and 0 for exact match_type.
    • Output → 6
  • INDEX(D5:D14,MATCH(MIN(IF(D5:D14<>0,D5:D14)),D5:D14,0)) becomes INDEX(D5:D14,6).
  • INDEX(D5:D14,6) → the INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
    • Output → 2

  • As usual, press ENTER.

Implementing INDEX-MATCH Formula with MIN Function to exclude zero in Excel

Read More: How to Calculate Average, Minimum And Maximum in Excel


How to Use Min Function to Exclude Zero Using VBA in Excel

Tired of using formulas in Excel? Then you’re in luck because I’ll show you how you can automate the process within a few clicks. We’ll use a VBA macro for this purpose. So, just stick with us till the last.

📌 Steps:

  • Initially, navigate to the Developer tab.
  • Then, click on Visual Basic on the Code group of commands.

opening VBE from developer tab in Excel

Immediately, the Microsoft Visual Basic for Applications window pops up before us.

  • Currently, proceed to the Insert tab.
  • Then, select Module from the options.

Inserting Module in VB editor in Excel

Instantly, it’ll add a code module on the right side of the display.

  • Quickly, paste the following scripts in Module1.
Sub min_Exclude_zero()
MsgBox "The minimum value excluding zero is " & [min(if(D:D<>0,D:D))], vbOKOnly, "ExcelDemy"
End Sub

VBA code to Use Min Function to Exclude Zero in Excel

  • After that, save the file as an Excel Macro-Enabled Workbook.
  • Later, return to the VBA worksheet.
  • Again, go to the Developer tab.
  • Next, click on Macros in the Code group.

Bringing Macro dialog box in Excel

Suddenly, the Macro dialog box appears before us.

  • Here, select the only available macro we just created.
  • Then, click on the Run button.

Calling macro

As a result, a MsgBox will show up and show us the correct result.

msgbox showing result of using Min function exclude zero in Excel

Compare the value with the previous methods for assurance.

Read More: How to Find Minimum Value That Is Greater Than 0 in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Practice section


Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet on how to use the MIN function to exclude zero in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo