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!!

See if this gives you any ideas....

With
A1: Data Type
B1: Totals
C1:AZ1 contains Day_01, Day_02....Day_50
A2: Expected
A3: Actual

And
B2: (The sales target....eg 100,000)
B3: =SUM(C3:AZ3)

Then
If the day contains an Actual sales amount, that amount is displayed.
Otherwise, this formula subtracts the total actual sales-to-date from
the tota expected sales and calculates the future average expected daily
sales required to meet the target.

C3: =IF(ISNUMBER(C3),C3,($B$2-$B$3)/(50-COUNT($C$3:$AZ$3)))
Copy C3 across through AZ3.

Initially, all cells in C3:AZ3 contain 1,000

After C4: 3000
C3: displays 3000....D3:AZ3 now display: 1,980

Is that something you can work with?