Exclude 0 from MIN array results
 

Hello,

I have a MIN array that tests a condition in column A, and then calculates
the minimum value from column B for all the rows in which the condition is
true.

=MIN((A1:A4="CF")*(B1:B4))

And sample data might be something like this:

CF 6
CF 8
WW 10
WW 4

The desired result would be 6, since that is lowest number for any row with
"CF" in it.

However, as I understand Excel performs the calculations, the array contains
values like this:

MIN((TRUE, TRUE, FALSE, FALSE)*(6, 8, 10, 4))

which becomes

MIN((6, 8, 0, 0))

And obviously, the minimum in that set is 0, which is what the function
returns.

How do I get the function to return the lowest of the nonzero numbers?

Thanks

Exclude 0 from MIN array results
 

=IF(MIN(your_expression)=0,SMALL(your_expression,2 ),MIN(your_expression))
--
Gary's Student


"Craig" wrote:

Hello,

I have a MIN array that tests a condition in column A, and then calculates
the minimum value from column B for all the rows in which the condition is
true.

=MIN((A1:A4="CF")*(B1:B4))

And sample data might be something like this:

CF 6
CF 8
WW 10
WW 4

The desired result would be 6, since that is lowest number for any row with
"CF" in it.

However, as I understand Excel performs the calculations, the array contains
values like this:

MIN((TRUE, TRUE, FALSE, FALSE)*(6, 8, 10, 4))

which becomes

MIN((6, 8, 0, 0))

And obviously, the minimum in that set is 0, which is what the function
returns.

How do I get the function to return the lowest of the nonzero numbers?

Thanks

Exclude 0 from MIN array results
 

Craig wrote:
I have a MIN array that tests a condition in column A, and then calculates
the minimum value from column B for all the rows in which the condition is
true.
=MIN((A1:A4="CF")*(B1:B4))
[....]
The desired result would be 6, since that is lowest number for any row with
"CF" in it.
[....]
And obviously, the minimum in that set is 0, which is what the function
returns.
How do I get the function to return the lowest of the nonzero numbers?

=min(if(A1:A4="CF",B1:B4))

entered as an array function (ctl-shift-Enter), as you did with your
MIN() formulation.