Problem with a MIN function

[Andy B]

Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A52>0)*(A46:A52+B46:B52)) array-entered
I've got dates in both ranges - and both ranges also contain blanks. I don't
understand why this formula does not return what I want!! I'm trying to get
the earliest date in A46:A52 where there is a blank in the corresponding
cell in B46:B52.
I've checked the formula (including the F9 option in the formula bar to view
each part) and it's got me beat!

 

[Frank Kabel]

Hi
try the arraz formula
=MIN(IF((A46:A52>0)*(B46:B52=""),A46:A52))

 

[Andy B]

Cheers Frank. (I still don't know why my formula doesn't work!)

 

[Ron Rosenfeld]

Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A52>0)*(A46:A52+B46:B52)) array-entered
I've got dates in both ranges - and both ranges also contain blanks. I don't
understand why this formula does not return what I want!! I'm trying to get
the earliest date in A46:A52 where there is a blank in the corresponding
cell in B46:B52.
I've checked the formula (including the F9 option in the formula bar to view
each part) and it's got me beat!

You received a working formula from Andy.

You didn't say how your formula was not working, but I expect it is returning a
zero (0).

For example, if any cell in the range A46:A52 is blank, then A46:A52>0 will
return FALSE which will evaluate to 0. '0' times your second part also equals
'0'. The MIN function will, naturally, see '0' as being less than any other
factor (assuming there are no negative numbers).


--ron

 

[Andy B]

Thanks Ron. A normal MIN function must ignore blanks, but my formula must
count them as zeroes.

 

[Hi Andy]

Simple explanation. I would assume you get a result of 1-1-
1900?

Reson: if you first condition (A46:A52>0) is not met this
return zero. So the minimum value is zero.
Your formula may work if you have valid dates in all rows

 

[Ron Rosenfeld]

Thanks Ron. A normal MIN function must ignore blanks, but my formula must
count them as zeroes.

Yes, your formula converts the Blanks to zeroes.


--ron