Teasing Monthly Returns from intra month returns

Hi All,

This has been frustrating me most of the afternoon. I have an array of returns from different points in a given month. The one constant is I have a return from some point in the month to the end of each month.

For example... I have the returns from 3/1/2012 through 3/4/2012 and 3/5/2012 through 3/31/2012. I want to geometrically link (1+r) the two returns to give me a monthly return for March.

I am trying to find a way that will take the product of all the returns in a given month no matter how many discreet returns I have within that month.

I tried a conditional array formula using if(and) without any luck.

I greatly appreciate any help.

Thanks,
Brad

I have attached a simple spreadsheet.Monthly Returns Example.xlsx

Nice Ace,

I appreciated that I don't need the hpr+1 column using your formula. I can see where I will run into problems when the year shifts. Do you see what the problem is with this fix...I think I read that array formulas can have only 1 condition...

=PRODUCT(IF(AND(MONTH($A$2:$A$10)=MONTH(E6),YEAR($A$2:$A$10)=YEAR(E6)),1+$B$2:$B$10)) ?

A9 = 3/5/2013
A10 = 3/31/2013

Thank for your help,
Brad

Works great. I'm going to have to wrap my head around the logic of the formula, but I get the gist. month*year must be equal to month*year. Very nicely done.

Thanks again,
Brad