Home |
Search |
Today's Posts |
#1
|
|||
|
|||
large / sumproduct combo
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 |
#2
|
|||
|
|||
large / sumproduct combo
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" wrote in message ... 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 |
#3
|
|||
|
|||
large / sumproduct combo
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:B 30="Balanced"),A2:A30)),A2 :A30,0)) still as an array formula -- HTH RP (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... 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" wrote in message ... 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 |
#4
|
|||
|
|||
large / sumproduct combo
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:B 30="Balanced"),A2:A30)),A2 :A30,0)) still as an array formula -- HTH RP (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting Combo Boxes /Returning an Array | Excel Discussion (Misc queries) | |||
Dynamic Combo Box | Excel Worksheet Functions | |||
connecting combo boxes to yield data in another cell. | Excel Discussion (Misc queries) | |||
dynamic combo boxes | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |