Results 1 to 13 of 13
  1. #1
    Lounger
    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!

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: payroll formula (Excel 2002 XP)

    LuceeLou,

    Is this the sort of thing your are after?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  5. #5
    Lounger
    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.

  6. #6
    Lounger
    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.

  7. #7
    Lounger
    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>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: payroll formula (Excel 2002 XP)

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

  9. #9
    Lounger
    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: payroll formula (Excel 2002 XP)

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

  11. #11
    Lounger
    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

  12. #12
    New Lounger
    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,(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. #13
    Lounger
    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>

Posting Permissions

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