Results 1 to 13 of 13
Thread: payroll formula (Excel 2002 XP)

20050408, 05:28 #1
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20050408, 06:18 #2
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 473
 Thanks
 66
 Thanked 2 Times in 1 Post
Re: payroll formula (Excel 2002 XP)
LuceeLou,
Is this the sort of thing your are after?

20050408, 06:24 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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.

20050408, 06:27 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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>

20050408, 06:28 #5
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050408, 06:29 #6
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: payroll formula (Excel 2002 XP)
Please. But the spreadsheet has to stay the same layout as I have it.

20050408, 06:32 #7
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>

20050408, 06:45 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: payroll formula (Excel 2002 XP)
I don't know who ms Ward is, but here is a version without the inserted columns.

20050408, 06:46 #9
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050408, 06:48 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: payroll formula (Excel 2002 XP)
See my previous reply. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20050408, 06:55 #11
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050409, 17:07 #12
 Join Date
 Apr 2005
 Location
 Columbus, Ohio, USA
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
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,(D240)*(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?

20050409, 18:08 #13
 Join Date
 Apr 2005
 Location
 Mesa, Arizona, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>