+ Reply to Thread
Results 1 to 14 of 14

calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set 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 set rate

    Id like to calculate TOTAL COST based on user entering mileage into one cell:

    First 2 miles of journey are charged at £3.50, additional miles thereafter ( on the same journey) are charged at £1.50. Any ideas?

    Thanks in advance

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

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

    Hello & Welcome to the Forum,

    Maybe just...

    =(2*3.5)+((A1-2)*1.5)
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    05-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    291

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

    i think wrap it with below considering 1 or 2 km as well

    =IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5))
    Click on * below if you find this helpful

    Thanks,
    A

  4. #4
    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 set rate

    Quote Originally Posted by jeffreybrown View Post
    Hello & Welcome to the Forum,

    Maybe just...

    =(2*3.5)+((A1-2)*1.5)

    in that event 1 (c4) mile is calculated at £5.50 =(2*3.5)+((C4-2)*1.5)
    in that event 2 (c5) miles is calculated at £7.00 =(2*3.5)+((C5-2)*1.5)
    and 3 miles = £8.50

    which is incorrect

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

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

    So we get all the requirements correct the first time...

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  6. #6
    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 set rate

    Quote Originally Posted by amy_d2 View Post
    i think wrap it with below considering 1 or 2 km as well

    =IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5))
    That's solved it, Thank you!!

  7. #7
    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 set rate

    Thanks for asking jeff, think amy_d2 has got it, except for one niggle, when cell is at 0 fr mileage the cost is being calculated at

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

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

    You could just say less than 3...

    ...and check A1 for a number...

    =IF(A1,IF(A1<3,A1*3.5,(2*3.5)+((A1-2)*1.5)),"")

  9. #9
    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 set rate

    £3.32, but when changed to 1 mile calculates correcty as 3.50?

  10. #10
    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 set rate

    Thats true too, will give it a try!

  11. #11
    Forum Contributor
    Join Date
    05-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    291

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

    Hi You can use this


    =IF(A1=0,"",IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5)))

    should have consider 0 too , sorry about that

    Edited

    or use jeffreybrown's formula
    Last edited by amy_d2; 08-16-2013 at 12:21 PM.

  12. #12
    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 set rate

    That works, and bit neater looking, thank you

  13. #13
    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 set rate

    Amy, Jeff,

    If I wanted to slightly change the calculation, can you please help?

    I want 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

    Thanks in advance

  14. #14
    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 set rate

    Amy, Jeff,

    If I wanted to slightly change the calculation, can you please help?

    I want 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

    Thanks in advance,

+ 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 rates based on range of miles
    By big-wheels in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2008, 06:35 PM
  3. Calculate Miles per hour
    By anwittin in forum Excel General
    Replies: 5
    Last Post: 11-03-2006, 05:03 PM
  4. [SOLVED] 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
  5. How do I calculate total hours by rate of pay?
    By FrankF in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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