+ Reply to Thread
Results 1 to 10 of 10

Formula for payroll with varrious pay rates

  1. #1
    Registered User
    Join Date
    03-29-2015
    Location
    Ontario Canada
    MS-Off Ver
    2014
    Posts
    3

    Formula for payroll with varrious pay rates

    Hello,

    I am trying to use a formula for drivers pay where the rate of pay will vary depending on the length of the trip.

    For example between 0 and 300 miles pays $0.70 per mile, and $30.00 per delivery, where as a trip between 301 and 625 miles will pay $0.65 per mile and $25.00 per delivery.

    I want to be able to just enter the millage and deliveries and have the formula figure it out. One cell for millage and one for deliveries.

    Thanks.

  2. #2
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Formula for payroll with varrious pay rates

    Hi William,

    I have attached a spreadsheet with what I think you want.
    Please note columns C and E are hidden and all the formulas only go as far as row 20.

    Regards

    Peter C
    Attached Files Attached Files

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for payroll with varrious pay rates

    Go to this page:

    http://contextures.com/xlFunctions02.html

    Scroll down to this section:

    Create a VLookup formula for a range of values
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Formula for payroll with varrious pay rates

    You can also do it this way:

    Assume your mileage is in cell A2, and the # of deliveries on that trip are in cell B2, try this formula in C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope it helps.

    - Moo

  5. #5
    Registered User
    Join Date
    03-20-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    18

    Re: Formula for payroll with varrious pay rates

    Quote Originally Posted by Moo the Dog View Post
    You can also do it this way:

    Assume your mileage is in cell A2, and the # of deliveries on that trip are in cell B2, try this formula in C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope it helps.

    - Moo
    Very interesting take on it. I was under the impression that MS is removing Lookup going forward; regardless, great logic. I've always generally used INDEX or VLOOKUP for similar problems.

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Formula for payroll with varrious pay rates

    Quote Originally Posted by dos mios View Post
    I was under the impression that MS is removing Lookup going forward
    I haven't heard anything about that, but we are going to be very busy around here if they do... there are a LOT of formulas that I have seen on these boards that use it! =)

    - Moo

  7. #7
    Registered User
    Join Date
    03-29-2015
    Location
    Ontario Canada
    MS-Off Ver
    2014
    Posts
    3

    Re: Formula for payroll with varrious pay rates

    =A2*LOOKUP(A2,{0,301},{0.7,0.65})+B2*LOOKUP(A2,{0,301},{30,25})


    so how could I use this to calculate the pay with the varying rates, as the rates change for millage from 301 to 625. I want to be able to put all the data in one cell, not a different cell as the mileage changes. all the rates are based on the length of trip, and I have four different categories for pay.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for payroll with varrious pay rates

    Create a table and the formula is short and simple!

    Show us ALL of the levels and the corresponding rates.

  9. #9
    Registered User
    Join Date
    03-29-2015
    Location
    Ontario Canada
    MS-Off Ver
    2014
    Posts
    3

    Re: Formula for payroll with varrious pay rates

    Quote Originally Posted by Tony Valko View Post
    Create a table and the formula is short and simple!

    Show us ALL of the levels and the corresponding rates.
    all rates are based on the mileage of the trip
    m=km traveled, r=rate per km, d=delivery

    if m=<300, the r=$0.275 and d=$28.00
    if m<301 and>626, then r=$0.265 and d=$26.50
    if m<626 and >800, then r=$0.26 and d= $25.00
    if m<800, then r=$0.25 and d=$23.50

    I would like to be able to enter the values in one cell, for each given day of the week.

    for example a trip of 729 km with 3 deliveries on Monday and a trip of 407 km with 7 deliveries would be entered as;

    KM Del.
    Monday 729 3
    Tuesday 407 7

    and so forth for the rest of the week. Based on the data in these two columns, the pay will be calculated at the proper rate and displayed in a 4th column.
    Last edited by William B 1965; 03-30-2015 at 05:18 PM.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for payroll with varrious pay rates

    Like this...

    Data Range
    A
    B
    C
    D
    1
    Distance
    Deliveries
    Amount
    2
    Monday
    729
    3
    264.54
    3
    Tuesday
    407
    7
    293.36
    4
    5
    6
    7
    8
    From
    To
    Rate
    Delivery
    9
    0
    300
    0.275
    28
    10
    301
    626
    0.265
    26.5
    11
    627
    800
    0.26
    25
    12
    801
    801+
    0.25
    23.5


    You don't actually need the "To" column but I included it to make it easier to see the levels.

    Depending on how you want to charge you need to round the results. You can either round each component (distance and deliveries) or you can round the combined result (distance plus deliveries).

    One of these formulas entered in D2 and copied down:

    =ROUND(LOOKUP(B2,A$9:C$12)*B2,2)+ROUND(LOOKUP(B2,A$9:D$12)*C2,2)

    =ROUND(LOOKUP(B2,A$9:C$12)*B2+LOOKUP(B2,A$9:D$12)*C2,2)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Payroll Start and End Dates, need Payroll Period fix
    By colarguns in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-12-2014, 08:03 PM
  2. Replies: 0
    Last Post: 01-10-2013, 03:07 PM
  3. Adding payroll stubs payroll calculator
    By Sable in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-05-2006, 12:40 PM
  4. [SOLVED] need help with a payroll formula
    By Frustrated in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2006, 04:45 AM
  5. Replies: 0
    Last Post: 01-29-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1