Find MIN of Dates and Returns Value

[John]

I received the following code from Bob Philips that looks for the max date
for a location (between those dates in column AH) and returns the closing
stock

{=INDEX(Closing_Stocks,MAX((StoreNo="London")*(SalesDate>=$AH$1)*(Inventory_No=$H$6)*(SalesDate<="$AH")*ROW(StoreNo))-1)/10}

What I want now is to return the Opening Stock. Again I have a range name
called "Opening_Stocks". I thought it was a matter of just changing MAX to
MIN, but I'm getting a #Value appear although I'm surpressing it to return
0, my code is as follows

{=IF(ISERROR(INDEX(Opening_Stocks,MIN((StoreNo=$B$50)*(SalesDate>=$AH$1)*(Inventory_No=B9)*(SalesDate<="$AH")*ROW(StoreNo))-1)/10),0,(INDEX(Opening_Stocks,MIN((StoreNo=$B$50)*(SalesDate>=$AH$1)*(Inventory_No=B9)*(SalesDate<="$AH")*ROW(StoreNo))-1)/10))}

Any help would be great as its driving me nuts

 

[Bob Phillips]

John,

The formula seems to have gotten corrupted.

{=INDEX(Closing_Stocks,MAX((StoreNo="London")*(SalesDate>=$AH$1)*(Inventory_
No=$H$6)*(SalesDate<="$AH")*ROW(StoreNo))-1)/10}

The bit that say SalesDate<="$AH" has to be wrong, and it has carried
forward to the other.

 

[John]

Thanks Bob

My Closing Stock (full code) is

{=IF(ISERROR(INDEX(Closing_Stocks,MAX((StoreNo=$B$50)*(SalesDate>=$AM$1)*(Inventory_No=B9)*(SalesDate<="$AM")*ROW(StoreNo))-1)/10),0,(INDEX(Closing_Stocks,MAX((StoreNo=$B$50)*(SalesDate>=$AM$1)*(Inventory_No=B9)*(SalesDate<="$AM")*ROW(StoreNo))-1)/10))}

And this returns the correct value, then I changed the relevant parts for
the Opening Stock to

{=IF(ISERROR(INDEX(Opening_Stocks,MIN((StoreNo=$B$50)*(SalesDate>=$AM$1)*(Inventory_No=B9)*(SalesDate<="$AM")*ROW(StoreNo))-1)/10),0,(INDEX(Opening_Stocks,MIN((StoreNo=$B$50)*(SalesDate>=$AM$1)*(Inventory_No=B9)*(SalesDate<="$AM")*ROW(StoreNo))-1)/10))}

But I get 0 (should be 1778) or 0 as I have the ISERROR within.

 

[Bob Phillips]

John,

Can you send me your up to date workbook again, I trashed the other, and I
will take a look? The problem is probably because MIN will take an empty
value as valid, so it probably needs another test for <> 0.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Bob, just sent

John,

Can you send me your up to date workbook again, I trashed the other, and I
will take a look? The problem is probably because MIN will take an empty
value as valid, so it probably needs another test for <> 0.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

John,

Solution sent in post.

To anyone else that might be interested, the problem was that the tests
returned an array, and as some data did not match, that array returned a
number of 0 values, which the MIN caught. Something along the lines of the
following array formula

MIN((rng1="value1")*(rng2="value2"))

The way I overcame it was to intercept the array and replace all 0 values
with 99999, like so

=MIN(IF((rng1="value1")*(rng2="value2")>0,(rng1="value1")*(rng2="value2"),99
999))

This works, but my question is, can anyone think of a better way, it gets
klunky when you have many tests within the MIN statement?

--

HTH

RP
(remove nothere from the email address if mailing direct)