How to find the minimum value in range while ignoring 0 value in Microsoft Excel

To find the minimum numerical value in range while ignoring the 0 value, we can use the Small function along with Countif function in Microsoft Excel. 

SMALL: - This function is used to return the k^th smallest value in a data set.
 

 
Example: Column A contains a list of numbers from which we need to find out the  smallest number.

• Select the cell B1.
• Write the formula =SMALL(A1:A4,1)
• Press Enter on your keyboard.
• The function will return the smallest number in the row.

 

 
COUNTIF:  This function is used to count the number of cells within a range which meet a specified condition.
 

 
For Example:- We have data in range A2:A11 in which we need to count how many times the states are repeating in Column A.

• Select the cell B2, and write the formula =Countif (A2:A11, A2) press Enter on the keyboard.
• The COUNTIF function will return 4 which means that “Washington” is repeating 4 times in Column A.

 

 
In this article,lets use the Small function to find out the smallest value in the range and the CountIf function will help us toignore the 0 value while choosing the smallest number from the range.

Let’s take an example to understand how we can find the minimum numerical value in a range while ignoring the 0 value.

We have data for items in range A1:C11. Column A contains Zone, column B contains Agent name and column C contains sold unit.
 

 
If you want to find the minimum number of sold quantity to ignore the 0, follow the below given steps:-

• Select the cell D2.
• Write the formula to find out the small value
• =SMALL(C1:C11,COUNTIF(C1:C11,0)+1)
• Press Enter on your keyboard.
• The function will return the minimum value in the range while ignoring the 0 value.

This is the way we can find out the minimum number in a range while ignoring 0 value by using the Small function along with the COUNTIF function in Microsoft Excel.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com