Mid month price averages

I've run into a bit of trouble with some excel work so I thought id post here to see if u would be able to help me . I already managed to get a working formula to generate monthly prices based on weekly prices. Now i need help because some of the customers require middle of the month price averages and so have averages drawn from the middle of the month(week of the 15th/16th) to the middle of the next month. Is there a way to alter the formula to work from the middle of the months rather than than their start?

I've added an excel sheet with the relevant information and added some dummy data in a similar range to what I'm working with.

rgrds,
Marcus

Excel forum sample.xlsx

You can probably make it more efficient but my effort attached. The EOmonth() function is the secret to making this work. This goes 16th to the 15th of the following month.

Thanks Brendan it appears to be working . Just a quick question.. Does the formula work if i were to add additional months and their respective prices to the data set? (I havent had a chance to update them yet)

Yes should do.

alright thanks

are you able to mark this solved as per rule 9

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.