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.
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.
- Then, select cell D16 and enter the following formula.
=MIN(IF(D5:D14<>0,D5:D14))- 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.
Read More: How to Use Combined MIN and IF Function in Excel
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.
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.
Read More: How to Find Lowest Value with Criteria 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.
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.
- Later, return to the VBA worksheet.
- 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.
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.
Download Practice Workbook
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.
Related Articles
- How to Find Lowest 3 Values in Excel
- How to Find Minimum Value with VLOOKUP in Excel
- Excel MIN Function Returns 0
<< Go Back to Excel MIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
