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

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

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

Create a table and the formula is short and simple!

Show us ALL of the levels and the corresponding 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.

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)