Excel Minimum Formula Excluding 0/Zero

Let’s see in this post how we could exclude 0 in calculating minimum in excel.

By default as you know the MIN formula in excel will take 0 as minimum (if negative values are not there), and we want the MIN formula to return non 0 minimum value in excel.

If there are zero’s in our range in which we want to find minimum value, 0 will be returned.




Below is the data in which we want to find the minimum value in range A1:E1 and as you could see we have 0 in the range and by applying a simple Min formula to find the minimum will give us 0, so to avoid that we would use an array formula with a combination of Min and IF to prevent excel from returning 0 as the min result.

minimum formula excluding 0/zero in excel

Enter the formula =MIN(IF(A1:E1>0,A1:E1)) in cell B3 and press Ctrl & Shift while pushing ENTER as it is an array formula.

excel formula showing how to exclude 0




This array formula will not take 0 into consideration while calculating the min in excel.

You could see the formula is wrapped in curly braces that indicates it is an array formula.

As you can see, our formula has returned 3 as minimum and not 0.

Hope this helped.




Share The Knowledge

Random Posts

Leave a Reply