How to find the smallest number without zero using MINIFS in Excel
To find the smallest number in a range without including zeroes using the MINIFS function in Excel, you can set up a condition that excludes zeros from the criteria. You can use (>,<,=,<>) in the criteria.
Let’s make a change in our dataset to understand the use of MINIFS without zeroes. Change the quantity of Cakes sold in March to 0.
Now, suppose we need to find the minimum value of quantity sold other than zero. The formula can be written as:
=MINIFS(D2:D14,D2:D14,”>0″)
- D2:D14 is the min_range, where we want to find the minimum quantity sold.
- D2:D14 is the first criteria range, which checks the “Qty Sold” column for value greater than 0.
- “>0” is the first criteria.
The MINIFS function will return the minimum quantity sold other than 0 which in this example would be 233.
You can use the same formula by adding multiple conditions to find the minimum Qty Sold of “Cakes” other than 0. The formula will be:
=MINIFS(D2:D14,D2:D14,”>0″,A2:A14,”Cakes”)
- D2:D14 is the min_range, where we want to find the minimum quantity sold.
- D2:D14 is the first criteria range, which checks the “Qty Sold” column for quantity sold other than 0.
- “>0” is the first criteria.
- A2:A14 is the second criteria range, which checks the “Product” column for “Cakes”.
- “Cakes” is the second criteria.
The MINIFS function will return the minimum quantity sold that meets both criteria, which in this example would be 250.
How to use MINIFS function in Microsoft Excel
The MINIFS function in Excel is a powerful tool for filtering and analyzing data. It allows you to find the smallest numeric value in a range of cells that meet one or more specified requirements. This can be useful for a variety of tasks, such as finding the lowest price for a product in a specific category, finding the shortest delivery time for a product, finding the highest test score for a student in a particular class, or finding the lowest employee salary in a department.
Contact Us