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

Get FREE Advanced Excel Exercises with Solutions!

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 to Exclude Zero in Excel: 5 Methods

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. 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.

### 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. • 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. ### 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. Read More: How to Find Minimum Value in Excel

### 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. ### 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. ### 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. Read More: How to Find Minimum Value Based on Multiple Criteria 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. Immediately, the Microsoft Visual Basic for Applications window pops up before us.

• Currently, proceed to the Insert tab.
• Then, select Module from the options. 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`````` • After that, save the file as an Excel Macro-Enabled Workbook.
• Again, go to the Developer tab.
• Next, click on Macros in the Code group. Suddenly, the Macro dialog box appears before us.

• Here, select the only available macro we just created.
• Then, click on the Run button. As a result, a MsgBox will show up and show us the correct result. Compare the value with the previous methods for assurance.

## 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. You may download the following Excel workbook for better understanding and practice yourself.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  