Problem with a MIN function

Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A520)*(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!

--
Andy.

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

-----Original Message-----
Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A520)*(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!

--
Andy.



.

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

--
Andy.


"Frank Kabel" wrote in message
...
Hi
try the arraz formula
=MIN(IF((A46:A520)*(B46:B52=""),A46:A52))

-----Original Message-----
Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A520)*(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!

--
Andy.



.

On Wed, 16 Jun 2004 10:27:25 +0100, "Andy B"
wrote:

Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A520)*(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:A520 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

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

--
Andy.


"Ron Rosenfeld" wrote in message
...
On Wed, 16 Jun 2004 10:27:25 +0100, "Andy B"
wrote:

Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A520)*(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:A520
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

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

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

-----Original Message-----
Hi all

Please can someone explain to me why this doesn't work:
=MIN((A46:A520)*(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!

--
Andy.



.

On Wed, 16 Jun 2004 12:28:32 +0100, "Andy B"
wrote:

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