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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Month End Nav?[/TD]
[TD]Price per unit[/TD]
[TD]Monthly Returns[/TD]
[/TR]
[TR]
[TD]Nov 2, 2015
[/TD]
[TD]FALSE[/TD]
[TD]11.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov 9, 2015[/TD]
[TD]FALSE[/TD]
[TD]11.64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov 16, 2015[/TD]
[TD]FALSE[/TD]
[TD]11.97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov 23, 2015[/TD]
[TD]FALSE[/TD]
[TD]12.06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov 30, 2015[/TD]
[TD]TRUE[/TD]
[TD]11.99[/TD]
[TD]2.80%[/TD]
[/TR]
[TR]
[TD]Dec 7, 2015[/TD]
[TD]FALSE[/TD]
[TD]11.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec 14, 2015[/TD]
[TD]FALSE[/TD]
[TD]12.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec 21, 2015[/TD]
[TD]FALSE[/TD]
[TD]11.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec 29, 2015[/TD]
[TD]TRUE[/TD]
[TD]11.82[/TD]
[TD]-3.71%[/TD]
[/TR]
[TR]
[TD]Dec 31, 2015[/TD]
[TD]FALSE[/TD]
[TD]11.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 4, 2016[/TD]
[TD]FALSE[/TD]
[TD]12.19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 11, 2016
[/TD]
[TD]FALSE[/TD]
[TD]12.73[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 18, 2016[/TD]
[TD]FALSE[/TD]
[TD]12.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 25, 2016[/TD]
[TD]TRUE[/TD]
[TD]12.38[/TD]
[TD]5.64%[/TD]
[/TR]
[TR]
[TD]Feb 1, 2016[/TD]
[TD]FALSE[/TD]
[TD]12.88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb 8, 2016[/TD]
[TD]FALSE[/TD]
[TD]12.82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb 16, 2016[/TD]
[TD]FALSE[/TD]
[TD]12.69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb 22, 2016[/TD]
[TD]FALSE[/TD]
[TD]12.81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb 29, 2016[/TD]
[TD]TRUE[/TD]
[TD]12.81[/TD]
[TD]2.65%[/TD]
[/TR]
</tbody>[/TABLE]

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.