payroll formula (Excel 2002 XP)

[LuceeLou]

<img src=/S/meltdown.gif border=0 alt=meltdown width=15 height=15> Can someone help me... I need to formulate a cell for regular pay to reflect the total for hours worked (48) times the pay rate ($8) with a threshold of 40, then another cell for overtime pay. I need to copy the formula to other cells for different hours worked and some are less than 40 worked.
I'm at a loss!

[verada]

LuceeLou,

Is this the sort of thing your are after?

[HansV]

Welcome to Woody's Lounge!

To get the regular hours worked by Barber (row 2), use =MIN(D2,$D$13). The reference to D2 is relative, and will change when you fill down, the reference to D13 is absolute and will not change. The overtime hours are worked hours - regular hours.

See the attached version. I have inserted several columns to make the calculations easier to follow. You can hide these extra columns, if you like.

[HansV]

Stutz in row 7 has worked only 35 hours, yet receives pay for 40 hours in your version. Can I work for you? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[LuceeLou]

Thanks for the post. I have tried that one. But if you notice Stutz's pay isn't comin out right.

[LuceeLou]

Please. But the spreadsheet has to stay the same layout as I have it.

[LuceeLou]

Thank you so much Hans. I can see where the MIN would be helpful if I were adding a new column. I have to keep it the way it is. Ms. Ward is a stickler for her forms. <img src=/S/bif.gif border=0 alt=bif width=70 height=28>

[HansV]

I don't know who ms Ward is, but here is a version without the inserted columns.

[LuceeLou]

Hans, you are so cool. I copied and pasted the formula you used =MIN(D2,$D$13) and added *C2 and pasted the formula into the original cell E2. That was the correct formula.
Now if I were to remove the F column, what would the formula be to calculate the overtime pay? I tried =(D2-$D$13)*$D$14 but obviously it is wrong.

[HansV]

See my previous reply. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[LuceeLou]

She is a tough boss.
Hans you are the absolute best!!!!!!!
<img src=/S/yep.gif border=0 alt=yep width=15 height=15>
High Regards to you,
LuceeLou

[JATorres]

Here goes the formulas for your Regular Pay and Overtime pay. I attached the completed form.

Overtime formula:
=IF(D2>40,(D2-40)*(C2*$D$14),0)

Regular Pay formula:
=IF(D2<=40,D2*C2,C2*40)

P.S. Is this a homework assignment or for a real job?

[LuceeLou]

Thanks JATorres. It was for an assignment. I worked this for several hours. Hans assisted me with the formulas for me. I had the last cell reference for the wrong cell. You are all great! It's like learning to read without knowing the alphabet! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>