If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Problem with a Min function
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. . |
#3
|
|||
|
|||
Problem with a Min function
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. . |
#4
|
|||
|
|||
Problem with a MIN function
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 |
#5
|
|||
|
|||
Problem with a MIN function
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 |
#6
|
|||
|
|||
Problem with a MIN function
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. . |
#7
|
|||
|
|||
Problem with a MIN function
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 |
Thread Tools | |
Display Modes | |
|
|