# Formula for payroll with varrious pay rates

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

3. ## Re: Formula for payroll with varrious pay rates

http://contextures.com/xlFunctions02.html

Scroll down to this section:

Create a VLookup formula for a range of values

4. ## 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:
`Please Login or Register  to view this content.`
Hope it helps.

- Moo

5. ## Re: Formula for payroll with varrious pay rates

Originally Posted by Moo the Dog
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:
`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. ## Re: Formula for payroll with varrious pay rates

Originally Posted by dos mios
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. ## 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. ## 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. ## Re: Formula for payroll with varrious pay rates

Originally Posted by Tony Valko
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.

10. ## 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)

##### Users Browsing this Thread

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

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