+ Reply to Thread
Results 1 to 3 of 3

Calculating Mileage rates based on different rates depending on cumulative miles travelled

  1. #1
    Registered User
    Join Date
    08-15-2017
    Location
    Dublin
    MS-Off Ver
    2016
    Posts
    2

    Calculating Mileage rates based on different rates depending on cumulative miles travelled

    Hi

    Was looking for some help if anybody has any ideas?

    I am currently working on building a spreadsheet that will calculate mileage rate depending on what miles (cumulative) have been traveled per month. I have got the nested formula working but it is calculating the cost on the cumulative figure and not the actual mileage traveled for the particular month.

    I have spent alot of time but cannot get it to calculate on the individuals month total. I think i need to add into the formula to minus previous cumalative figure from current and multiply(taking into consideration all the rates that could be applicable to the total mileage)

    Thanks
    M
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Calculating Mileage rates based on different rates depending on cumulative miles trave

    You did not add the expected result in your file.

    J8=$B8*VLOOKUP($C8,$K$4:$L$7,2,1) and drag down.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-15-2017
    Location
    Dublin
    MS-Off Ver
    2016
    Posts
    2

    Re: Calculating Mileage rates based on different rates depending on cumulative miles trave

    The intended result is as follows

    D8 calculated total should be .45 (1 * .4479)
    D9 calculated totals should be 672.10 (1499*.4479+ 1*.70)
    D10 calculated total should be 2799.30 (3999*.70)

    Thanks
    Marie

+ 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] Calculating penalty rates based on day of the week
    By takotsubo in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-30-2017, 03:42 AM
  2. [SOLVED] Help with creating excel spreadsheet for calculating mileage with multiple rates
    By Annahstar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-29-2016, 01:18 PM
  3. Calculate Mileage Based on 2 Rates
    By jbondbmw in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2014, 09:21 PM
  4. Formula for HMRC Mileage rates
    By Jinky_uk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2013, 01:04 PM
  5. [SOLVED] Mileage Log and Reimbursement Form with Split rates
    By robiniwitz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-28-2012, 02:26 PM
  6. Using a formula to calcualte different mileage rates
    By andrewf in forum Excel General
    Replies: 2
    Last Post: 01-17-2012, 11:59 AM
  7. 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

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