Formula for calculating target hours against actual hours

[Bootface]

Hi
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.

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




--
Bootface

[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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Bootface]

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




--
Bootface

[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

[Bootface]

Hi Claus
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?
Many Thanks




--
Bootface

[Claus Busch]

Hi,

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

[Claus Busch]

Hi,

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

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

[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? Thanks




--
Bootface

[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

[Bootface]

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




--
Bootface

[Bootface]

Hi Claus
Me again with another question on this!! If I needed to change the
target hours so that they reflect a different figure in cell F2 how do I
go about this? I now need to also show the following on separate
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 and
target hours easily in order to get the results I need? I know that
there could also be future variations on the above so if we could create
2 cells where i just have to input the overall target hours and the
weeks in order to get my results based on the weekly actual hours that I
input into my spreadsheet, that would be fantastic - is this possible?

Thanks!




--
Bootface

[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

[Bootface]

Fantastic, thank you so very much :-).




--
Bootface