Calculating %

[Erica]

I am trying to calculate the percent of budget used according to hours worked
and pay rate.

In order to calculate the hours worked i used the following formula:
=IF(((C34-B34)*24)>=5,C34-(B34+TIME(0,30,0)),C34-B34)
on a seperate worksheet I used the formula:
=Schedule!$D$34
for each day of the week and
=SUM(I30,H30,G30,F30,E30,D30,C30)
to add all of the hours worked within that week.
I then used the simple formula of
=J30*B30
to calulate the cost of the labor.

And my question is.....
I now need to figure the percentage of budget used per emolyee on a weekly
basis.
I have tried the formula =k30/c3 but I'm not getting the correct answer...
For example: based on a $100,000 budget an employee worked 22:30 hours and
is at a pay rate of $8.00 per hour. The total cost for labor is $180.00 but
the formula is calculating the percent vs. budget to be 0.01% instead of 0.18%

Please help!

 

[Peo Sjoblom]

Your problems is that this formula


=IF(((C34-B34)*24)>=5,C34-(B34+TIME(0,30,0)),C34-B34)

returns a time value, so if C34 is 18:00 or 06:00 PM and B34 is 10:00

the formula will return 07:30 a time value, now assume that


=SUM(I30,H30,G30,F30,E30,D30,C30)


will return 22:30 then to get the correct amount you need to use

=J30*B30*24 formatted as number or currency to get the correct amount

=22:30*24*8

is


180.00

then

=180/100,000.00 equals 0.0018

which is the same as 0.18%

You can change the first formula to

=IF(((C34-B34)*24)>=5,C34-(B34+TIME(0,30,0)),C34-B34)*24

and format as number or general

then the rest of the formulas should work


--


Regards,


Peo Sjoblom

 

[Erica]

The formula you gave me worked... Thank you so much!!!