+ Reply to Thread
Results 1 to 11 of 11

Calculate Price based on Number of Miles

  1. #1
    Registered User
    Join Date
    04-23-2007
    Posts
    31

    Angry Calculate Price based on Number of Miles

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

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,524

    Re: Calculate Price based on Number of Miles

    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
    Attached Files Attached Files
    Last edited by etaf; 05-14-2014 at 04:15 PM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-23-2007
    Posts
    31

    Re: Calculate Price based on Number of Miles

    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

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,524

    Re: Calculate Price based on Number of Miles

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-23-2007
    Posts
    31

    Re: Calculate Price based on Number of Miles

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

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,524

    Re: Calculate Price based on Number of Miles

    your welcome, thanks for the rep

  7. #7
    Registered User
    Join Date
    04-23-2007
    Posts
    31

    Re: Calculate Price based on Number of Miles

    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.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,524

    Re: Calculate Price based on Number of Miles

    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 ?

  9. #9
    Registered User
    Join Date
    04-23-2007
    Posts
    31

    Re: Calculate Price based on Number of Miles

    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

  10. #10
    Registered User
    Join Date
    04-23-2007
    Posts
    31

    Re: Calculate Price based on Number of Miles

    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

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,524

    Re: Calculate Price based on Number of Miles

    hows this,

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate
    By infinite2006 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-30-2013, 08:26 AM
  2. calculate price between max and min price using percentage number
    By pzouboul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2013, 05:59 PM
  3. Replies: 6
    Last Post: 04-03-2012, 12:36 AM
  4. Replies: 5
    Last Post: 02-06-2012, 06:21 AM
  5. Calculate rates based on range of miles
    By big-wheels in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2008, 06:35 PM

Tags for this Thread

Bookmarks

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