Return a Value Between the MAX of two dates Q

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have the formula below that looks at a table of transactions, which show
stock movements by date by location. The formula shown gives me total
purchases for a Product Code that is detailed within H6, within the table
concerned for the location 'London'

What I also want to achieve is to detail the closing Stock for this
particular product also for 'London'. My problem is that say if the table
shows all transaction between 11/04/05 and 17/04/05 (I show 7 days at a time
because my table is big) and Product H6 has no transactions on the 17/04/05
i.e. only for the 6 days to 16/04/05 I need a flexible formula that can say
look at the 'MAX' date between two date Ranges for that Product and Location
(I do have the dates shown in Cells AA1 and AA2 - Start and End dates) and
return the value within the Named Range "Closing_Stock", whether that is
Zero or 100 etc

Thanks




=SUMPRODUCT((StoreNo="London")*(Inventory_No=$H$6),(Purchases))/10
 
I thought the following formula might work but I'm getting a #N/A

=SUMPRODUCT(--(MAX(SalesDate=Input!AA1:AA2))*(Inventory_No=$H$6)*(StoreNo="London"))*(Closing_Stock)
 
John,

Assuming the dates are in a range called Dates

=INDEX(Purchases,,MATCH(MAX((StoreNo="London")*(Inventory_No=$H$6)*(Dates>=A
A1)*(Dates<=AA2)*Dates),Dates,0))

which assumes the data is horizontal. If it is vertical, use

=INDEX(Purchases,MATCH(MAX((StoreNo="London")*(Inventory_No=$H$6)*(Dates>=AA
1)*(Dates<=AA2)*Dates),Dates,0))

--

HTH

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

I'm still getting a #N/A

Aren't the two formulas the same except for the second "," after Purchases -
is that correct?

Rgds
 
Yes, they are, but that is because they are either INDEXING into a matched
column or a matched row.

It worked okay in my test, but I cannot be sure that my test data was set up
in the same way as your real data, I had to guess.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I have modified the formula slightly to the following, which produces the
#N/A

=INDEX(Closing_Stock,MATCH(MAX((StoreNo="London")*(Inventory_No=$H$6)*(SalesDate>=Input!AA1)*(SalesDate<=Input!AA2)*SalesDate),SalesDate,0))

Going into the Evaluation of same it shows the #N/A with the Range Name
"Inventory_No" - this value actually returns 14, which is different than my
value in H6, which is 1, thus there is a False Value returned.

Not sure from the formula why Inventory_No is producing 14
 
John,

Evaluating Inventory_No should give an array of values, not just one.

Can you describe your data, or perhaps send me your workbook?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I have ADSL John, so no problem.

Send to bob dot phillips at tiscali dot co dot uk

Do the obvious with that address.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Sent Bob, I've stripped it down a bit


Bob Phillips said:
I have ADSL John, so no problem.

Send to bob dot phillips at tiscali dot co dot uk

Do the obvious with that address.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top