Calculate price for a driver based on distance

[cool2005]

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.

 

[shina67]

Have you tried a pivot table?

 

[bebo021999]

What amount for >100km? I assume it was 45
Assume A1 is driver name, B2 is km, C2 is amount
C2:

=LOOKUP(B2,{0,30,50,75,100},{10.5,18,25,35,45})

 

[S M C]

If you are looking to just get the price based on the total distance, try something like this. If you are one of those who is very particular about precision, you can increase the precision by adding more zeroes to the 0.1 I used. But I guess you could just stick to this if you're using rounded distance.... like 31 (or 30), instead of 30.3 depending on what's your arrangement with your driver.

=LOOKUP(A1,{0;30;50;75;100}+0.1,{10.5;18;25;35;42.5})

 

[cool2005]

Thank you very much for your feedback.

I tried using:

=LOOKUP(B2,{0,30,50,75,100},{10.5,18,25,35,45})

but i get formula error. Is not cleat to me at this point, if i use input 28KM, would it autocomplete 10.5 dollars?

I don't need more decimals, it will be rounded distance and it will go up to 1500 km with different values.

 

[cool2005]

Have you tried a pivot table?

Not yet, not sure how to implement a pivot table given circumstances.

 

[bebo021999]

Thank you very much for your feedback.

I tried using:

=LOOKUP(B2,{0,30,50,75,100},{10.5,18,25,35,45})

but i get formula error. Is not cleat to me at this point, if i use input 28KM, would it autocomplete 10.5 dollars?

I don't need more decimals, it will be rounded distance and it will go up to 1500 km with different values.

Try to replace "," with ";"?
With more milestone up to 1500km, Its better to create a lookup table then using VLOOKUP or INDEX-MATCH

 

[cool2005]

There are 22 milestones to be filled in and goes up to 1500KM to be more precise. I tried also the following formula but still doesn't work:

 

[bebo021999]

The two vectors in LOOKUP should be in same direction ("," for horizontal and ";" for vertical range)

=LOOKUP(B2,{0;30;50;75;100},{10.5;18;25;35;45})

or

=LOOKUP(B2,{0,30,50,75,100},{10.5,18,25,35,45})

 

[cool2005]

I tried to use for vertical range and i have the following sample table:

On D2 i have value 100 KM

I suspect is something related to syntax, if i look in excel i have the following sample for formula format:

So would be LOOKUP(lookup_value;lookup_vector;[result_vector] - its ; mandatory instead of , after first two elements?