Rolling Calculation

Hi there,

To be honest, I'm not even sure the correct term for this is a Rolling Calculation - it may be something entirely different, but if it is slightly misleading, then it's due to my own lack of knowledge, rather than anything more mischevious!!

I'm currently working on a spreadsheet which illustrates our sales on a Daily basis, vs. Target, among other things.

What I have is one column with Target figures in (for every day of the year) and the achieved/actual figure in the next column. At the end of each month is a total figure and a YTD figure. I want the YTD figure for the Target column to only total up to the point where there is an equivalent Actual figure, so I have a like for like comparison, year on year.

I have attached a sample of this spreadsheet, so as to make some sense of the nonsense I've attempted to outline above!!

If anyone can help with this little conundrum, it would be greatly appreciated.

Many Thanks,

Northumberland81

It would be better if you had actual dates rather than day numbers - then you could use a SUMIF formula. It would also be better if you put your totals in a separate column so that you didn't need to keep inserting them after every month. You could put them in a summary table which showed each month and the totals for each month.

Hope this helps.

Pete

Thanks Pete and Spencer - in the original version I have on my laptop, I do use dates. I had tried using the SUMIF formula, but to no avail.
The solution you provided in your response, does the trick, so thank you for that.
At the moment, the current spreadsheet covers staff hours/cos, footfall to the business and then sales targets/achieved. Ideally, I'd like to arrange the data eventually, so I can use something akin to a dashboard, to drop down and find any combination of results I need (Spend per head, average transactional value etc) - but I imagine that's a much longer conversation for another day!

Thanks to you both for you help and advice with this, much appreciated.