Conditional Moving Average Based on Start Date

[Brandoeats]

I built a tool to compare a monthly savings forecast to actual savings, and then re-adjust go forward monthly forecast.

There are 2 adjusted forecast scenarios -

1) Use actual savings if available, use forecast savings if not.

2) Use actual savings if available, use 3mos moving average of adjusted forecast scenario #1 if not.

Final goal is to call out the difference between Adj Forecast #1 and #2 as benefit/risk due to trend.

Scenario #1 was fairly simple, however, I am running into issues /w the 3mos MA in v10b; specifically because savings can begin and end in any month between 1/2019 and 12/2019.

I think I can get conditional averaging to work /w averageif, however I am only getting errors.


Example Workbooks attached; I am here to answer additional questions - thanks in advance!

MA Example v10: Adjusted Forecast #2 (Start Date = January 2019) Working as Intended
MA Example v10b: Adjusted Forecast #2 (Start Date = March 2019) 3mos MA not working as Intended

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Brandoeats]

Completed, and uploaded -

Thanks!

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Brandoeats]

any ideas out there?

[JeteMc]

I believe that the formula in cell J12 on the 2019 Adj Forecast #2 sheet should read as follows although I get different numbers (when the formula is dragged across to U12) from those shown in columns W:AH

Please Login or Register  to view this content.

The formula that was in K12 produced an error because C12>=K11 is not a criteria of the range '2019 Adj Forecast #1'!J12:L12
For more information on the AVERAGEIF function see the article linked below.
https://support.office.com/en-us/art...9-F5576D8AC642
Let us know if you have any questions.