Calculate price for a driver based on distance

cool2005

New Member
Joined
Sep 22, 2022
Messages
5
Office Version
  1. 2016
Hi everyone,

Need your help if possible to create a spreadsheet in Excel 2016 that will calculate the price for my driver based on multiple conditions and driven distance:

For example if driver goes for 0-30km will get 10.50 dollars, if it drives between 31-50km will get 18 dollars, if will drive between 51-75KM will get 25 dollars and so on, 76-100 km will get 35 dollars. i was thinking to do it with a formula or make a table first in excel with this values and get the data from there.

basically what i need will be a table that contain name of the driver, driven kilometers and then i will get the price column automatically completed based on specific values:

if driver made 25 km column price will be autofilled with 10.50 dollars
if driver made 71 km will get 25 dollars automatically

i will only insert driven KM and price will be automatically calculated.
 
1663835003505.png
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
T202209a.xlsm
DEFGHIJ
1DistanceRate
21810.5010.5010.50010.50
33518.0018.0018.0030.000118.00
48025.0025.0025.0075.00125.00
53010.5010.5010.50100.00135.00
610025.0025.0025.00
7
5b
Cell Formulas
RangeFormula
E2:E6E2=LOOKUP(D2,$I$2:$J$5)
F2:F6F2=LOOKUP(D2,{0,10.5;30.0001,18;75.001,25;100.001,35})
G2:G6G2=VLOOKUP(D2,$I$2:$J$5,2.1)


The above shows two versions of the same formula.
You can paste the post into a clean sheet. Click on the icon below the f(x) in the header and paste to your sheet.
N.B. Note the type of brackets etc.
I also added an example with Vlookup.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,836
Messages
6,181,250
Members
453,026
Latest member
cknader

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top