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)

 

[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)

 

[Dave Breitenbach]

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