Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: mileage expenses spreadsheet

  1. #1

    mileage expenses spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    Not sure if this is the right area to post this in (mod's feel free to move it) but here goes.

    I am trying to create a spreadsheet for the directors and management staff to claim their mileage expenses. I have created tables in excel with total miles between each possible starting point and destination. I want to create the spreadsheet so that we can just select the various destinations travelled to (for instance all journeys begin at livingston and most will end here too) in order and the total mileage travelled is totalled at the end of the row. there will be a row per day for each (up to 31) day of the month. My question is, what would be the best formaula to use to extract the correct data from the correct table depending on the previous location (either start point or most previous destination). The fact that there are 35 seperate tables as we have a possible 35 sites that we visit at various times, is causing me a little bit of a headache. I am by no means even remotely confident in my excel abilities but am keen to learn, so any help, pointers, abuse would be most appreciated.

    Regards

    H

  2. #2
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    I would have my data in one big table, then you can use sumifs to match the mileage amount to the start point column, and the end point column

    data:
    start finish mileage
    point A point B 100
    point A Point C 150
    point A Point D 200
    point A Point E 250
    point A Point F 300
    Point B Point A 101
    Point B Point C 151
    Point B Point D 201
    Point B Point E 251
    Point B Point F 301


    spreadsheet:
    Date start point end point mileage
    8/1/2011 point a point b 100


    formula for mileage is =SUMIFS(C:C,A:A, f2,B:B, G2)

    where c:C is the column containing the mileage numbers, a:a is the start points, B;B is the finish points in the data, and f2 is the user entered start point, and g2 is the user entered end point (has to match perfectly to work)

    i would use data validation on a named range of all the location gchoices to create a pull down list that way you know that it`s actually going to match the data.

    you can use the indirect formula to make it look in seperate tables if you don`t want to merge all the data into one place.

    i think sumifs only works in excel 2007 or better.

  3. #3
    Have a matrix like so

    ............. Livingston....Stranraer....Arbroath....etc
    Livingston......-...............40............50..............
    Stranraer.......40.............-..............25..............
    Arbroath........50............25.............-................

    and use MATCH to match column and row and INDEX to get the mileage.

  4. #4
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    wow, that's so much smarter than my solution

    *bows*

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    Have a matrix like so

    ............. Livingston....Stranraer....Arbroath....etc
    Livingston......-...............40............50..............
    Stranraer.......40.............-..............25..............
    Arbroath........50............25.............-................

    and use MATCH to match column and row and INDEX to get the mileage.
    dude thats so simple yet so clever. will give that a try. might need to pop back and ask for hints on how to use MATCH and INDEX commands.

  6. #6
    Quote Originally Posted by JYool View Post
    i think sumifs only works in excel 2007 or better.
    It does, it was introduced in Excel 2007. But, you can achieve the same results with an array formula or SUMPRODUCT, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

  7. #7
    ok so i have my massive table of data (mileages) but I am totally stumped as to what to do next. my initial thoughts were to have my data hidden, but have the spreadsheet on the front with the following columns:

    day|start|dest1|mileage|dest2|mileage|dest3|mileage|dest4|mileage|dest5|mileage| total mileage

    where mileage is the miles travelled between destinations. and the total mileage is obviously the total of all individual mileages. should be simple yet my brain cannot figure it out.

    MATCH and INDEX?

  8. #8
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  9. #9
    I have already downloaded these templates, but they still require an awful lot of filling in manual and looking up tables for mileages etc. The second one is kind of what i would like to create, but given we have 35 different sites (36 including HO) the lookups get a little complicated for a novice like me.

    i've attached a copy of the mileages tables, to give you an idea of how much data there is to select from.
    Also on the next page of the attached spreadsheet is roughly how i thought it would look if i could get the formulae correct.
    mileage lists.xlsx

    any help/abuse more than welcome.

  10. #10
    Post your workbook so we can see some actual data.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •