+ Reply to Thread
Results 1 to 6 of 6

calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rate

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    13

    calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rate

    I want a formula to perform the following:

    the first 2 miles to be charged at 3.50 inclusive; for example regardless if they travel 1/2 mile, 1 mile, 1.5 miles or 2 miles, the charge is 3.50. Once that threshold is reached, anything in excess of 2 miles is charged an additional 1.67 per mile

    Therefore a passenger travelling:

    1 mile charge = 3.50, 2 miles = 3.50, 3 miles = 4.67, 4 miles = 6.84

    The current formula I have just needs tweaked =IF(C4<3,C4*3.5,(2*3.5)+((C4-2)*1.67)) since the results are as follows:

    1 Mile 3.50
    2 Mile 7.00
    3 Mile 8.67
    4 Mile 10.34



    Thanks in advance,

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rat

    =if(c4<=2,c4*3.5,3.5+((c4-2)*1.67))

  3. #3
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rat

    Quote Originally Posted by daffodil11 View Post
    =if(c4<=2,c4*3.5,3.5+((c4-2)*1.67))
    Based on revised formula this is what Ive got, but I know this is incorrect e.g 2 miles should still be 3.50, I think all other calculations are fine, just the "2 miler"

    =IF(C4<=2,C4*3.5,3.5+((C4-2)*1.67))

    Mileage Transport Cost (3.50 per mile, for first 2 miles) then 1.67
    0 0.00
    1 3.50
    2 7.00
    3 5.17
    4 6.84
    5 8.51
    6 10.18

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rat

    Sorry, brain fart.

    =IF(C4<=2,3.5,3.5+((C4-2)*1.67))

  5. #5
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rat

    Quote Originally Posted by daffodil11 View Post
    Sorry, brain fart.

    =IF(C4<=2,3.5,3.5+((C4-2)*1.67))
    Thats it, thank you, very much appreciated!

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter at different rat

    Thanks for the feedback. Glad I could help.


+ 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. [SOLVED] Need a formula that will calculate miles between multiple destinations
    By gus.tavera in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-09-2017, 07:00 PM
  2. 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
  3. 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
  4. Calculate Miles per hour
    By anwittin in forum Excel General
    Replies: 5
    Last Post: 11-03-2006, 05:03 PM
  5. How to use miles per hour and mileage to estimate travel time
    By OhioSnowBird13 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2006, 10:15 AM

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