Formula assistance

[Raisincain]

Hello!

I have posted this question in the more appropriate formula assistance
forum, but since it seems infrequently visted compared to this forum, I
thought maybe someone here could offer some advice.

I have several employees that work for me at $7.00 per hour. They want to
work more hours per week at this same wage, but I am unable to afford
overtime for them.

Department of labor laws state that every hour over 40 per week must be paid
at 1.5 the normal hourly rate. My employees have agreed to take a cut in pay
to enable me to pay them whatever amount is necessary if, when overtime is
applied, they average out to $7.00 per hour. In other words, they are
willing to work for 40 hours per week at x amount and a variable number of
hours for x*1.5 as long as the x +(x*1.5) averages $7.00 per hour.

This number x will change with each schedule since they will work a
different number of hours per schedule (ie an employee may work 55 hours in
week one, 50 hours in week two, 60 hours in week three, and so on). Can
anyone help with a formula that will solve for x based on these changing
hours with the average rate of pay still staying at or around $7.00 per hour?

Thank you,
Brad in Dallas

 

[Roger Govier]

Hi

With Hours actually worked in A1, Standard price per hour ($7) in B1, and
A1*B1 in C1
then
=C1/((40+(A1-40)*1.5))
gives the value per hour to pay

So in A3 enter 40, in B3 enter the formula above and in C3 enter = A3*B3
in A4 enter =A1-A3, in B4 enter = B3*1.5, in C4 enter A4*B4

 

[Raisincain]

Excellent work; thanks Roger!

Hi

With Hours actually worked in A1, Standard price per hour ($7) in B1, and
A1*B1 in C1
then
=C1/((40+(A1-40)*1.5))
gives the value per hour to pay

So in A3 enter 40, in B3 enter the formula above and in C3 enter = A3*B3
in A4 enter =A1-A3, in B4 enter = B3*1.5, in C4 enter A4*B4

 

[Raisincain]

A+!!!

best wishes and I hope we get a good mark for OUR homework

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

 

[Stan Brown]

Sat, 8 Dec 2007 10:18:00 -0800 from Raisincain

I have posted this question in the more appropriate formula assistance
forum, but since it seems infrequently visted compared to this forum, I
thought maybe someone here could offer some advice.

Please don't multipost. If you must post to multiple groups,
crosspost so you don't waste people's time.

I answered your query in the functions group.

 

[Stan Brown]

Sat, 8 Dec 2007 18:43:15 -0000 from Roger Govier

With Hours actually worked in A1, Standard price per hour ($7) in B1, and
A1*B1 in C1
then
=C1/((40+(A1-40)*1.5))
gives the value per hour to pay

So in A3 enter 40, in B3 enter the formula above and in C3 enter = A3*B3
in A4 enter =A1-A3, in B4 enter = B3*1.5, in C4 enter A4*B4

Please note that this is almost certainly illegal under US and state
labor laws.