5 Ways To Get Minimum Value In Excel Excluding Zero

Understanding the Problem

When working with datasets in Excel, it's common to need to find the minimum value in a range of cells. However, in many cases, you may want to exclude zero from the calculation, as it can skew the results. In this article, we'll explore five different ways to get the minimum value in Excel, excluding zero.

Method 1: Using the MINIFS Function

The MINIFS function is a great way to find the minimum value in a range of cells, while excluding zero. The syntax for the MINIFS function is:

MINIFS(range, criteria_range1, criteria1, [criteria_range2], [criteria2],...)

Where:

range is the range of cells that you want to find the minimum value in
criteria_range1 is the range of cells that you want to apply the first criteria to
criteria1 is the first criteria that you want to apply
[criteria_range2] and [criteria2] are optional additional criteria

For example, suppose you have a range of sales data in cells A1:A10, and you want to find the minimum sales value, excluding zero. You can use the following formula:

=MINIFS(A1:A10, A1:A10, ">0")

This formula tells Excel to find the minimum value in the range A1:A10, but only consider cells that have a value greater than zero.

Example Use Case

Suppose you have the following sales data:

Sales
10
20
0
30
40
0
50

If you use the MINIFS function with the formula =MINIFS(A1:A10, A1:A10, ">0"), Excel will return the value 10, which is the minimum sales value, excluding zero.

Method 2: Using the MINIF Function with Multiple Criteria

If you need to apply multiple criteria to find the minimum value, you can use the MINIF function. The syntax for the MINIF function is:

MINIF(range, criteria_range, criteria)

Where:

range is the range of cells that you want to find the minimum value in
criteria_range is the range of cells that you want to apply the criteria to
criteria is the criteria that you want to apply

For example, suppose you have a range of sales data in cells A1:A10, and you want to find the minimum sales value, excluding zero, for a specific region. You can use the following formula:

=MINIF(A1:A10, A1:A10, ">0", B1:B10, "North")

This formula tells Excel to find the minimum value in the range A1:A10, but only consider cells that have a value greater than zero, and are also located in the North region.

Example Use Case

Suppose you have the following sales data:

Sales	Region
10	North
20	South
0	North
30	South
40	North
0	South
50	North

If you use the MINIF function with the formula =MINIF(A1:A10, A1:A10, ">0", B1:B10, "North"), Excel will return the value 10, which is the minimum sales value, excluding zero, for the North region.

Method 3: Using the SMALL Function

The SMALL function is another way to find the minimum value in a range of cells, excluding zero. The syntax for the SMALL function is:

SMALL(range, k)

Where:

range is the range of cells that you want to find the minimum value in
k is the position of the value that you want to find

For example, suppose you have a range of sales data in cells A1:A10, and you want to find the minimum sales value, excluding zero. You can use the following formula:

=SMALL(A1:A10, 1)

This formula tells Excel to find the smallest value in the range A1:A10, but if there are multiple smallest values, it will return the first one.

Example Use Case

Suppose you have the following sales data:

Sales
10
20
0
30
40
0
50

If you use the SMALL function with the formula =SMALL(A1:A10, 1), Excel will return the value 10, which is the minimum sales value, excluding zero.

Method 4: Using the FILTER Function

The FILTER function is a new function in Excel that allows you to filter a range of cells based on a criteria. You can use the FILTER function to find the minimum value in a range of cells, excluding zero. The syntax for the FILTER function is:

FILTER(range, criteria)

Where:

range is the range of cells that you want to filter
criteria is the criteria that you want to apply

For example, suppose you have a range of sales data in cells A1:A10, and you want to find the minimum sales value, excluding zero. You can use the following formula:

=MIN(FILTER(A1:A10, A1:A10>0))

This formula tells Excel to filter the range A1:A10 to only include cells that have a value greater than zero, and then find the minimum value in the filtered range.

Example Use Case

Suppose you have the following sales data:

Sales
10
20
0
30
40
0
50

If you use the FILTER function with the formula =MIN(FILTER(A1:A10, A1:A10>0)), Excel will return the value 10, which is the minimum sales value, excluding zero.

Method 5: Using the AGGREGATE Function

The AGGREGATE function is a powerful function in Excel that allows you to perform a variety of calculations, including finding the minimum value in a range of cells. You can use the AGGREGATE function to find the minimum value in a range of cells, excluding zero. The syntax for the AGGREGATE function is:

AGGREGATE(function_num, ref1, [ref2],...)

Where:

function_num is the number of the function that you want to perform
ref1 is the first reference that you want to use
[ref2] is an optional additional reference

For example, suppose you have a range of sales data in cells A1:A10, and you want to find the minimum sales value, excluding zero. You can use the following formula:

=AGGREGATE(15, 6, A1:A10, ">0")

This formula tells Excel to find the minimum value in the range A1:A10, but only consider cells that have a value greater than zero.

Example Use Case

Suppose you have the following sales data:

Sales
10
20
0
30
40
0
50

If you use the AGGREGATE function with the formula =AGGREGATE(15, 6, A1:A10, ">0"), Excel will return the value 10, which is the minimum sales value, excluding zero.

Conclusion

In this article, we've explored five different ways to get the minimum value in Excel, excluding zero. Whether you use the MINIFS function, the MINIF function with multiple criteria, the SMALL function, the FILTER function, or the AGGREGATE function, you can easily find the minimum value in a range of cells, excluding zero. By using one of these methods, you can simplify your calculations and get the results you need.