Average from whole months data taking a exact date to start with.

Hi experts,

Now I am in a really big trouble, after I've been working in a file to work out stocks so hard, my boss wants me to be more accurate. He proposed me to the the following stock calculation and I have got no IDEA whatsoever if there is a formula to do it.

In the file attached there is only the data to look at, though my file it's a little bit lager.

Now in cell G10 I've got the stock forecast in 3 months time, this stock forecast is calculated from a date rounded to the closest month (according to my criteria), then the formula looks at the reference number cell D1 to work out the formula. All the steps are in the file.

In order to be more accurate my boss and I want to use the exact date, so that delete all the round month process. My intention is to use the day of the date and then make a fraction of the months to calculate the average. Taking into account that a month have as an average of 30.4166 days.

In the file I did an example, to clarify my messy explanation.

Cells B2, C2, D2 won't be needed in order to do so.

Please have a look at the file. At the end my intention is to to such a formula or a similar one. The formula should be an automatic one when typing dates in cell A10

The result of it is in row 31 and 32.

Thank you so much in advance!!


trial.xlsx

Sorry the formula in D32 is:

=B32-(AVERAGE(D23:E24,H23:H24)*3)