Excel Question - Simple Forecast / Actuals Formula / Layout

[omniflow]

I'm trying to figure out the best way to create a nice easy to use Forecast / Actuals spreadsheet to track time bookings on a project.

Maybe up to 20 or 30 lines, each line for a different person

At the beginning of the year / project I would fill in the number of days I am forecasting each person to book.
At the end of each month (or more likely about 3 weeks after the end of each month) I would fill in the number of days each person actually booked

I would want to be able to see:

YTD actuals
YTD actuals + remaining forecast

Is there an easy way to do this without using hundreds of if / isblank statements?

Thanks

[sideways sid]

It was quicker to do it than explain it, but now I can't attach it here.

PM me your email address and I'll send it over.

[omniflow]

Thanks - I think maybe I wasn't quite clear in my original request.

I want to be able to retain the original forecast number, so I want to put the actual number in a new column - each month has two columns - 1 for forecast, the other one is either blank (or could be zero), or it has the actual number in it.

Then, taking the middle of may as an example, I want to show:

YTD Actuals - Actual values for Jan, Feb, Mar and Apr
YTD Actuals + remaining forecast = actual values for Jan - Apr + forecast values for May - Dec

I want to create a formula that works whatever the month is, rather than having a separate formula for each month.

[Doofus]

You can use SumIf, or you can set a switch for each month, such that excel puts either the forecast, or the actual, if there is one, into a hidden cell, and you can then add those up.

YTD Actual is easy. It's the sum of all the actuals, because any that haven't happened yet will be zero.

Because you know which month you're up to (because of the 'actual' number), you can use that to select which cells you add up to get your actuals and rolling forecast.

[Mr Pointy]

How on earth would anyone have got that from your original post?

Create a sheet with the headings you want & post a picture. That way the next poor sod to have a go might stand a chance.

[Doofus]

[omniflow]

Doofus said:

You can use SumIf, or you can set a switch for each month, such that excel puts either the forecast, or the actual, if there is one, into a hidden cell, and you can then add those up.

YTD Actual is easy. It's the sum of all the actuals, because any that haven't happened yet will be zero.

Because you know which month you're up to (because of the 'actual' number), you can use that to select which cells you add up to get your actuals and rolling forecast.

Thanks for this - I couldn't see the wood for the trees

Do all of the setup, using a 3rd column, then when it's all working fine, hide the extra columns to keep it looking neat.