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

Gelaw

New Member
Joined
Jan 23, 2014
Messages
6
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sinned141

Board Regular
Joined
May 4, 2009
Messages
115
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

New Member
Joined
Jan 23, 2014
Messages
6
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

Board Regular
Joined
May 4, 2009
Messages
115
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

New Member
Joined
Jan 23, 2014
Messages
6

ADVERTISEMENT

Thanks Sinned141, the 26 fortnights are just numbered.
cheers
 

sinned141

Board Regular
Joined
May 4, 2009
Messages
115
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

 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top