Find the Smallest Non-Zero Value in Excel

Do you need to find the smallest value but want to skip over any zero values? In this post, I’ll show you how you can do just that. The MIN function in Excel does a good job of finding the lowest value but if you have any zero values, those will get included in the result. So how can you get around the issue and get the smallest non-zero value? There are multiple ways you can accomplish this.

Using an array function

An array function allows you to evaluate each cell and that can tell the function whether to include it in its calculation. In the following example, I have a list of the numbers from 0 through to 10:

By just using the MIN function on that entire range, I will get a value of 0 as the result. However, you can get around this by embedding an IF statement within your MIN function and then turning the formula into an array. The regular MIN function would look as follows:

=MIN(A1:A11)

The IF statement will look to see if a value is not equal to 0 by using the “<>” operators:

=IF(A1:A11<>0,A1:A11)

The IF function evaluates every cell and if it’s not equal to 0, then it returns the value. Here’s how it looks embedded within the IF statement:

=MIN(IF(A1:A11<>0,A1:A11))

The result of this formula is a 1, as it will ignore any 0 values. If you’re running an older version of Excel that doesn’t calculate this correctly, you will need to use CTRL+SHIFT+ENTER to convert this into an array formula and so that you have the curly braces around it as such:

{=MIN(IF(A1:A11<>0,A1:A11))}

Using the LARGE function

Another way you can calculate the smallest non-zero number is by using the LARGE function. Technically, this is used when you want to find the largest values in a range. For instance, if I wanted to get the largest number in range A:A, my formula would be as follows:

=LARGE(A:A,1)

This is the same as if I were to use the MAX function. However, if I know there are 10 items in my range and I grab the 10th largest, that’s the same as saying I want to extract the smallest number in the range.

The key to making this work is using the COUNTIF function. With the COUNTIF, I can count the number values that are greater than zero in a range. This formula would accomplish that:

=COUNTIF(A:A,”>0″)

If I include that in my LARGE function, it will look like this:

=LARGE(A:A, COUNTIF(A:A,”>0″) )

An array calculation is not needed here since I am not evaluating every cell. And this formula arrives at the same result as the earlier method.

If you liked this post on How to Find the Smallest Non-Zero Value in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.