large / sumproduct combo

[David]

I have a table with 4 columns as below:

Date Portfolio units price value
05/03/05 Balanced 0 0 0
05/03/05 Balanced 0.4270 $11.71 $5.00
05/03/05 Balanced 0 0 0
05/03/05 Balanced 0 0 0
05/03/05 Balanced 0 0 0
05/03/05 Growth 0 0 0
05/03/05 Growth 0.4125 $12.12 $5.00
05/03/05 Growth 0 0 0
05/03/05 Growth 0 0 0
05/03/05 Growth 0 0 0

The table continues and includes cases where there is more than one listing
for a given portfolio(say, "Balanced") within a given month - but on
different dates.
I'd like to write a formula that says, "Give me the most recent price for
the "X" portfolio in the Month of "Y". I can look up the total of all values
in the price column for the balanced portfolio(or a count thereof) with
sumproduct, but I need to combine it with the large function(maybe?) in a way
that gives me the closest date to the given date.

The intent here is to establish a value of a portfolio as of the end of any
given month, so I would need the most recent value(that is less than the
month end I'm looking for) for that portfolio in order to value. it.

Hopefully I explained this in enough detail, but if there are any questions,
please dont hesitate to ask.


tia,
Dave

[Peo Sjoblom]

One way

=MAX(IF((MONTH(A2:A30)=1)*(B2:B30="Balanced")*(A2:A30),C2:C30))

entered with ctrl + shift & enter

where price is in C2:C30, the above will work for January, you can add year
as well
if needed

--
Regards,

Peo Sjoblom

(No private emails please)


"David" <[email protected]> wrote in message
news:[email protected]...
>I have a table with 4 columns as below:
>
> Date Portfolio units price value
> 05/03/05 Balanced 0 0 0
> 05/03/05 Balanced 0.4270 $11.71 $5.00
> 05/03/05 Balanced 0 0 0
> 05/03/05 Balanced 0 0 0
> 05/03/05 Balanced 0 0 0
> 05/03/05 Growth 0 0 0
> 05/03/05 Growth 0.4125 $12.12 $5.00
> 05/03/05 Growth 0 0 0
> 05/03/05 Growth 0 0 0
> 05/03/05 Growth 0 0 0
>
> The table continues and includes cases where there is more than one
> listing
> for a given portfolio(say, "Balanced") within a given month - but on
> different dates.
> I'd like to write a formula that says, "Give me the most recent price for
> the "X" portfolio in the Month of "Y". I can look up the total of all
> values
> in the price column for the balanced portfolio(or a count thereof) with
> sumproduct, but I need to combine it with the large function(maybe?) in a
> way
> that gives me the closest date to the given date.
>
> The intent here is to establish a value of a portfolio as of the end of
> any
> given month, so I would need the most recent value(that is less than the
> month end I'm looking for) for that portfolio in order to value. it.
>
> Hopefully I explained this in enough detail, but if there are any
> questions,
> please dont hesitate to ask.
>
>
> tia,
> Dave

[Bob Phillips]

I think Peo's formula gives you the maximum amount for the month and
portfolio in question, not the latest. So if the value were 147 on the 11th
and 145 on the 12th, it returns 147 not 145. A small tweak should sort it

=INDEX(C2:C30,MATCH(MAX(IF((MONTH(A2:A30)=5)*(B2:B30="Balanced"),A2:A30)),A2
:A30,0))

still as an array formula

--

HTH

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


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> One way
>
> =MAX(IF((MONTH(A2:A30)=1)*(B2:B30="Balanced")*(A2:A30),C2:C30))
>
> entered with ctrl + shift & enter
>
> where price is in C2:C30, the above will work for January, you can add
year
> as well
> if needed
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> >I have a table with 4 columns as below:
> >
> > Date Portfolio units price value
> > 05/03/05 Balanced 0 0 0
> > 05/03/05 Balanced 0.4270 $11.71 $5.00
> > 05/03/05 Balanced 0 0 0
> > 05/03/05 Balanced 0 0 0
> > 05/03/05 Balanced 0 0 0
> > 05/03/05 Growth 0 0 0
> > 05/03/05 Growth 0.4125 $12.12 $5.00
> > 05/03/05 Growth 0 0 0
> > 05/03/05 Growth 0 0 0
> > 05/03/05 Growth 0 0 0
> >
> > The table continues and includes cases where there is more than one
> > listing
> > for a given portfolio(say, "Balanced") within a given month - but on
> > different dates.
> > I'd like to write a formula that says, "Give me the most recent price
for
> > the "X" portfolio in the Month of "Y". I can look up the total of all
> > values
> > in the price column for the balanced portfolio(or a count thereof) with
> > sumproduct, but I need to combine it with the large function(maybe?) in
a
> > way
> > that gives me the closest date to the given date.
> >
> > The intent here is to establish a value of a portfolio as of the end of
> > any
> > given month, so I would need the most recent value(that is less than the
> > month end I'm looking for) for that portfolio in order to value. it.
> >
> > Hopefully I explained this in enough detail, but if there are any
> > questions,
> > please dont hesitate to ask.
> >
> >
> > tia,
> > Dave
>

[Dave Breitenbach]

Thanks to both for the effort. I'm using Bob's adjusted formula and it works
perfectly.

"Bob Phillips" wrote:

> I think Peo's formula gives you the maximum amount for the month and
> portfolio in question, not the latest. So if the value were 147 on the 11th
> and 145 on the 12th, it returns 147 not 145. A small tweak should sort it
>
> =INDEX(C2:C30,MATCH(MAX(IF((MONTH(A2:A30)=5)*(B2:B30="Balanced"),A2:A30)),A2
> :A30,0))
>
> still as an array formula
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > One way
> >
> > =MAX(IF((MONTH(A2:A30)=1)*(B2:B30="Balanced")*(A2:A30),C2:C30))
> >
> > entered with ctrl + shift & enter
> >
> > where price is in C2:C30, the above will work for January, you can add
> year
> > as well
> > if needed
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a table with 4 columns as below:
> > >
> > > Date Portfolio units price value
> > > 05/03/05 Balanced 0 0 0
> > > 05/03/05 Balanced 0.4270 $11.71 $5.00
> > > 05/03/05 Balanced 0 0 0
> > > 05/03/05 Balanced 0 0 0
> > > 05/03/05 Balanced 0 0 0
> > > 05/03/05 Growth 0 0 0
> > > 05/03/05 Growth 0.4125 $12.12 $5.00
> > > 05/03/05 Growth 0 0 0
> > > 05/03/05 Growth 0 0 0
> > > 05/03/05 Growth 0 0 0
> > >
> > > The table continues and includes cases where there is more than one
> > > listing
> > > for a given portfolio(say, "Balanced") within a given month - but on
> > > different dates.
> > > I'd like to write a formula that says, "Give me the most recent price
> for
> > > the "X" portfolio in the Month of "Y". I can look up the total of all
> > > values
> > > in the price column for the balanced portfolio(or a count thereof) with
> > > sumproduct, but I need to combine it with the large function(maybe?) in
> a
> > > way
> > > that gives me the closest date to the given date.
> > >
> > > The intent here is to establish a value of a portfolio as of the end of
> > > any
> > > given month, so I would need the most recent value(that is less than the
> > > month end I'm looking for) for that portfolio in order to value. it.
> > >
> > > Hopefully I explained this in enough detail, but if there are any
> > > questions,
> > > please dont hesitate to ask.
> > >
> > >
> > > tia,
> > > Dave
> >
>
>
>