Choosing previous month end for monthly returns

[mloveloc]

Hi,

This is a problem I have been trying to solve for quite sometime. Every month I have the calculate monthly returns, (current month's price/previous month's price)-1 (or (current month's price/previous month's price)/previous month's price, either work.)

I want to make this process automatic however, the prices are valued weekly rather than monthly. I need to skip the weekly prices and go to the previous month's end price (last business day rather than the true month end.) Unfortunately, I can't count up a particular number of cells because the number of weeks fluctuate depending on the month and year.

This is how I have set up my data:

Date	Month End Nav?	Price per unit	Monthly Returns
Nov 2, 2015	FALSE	11.67
Nov 9, 2015	FALSE	11.64
Nov 16, 2015	FALSE	11.97
Nov 23, 2015	FALSE	12.06
Nov 30, 2015	TRUE	11.99	2.80%
Dec 7, 2015	FALSE	11.99
Dec 14, 2015	FALSE	12.01
Dec 21, 2015	FALSE	11.76
Dec 29, 2015	TRUE	11.82	-3.71%
Dec 31, 2015	FALSE	11.85
Jan 4, 2016	FALSE	12.19
Jan 11, 2016	FALSE	12.73
Jan 18, 2016	FALSE	12.48
Jan 25, 2016	TRUE	12.38	5.64%
Feb 1, 2016	FALSE	12.88
Feb 8, 2016	FALSE	12.82
Feb 16, 2016	FALSE	12.69
Feb 22, 2016	FALSE	12.81
Feb 29, 2016	TRUE	12.81	2.65%

<tbody>
</tbody>

I was hoping someone could help me (possibly search for the previous TRUE?) I am just unsure of how to do it that correctly choose the price. If possible it would be great also if it leaves the cell blank (or 0) for the weeks that are false to help my coworkers not confuse the wrong date. Learning how to do this would also help automate a lot of other tasks so it would be much appreciated!

Thanks,
Madeleine

 

[FlameRetired]

Withdrawn by FR.

 

[FlameRetired]

See if this returns the amounts. It returns nothing for 11/30/2015 because there is no previous month to reference in the data.

=IFERROR(IF(B2=TRUE,(LOOKUP(2,1/(TRUE=$B$1:B1),$C$1:C1)),""),"")

 

[mloveloc]

I got this to work! Thank you so much for your help!

 

[FlameRetired]

You are welcome. Glad to help.