I've attached a worksheet which I've taken from a larger workbook, which is a trading strategy.
The trading strategy formulas elsewhere in the workbook must use the dates in Column A (generally, business days) of the attached worksheet. However some of the data which goes into the calculations is obtained daily (Columns H and I).
What we want to do is match the data in H and I to the dates in Column A as best we can. The results of this matching are in column M.
The challenge to solve is that we're matching business days to data (in Column I) from every calendar day. The formula is meant to average out the extra data-days in column I and plug that into column M. So for example, if we have data for Saturday the 3rd, Sunday the 4th, and Monday the 5th, then we want column M to show the average of the 3rd:5th
The existing formulas work for a time, however around row 830 they get out of sync, probably due to the fact that by that time, the # of business days is lagging further and further behind the # of calendar days.
I've posted some notes in the attached worksheet to try to help any of you gurus who can take the time to look at this.
I always appreciate your help. Thanks!
Bookmarks