Return a Value Between the MAX of two dates Q

[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

[John]

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" <john@yahoooo.co> wrote in message
news:c_19e.51774$Z14.40770@news.indigo.ie...
>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
>

[Bob Phillips]

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)


"John" <john@yahoooo.co> wrote in message
news:c_19e.51774$Z14.40770@news.indigo.ie...
> 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
>
>

[John]

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


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
> 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)
>
>
> "John" <john@yahoooo.co> wrote in message
> news:c_19e.51774$Z14.40770@news.indigo.ie...
>> 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
>>
>>
>
>

[Bob Phillips]

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)


"John" <john@yahoooo.co> wrote in message
news:kh39e.51778$Z14.40761@news.indigo.ie...
> 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
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
> > 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)
> >
> >
> > "John" <john@yahoooo.co> wrote in message
> > news:c_19e.51774$Z14.40770@news.indigo.ie...
> >> 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
> >>
> >>
> >
> >
>
>

[John]

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


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23$7DO5LRFHA.2252@TK2MSFTNGP15.phx.gbl...
> 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)
>
>
> "John" <john@yahoooo.co> wrote in message
> news:kh39e.51778$Z14.40761@news.indigo.ie...
>> 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
>>
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
>> > 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)
>> >
>> >
>> > "John" <john@yahoooo.co> wrote in message
>> > news:c_19e.51774$Z14.40770@news.indigo.ie...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

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)


"John" <john@yahoooo.co> wrote in message
news:O049e.51782$Z14.40892@news.indigo.ie...
> 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)*(Sales
Date>=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
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23$7DO5LRFHA.2252@TK2MSFTNGP15.phx.gbl...
> > 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)
> >
> >
> > "John" <john@yahoooo.co> wrote in message
> > news:kh39e.51778$Z14.40761@news.indigo.ie...
> >> 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
> >>
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
> >> > 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)
> >> >
> >> >
> >> > "John" <john@yahoooo.co> wrote in message
> >> > news:c_19e.51774$Z14.40770@news.indigo.ie...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[John]

Its 2 mb Bob, I can send it just let me know

Rgds


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ew52yEMRFHA.164@TK2MSFTNGP12.phx.gbl...
> 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)
>
>
> "John" <john@yahoooo.co> wrote in message
> news:O049e.51782$Z14.40892@news.indigo.ie...
>> 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)*(Sales
> Date>=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
>>
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:%23$7DO5LRFHA.2252@TK2MSFTNGP15.phx.gbl...
>> > 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)
>> >
>> >
>> > "John" <john@yahoooo.co> wrote in message
>> > news:kh39e.51778$Z14.40761@news.indigo.ie...
>> >> 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
>> >>
>> >>
>> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> >> news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
>> >> > 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)
>> >> >
>> >> >
>> >> > "John" <john@yahoooo.co> wrote in message
>> >> > news:c_19e.51774$Z14.40770@news.indigo.ie...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

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)


"John" <john@yahoooo.co> wrote in message
news:b849e.51785$Z14.40662@news.indigo.ie...
> Its 2 mb Bob, I can send it just let me know
>
> Rgds
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:ew52yEMRFHA.164@TK2MSFTNGP12.phx.gbl...
> > 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)
> >
> >
> > "John" <john@yahoooo.co> wrote in message
> > news:O049e.51782$Z14.40892@news.indigo.ie...
> >> 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)*(Sales
> > Date>=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
> >>
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:%23$7DO5LRFHA.2252@TK2MSFTNGP15.phx.gbl...
> >> > 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)
> >> >
> >> >
> >> > "John" <john@yahoooo.co> wrote in message
> >> > news:kh39e.51778$Z14.40761@news.indigo.ie...
> >> >> 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
> >> >>
> >> >>
> >> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> >> news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
> >> >> > 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)
> >> >> >
> >> >> >
> >> >> > "John" <john@yahoooo.co> wrote in message
> >> >> > news:c_19e.51774$Z14.40770@news.indigo.ie...
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[John]

Sent Bob, I've stripped it down a bit


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uLQCERMRFHA.3140@tk2msftngp13.phx.gbl...
>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)
>
>
> "John" <john@yahoooo.co> wrote in message
> news:b849e.51785$Z14.40662@news.indigo.ie...
>> Its 2 mb Bob, I can send it just let me know
>>
>> Rgds
>>
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:ew52yEMRFHA.164@TK2MSFTNGP12.phx.gbl...
>> > 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)
>> >
>> >
>> > "John" <john@yahoooo.co> wrote in message
>> > news:O049e.51782$Z14.40892@news.indigo.ie...
>> >> 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)*(Sales
>> > Date>=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
>> >>
>> >>
>> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> >> news:%23$7DO5LRFHA.2252@TK2MSFTNGP15.phx.gbl...
>> >> > 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)
>> >> >
>> >> >
>> >> > "John" <john@yahoooo.co> wrote in message
>> >> > news:kh39e.51778$Z14.40761@news.indigo.ie...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> >> >> news:eIVqpeLRFHA.2736@TK2MSFTNGP09.phx.gbl...
>> >> >> > 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)
>> >> >> >
>> >> >> >
>> >> >> > "John" <john@yahoooo.co> wrote in message
>> >> >> > news:c_19e.51774$Z14.40770@news.indigo.ie...
>> >> >> >> 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
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>