current average turnover by months

K

kimfag

Two columns Months and turnover, i would like to return if you are ahead of
or behind the monthly needed average turnover. So the function needs to see
if the turnover column/cells has information or not to be able to return the
correct amount of months left in teh year (divided on xx months left).

Months Turnover
Januar 100 000
Februar 150 000
Mars 500 000
April
Mai
Juni
Juli
August
September
Oktober
November
Desember



Budsjett 2009 7 600 000
Akkumullert omsetning 750 000

snitt oms. pr. måned 633 333
snitt pr uke (52) 146 154


Gjenstående budsjett 6 850 000
Nytt snittpr mnd 761 111
On scedule or behind -127 778

example shows the person are -12778 behind schedule with 9 months to go (we
have 3 months turnover).
 
R

Roger Govier

Hi

With Months in A2:A13, and Values in B2:B13 and
Total Budget for Year in cell B15 then

Current Shortfall
=SUM(B2:B13)-(COUNTIF(B2:B13,"<>")/12)*$B$15

Monthly Amount required for remaining months in order to reach budget
=B15-SUM(B2:B13)/(12-COUNTIF(B2:B13,"<>"))
 
Top