Formula for calculating target hours against actual hours

[Bootface]

Hi
I am currently trying to create a formula that will change my targete
hours based on the actual hours that I complete each week. For exampl
I have 300 hours split evenly over 52 weeks to give me my target hour
for each week. Column A shows the week number, Column B shows th
targeted hours for the week, Column C is where I input the actual hour
completed. When I input the hours in column C I need the target hour
for the remaining weeks to change so that I can see if they are o
target to achieving the 300hrs in the year.

I did try a formula that calculated total targeted hours minus actua
hours completed and then divide by the remaining weeks but my formul
also changed the historical data so I am a bit stuck. If anyone coul
assist and provide me with the correct formula I would be very grateful
Thank

 

[Claus Busch]

Hi,

Am Tue, 4 Jun 2013 12:26:34 +0100 schrieb Bootface:

I am currently trying to create a formula that will change my targeted
hours based on the actual hours that I complete each week. For example
I have 300 hours split evenly over 52 weeks to give me my target hours
for each week. Column A shows the week number, Column B shows the
targeted hours for the week, Column C is where I input the actual hours
completed. When I input the hours in column C I need the target hours
for the remaining weeks to change so that I can see if they are on
target to achieving the 300hrs in the year.

in B1 try:
=300/COUNT(A1:A60)
in B2 try:
=IF(C1=0,B1,(300-SUM($C$1:C1))/COUNT(A2:$A$60))
and copy down to the last week number


Regards
Claus Busch

 

[Bootface]

You are a superstar!! Thank you very very much Claus :-

 

[Bootface]

Hi Claus
Just realised that as I am adding my actual hours to column C it i
changing the years target which should stay static at 300hrs. Fo
example if the target is 5.769 hrs per week in column B and I add 2.5hr
to column C, the target hours in column B should go up to reflect th
difference of 5.769 and 2.5. Can you advise what the amendment shoul
be or let me know if you need further info?
Many Thank

 

[Claus Busch]

Hi,

Am Tue, 4 Jun 2013 14:17:09 +0100 schrieb Bootface:

Thank you very very much Claus

you're welcome. Thank you for the feedback


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Tue, 4 Jun 2013 14:57:30 +0100 schrieb Bootface:

Just realised that as I am adding my actual hours to column C it is
changing the years target which should stay static at 300hrs. For
example if the target is 5.769 hrs per week in column B and I add 2.5hrs
to column C, the target hours in column B should go up to reflect the
difference of 5.769 and 2.5. Can you advise what the amendment should
be or let me know if you need further info?

if your target is 300 hours per year (52 weeks) , you have to work
5.77 hours a week. If you work 2.5 hours in the first week and you enter
this value in C1 you have to work 297.5 hours in the following 51 weeks.
And the will be a weekly target of 5.83 hours. If you work more than
5.77 hours in one week, the weekly target becomes smaller.
51 * 5.83 + the worked 2.5 hours = the target of 300 hours

If you have header you can calculate your yearly target:
=SUM(OFFSET($C$1,1,,COUNT($C:$C)))+SUM(OFFSET($B$1,COUNTA($C:$C),,52-COUNT($C:$C)))
and without headers:
=SUM(OFFSET($C$1,,,COUNT($C:$C)))+SUM(OFFSET($B$1,COUNTA($C:$C),,52-COUNT($C:$C)))


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Tue, 4 Jun 2013 14:57:30 +0100 schrieb Bootface:

Just realised that as I am adding my actual hours to column C it is
changing the years target which should stay static at 300hrs. For
example if the target is 5.769 hrs per week in column B and I add 2.5hrs
to column C, the target hours in column B should go up to reflect the
difference of 5.769 and 2.5. Can you advise what the amendment should
be or let me know if you need further info?

the years target will not be changed. Only the weekly target. Please
have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook "Weekly target"


Regards
Claus Busch

 

[Bootface]

Thanks again Claus this is really good. Just one more query if I hav
some weeks where 0 hours are completed my target figure in F2 changes
or shows as #REF!. Is there a way of stopping this? Thank

 

[Claus Busch]

Hi,

Am Tue, 4 Jun 2013 17:44:05 +0100 schrieb Bootface:

Thanks again Claus this is really good. Just one more query if I have
some weeks where 0 hours are completed my target figure in F2 changess
or shows as #REF!. Is there a way of stopping this?

If you have cells with 0 then change your formula in B2 to:
=IF(C1="",B1,(300-SUM($C$1:C1))/COUNT(A2:$A$60))

Do you have headers in your table? The error #REF! is not because the 0.
If you have the formula for a table with headers and you delete the
first row the error comes.
If you don't have headers use:
=SUM(OFFSET($C$1,,,COUNT($C:$C)))+SUM(OFFSET($B$1,COUNTA($C:$C),,52-COUNT($C:$C)))
If you have headers use the formula of the workbook in SkyDrive


Regards
Claus Busch

 

[Bootface]

I do have headers and that has worked a treat. Thanks again really i
appreciated :-

 

[Bootface]

Hi Claus
Me again with another question on this!! If I needed to change th
target hours so that they reflect a different figure in cell F2 how do
go about this? I now need to also show the following on separat
worksheets.
150 target hours over 52 weeks
250 target hours over 52 weeks
208 target hours over 36 weeks
202 target hours over 35 weeks
214 target hours over 37 weeks

Is there a simple way whereby I can change the number of weeks an
target hours easily in order to get the results I need? I know tha
there could also be future variations on the above so if we could creat
2 cells where i just have to input the overall target hours and th
weeks in order to get my results based on the weekly actual hours that
input into my spreadsheet, that would be fantastic - is this possible?

Thanks

 

[Claus Busch]

Hi,

Am Wed, 5 Jun 2013 17:20:16 +0100 schrieb Bootface:

150 target hours over 52 weeks
250 target hours over 52 weeks
208 target hours over 36 weeks
202 target hours over 35 weeks
214 target hours over 37 weeks

write your years target in E2 and write in A1 only the number of weeks
you want to work
E.g. you write in E2 214 and in A only 1 to 37, then change your
formula in B2:
=$E$2/COUNT(A2:A61)
and in B3:
=IF(C2="",B2,($E$2-SUM($C$2:C2))/COUNT(A3:$A$61))
Or have a look for your workbook in SkyDrive


Regards
Claus Busch

 

[Bootface]

Fantastic, thank you so very much