Calculate Price based on Number of Miles

[antonyx]

Hi guys, I have an urgent request.

I run a cab firm and am trying to write out a formula in excel. The reason I'm telling you it's a cab firm is because it will give you a better understanding of why I'm trying to create this formula.

Here is how my formula works in plain english:

For the first 1 Miles Charge £10.00 Flat Rate
For the next 29 Miles Charge £2.20 for every 1 mile
For the next 5 Miles Charge £2.15 for every 1 mile
For the next 999 Miles Charge £1.70 for every 1 mile

I have a set of mileage figures in column 1, and in column 2 I want this formula to be applied to give the correct figure.

Here is how it should look:

Column 1 Column 2
1 10
5 18.80
10 29.80
15 40.80
20 51.80
25 62.80
30 73.80
35 84.55
40 93.05
45 101.55
50 110.05

I imagine it would be something like this...

If c1= <1 then c2=10

If c1= 1to30 then c2=10+((c1-1)*2.2)

If c1= 30to35 then c2=10+63.8+((c1-31)*2.15)



Does this make sense... PLEASE HELP ME!!!

[etaf]

i have hard coded into a nested IF statement
=IF(A2>35,((A2-35)*1.7)+(10+(29*2.2)+(5*2.15)),IF(A2>30,((A2-30)*2.15+(10+(29*2.2))),(10+((A2-1)*2.2))))

rather than just used a value to add to the cells- i have put it into the formula in long hand so you can see each step
and make it easier to modify if a rate or threshold changes
as the first part here ((A2-35)*1.7)+(10+(29*2.2)+(5*2.15))


although you may want to use a lookup table, as then the rates can be quickly changes

i'll think about that - meanwhile see attached

[antonyx]

Hi there, thank you very much for the rapid response.

I gave that example because I thought it would be easy for me to add and remove steps, this is not the case.

I think it may be better to use a look-up table but I wouldn't know where to begin as I've never used one.

In the attached file I've written the actual steps I need (12 steps as opposed to 4).

If there is chance you could convert these to a look-up table OR write out the formula in it's entirety that would be brilliant. If it's too much work I completely understand.

Thanks again.miles.xlsx

[etaf]

so i may have made the tables a little more complicated then they needs to be
but 1st pass at this

I have setup a lookup table to provide the charges for the mileage rates
R4 to T16
that sets out the thresholds - and so you can change those thresholds and rates anytime

then I found the easiest way - was to lookup for every mile what the rate would be for that mile
Then I simply added all the previous miles - so you get the correct price in the table for 1-1000 miles
table in V,W & X

i'm sure theres a better way - but the IF statement was getting quite complex
and this is just driven from a simple table in R,S,t

so did you mean 2 miles at 50p ?

anyway have a look see what you think

The tables etc can all be on a separate sheet if required

[antonyx]

Really nice work. Thank you very much. This is perfect and easy to edit.

[etaf]

your welcome, thanks for the rep

[antonyx]

Hi again. I have a slight problem.

In the spreadsheet you have given me I am able to enter a value in the yellow box (A2) and the correct value will display next to it (B2)...

However, my intention is to enter a large column of data into column A and have the respective rates display in column B.

At the moment the formula is only applied to row 2.

Can you adjust the spreadsheet so I'm able to paste an entire column of 'miles' into column A and the respective values are calculated in column B?

Thanks

*I would try myself but have never used lookup tables before and am unsure as to what to reference.

[etaf]

this is the formula
=VLOOKUP(A2,$V$4:$X$1186,3,FALSE)
and you can copy that down the column

But you may want the lookup table to be in a separate sheet on the workbook

How is your Spreadsheet (live version) laid out ?

[antonyx]

Thanks a lot for the rapid response!

Basically I'm going to copy and paste a very long single column of mileage values into the spreadsheet (and take them out when I'm done).

It would be preferable if the lookup table was on a separate sheet

[antonyx]

I also tried dragging the formula down the column to replicate the formula but it only worked for a certain number of rows.. (see screenshot)

screen.jpg

[etaf]

hows this,

see data - enter the data miles into the sheet and it looks up in the table sheet