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 kth smallest value in a data set.
 
small
 
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.

 
img1
 
COUNTIF:  This function is used to count the number of cells within a range which meet a specified condition.
 
countif
 
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.

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

 
img4
 

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.

 

image 48

 

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

 



One thought on “How to find the minimum value in range while ignoring 0 value in Microsoft Excel

  1. “more “”universal”” way to achieve the same result will be
    {=min(if(A1:A7=0,max(A1:A7),A1:A7)}

    (brackets {} means that you should press Ctrl-Shift-Enter to complete the formula)”

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>