# Thread: payroll formula (Excel 2002 XP)

1. ## payroll formula (Excel 2002 XP)

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

2. ## Re: payroll formula (Excel 2002 XP)

LuceeLou,

Is this the sort of thing your are after?

3. ## Re: payroll formula (Excel 2002 XP)

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.

4. ## Re: payroll formula (Excel 2002 XP)

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>

5. ## Re: payroll formula (Excel 2002 XP)

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

6. ## Re: payroll formula (Excel 2002 XP)

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

7. ## Re: payroll formula (Excel 2002 XP)

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>

8. ## Re: payroll formula (Excel 2002 XP)

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

9. ## Re: payroll formula (Excel 2002 XP)

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.

10. ## Re: payroll formula (Excel 2002 XP)

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

11. ## Re: payroll formula (Excel 2002 XP)

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

12. ## Re: payroll formula (Excel 2002 XP)

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?

13. ## Re: payroll formula (Excel 2002 XP)

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>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•