Automated Estimate at Completion

Hi Everyone,
This may be a VB answer but I thought I'd try here first.

I am trying to find out the Estimate at Completion - I'd like to sum up all of the previous months Actuals and then the forecast for the months remaining.
If the spreadsheet has to be set up differently, please let me know.
Currently I have 25 months and the data like this:
Jan 16 Forecast Jan 16 Actual...

Is there a formula that will update the sum on the first of each month...?

Thanks for the rep!

When I refreshed my workbook (today) the formula was only counting row 3 - Actuals, it was no longer adding the forecast (row 4) if row 3 is blank.

Originally Posted by 6StringJazzer

For an EAC you need to have future forecasts. The only forecasts you have are for months that have already past and have actuals. I provided a mock-up. You also need a different data layout. (BTW I have done this at least a dozen times at work.)

"update the sum on the first of each month." I am not sure what you are looking for here. The formula doesn't care what month it is, it only cares about what months you have recorded actuals for, which is all that matters.

I refreshed the workbook today (Aug 3) and the formula was no longer counting the forecast (row 4) if the Actuals (row 3) were blank.

I found the answer to my problem - the cells for my forecast and actuals were referencing from another sheet, so I had to edit the formula slightly, so the criteria "<=0" rather than "".
I hope this thread helps other people too!