Formula(?) for working out difference of accruing hours against set hours per fortnight shown in 1 cell

[Gelaw]

Hi,
Can anyone help with the following?
I have a spreadsheet with yearly hours budget/actual for my dept. of 24000 hours, which is divided into 26 fortnights giving 923hrs/fortnight. In 1 row I have the budget filled in for the year(26 cells of 923 hours). In the row below this, I have the actual worked hours, which are updated fortnightly.

Is there a formula which would show a running difference between fixed budgeted hours and accrued hours, shown in 1 cell, which gets automatically updated as the previous fortnight's actuals are updated, so I can quickly see how w are tracking?


Any help appreciated.
/Gelaw

 

[sinned141]

if budgeted start in b2 and actual start in b3 then I would just use
In b4 =sum(b2-b3)
in c4 =sum(c2-c3)+b4
then drag the c4 along all the other cells in the row

HTH

 

[Gelaw]

if budgeted start in b2 and actual start in b3 then I would just use
In b4 =sum(b2-b3)
in c4 =sum(c2-c3)+b4
then drag the c4 along all the other cells in the row

HTH

Hi,
Thanks for your quick reply. This method works, but is there a way to represent the running difference between budget/accrued in a separate, single cell?
cheers

 

[sinned141]

Hi

I am sure there is a way to do this and several people on here will probably be able to answer you in minutes. Just in case no one else answers though I'll try and work it out.

Are your 26 fortnights headed with a date or just numbered 1-26

 

[Gelaw]

Thanks Sinned141, the 26 fortnights are just numbered.
cheers

 

[sinned141]

Ok
Assuming still that budgeted starts in b2 and actual starts in b3 and assuming that actual is 0 or blank until filled in
In the cell that you want the running total put =(SUM((COUNTA(B3:AA3))*923))-(SUM(B3:AA3))
This should work, if not hopefully someone who knows what they’re doing will be able to help
HTH

 

[sinned141]

Ok
assuming that actual is 0 or blank until filled in

Sorry that should say "assuming that actual is blank until filled in
I don't think it will work if there are zeros in the cells for the coming weeks

 

[Gelaw]

Much thanks sinned141, you're a genius, this works perfectly