Averaging A Varying Total Across A Number Of Cells

Afternoon All,

I am currently trying to complete a spread sheet but am struggling with a particular calculation, I will try and explain this the best way I can.

I want to try and spread a figure across a whole column and then add the specific totals to another to give us a daily figure... for example...

We have an Expected Sales Column which is divided (not equally) across a 50 day period to give us a target for each day, we also have a Daily Actual Column which we enter manually any figures we have taken for that day.

What we need to do is take the varience (eg. Expected = £440, Daily Actual = £100, so the varience would be £340) and spread it across the remainder of the Sale period so it increases/decreases our Expected per day (so for day one when the varience was £340 it would then be divided by the remaining days - 49 and added to the new target for day 2,3,4,5,6 etc.)

We've added a varience column but we're struggling for the accurate calculation to work out the next days target (so instead of diving by 50, it'll reduce to 49, 48, 47 and so on as the days go by with an average amount taken or added to each day depending on what intake we've had.

I hope that one of you will understand that enough to help!


I would be extremely grateful if anyone could assist me in this increasingly frustrating task.

Thank you!!