Mileage in Excel (or in something else)
October 6, 2015 1:20 PM   Subscribe

There has to be a way that I can track my mileage in Excel.

I have to track mileage in Excel for work. They do not want me to give odometer readings for the mileage. This works out in my favor because if I go somewhere for lunch that isn't covered so I would have to subtract out those miles anyway.

I work at 5 different locations so I have many possible combos of location x to location y to keep track of. Right now I just have a list of the mileage both to and from each building and I add them all together.

It seems like there must be an excel formula where I can convert "LocationA to LocationB" automatically to a number in Column C that I could then subsequently add. Obviously I could do a find/replace, but then I would lose the original information.

I know about using the N function for text, but that just seems to replace text with a 0.
posted by aetg to Computers & Internet (5 answers total) 1 user marked this as a favorite
 
Best answer: This calls for VLOOKUP!

So to be clear, you have 5 places, A-E, and 10 different mileage combinations?

On a different sheet, make a table with the mileage -- AB, AC, AD, AE, BA, BC, etc. This will give you 20 combinations, but it makes things a bit easier. Then on your main sheet, column A will be the right combination (AB, AC, BD, DA, etc).

Column B:

=VLOOKUP(A1,Sheet2!$A$1:$B$20,2,FALSE)

What this means is that it will look at cell A1 (this will change if you copy it to the next row) and then remember what's in it (AB, CA, whatever). After that it will go to Sheet2 and look at the first column in the table in A1:B20 (the $s mean it won't change), and if it finds an exact copy (FALSE), it will take what's in the next column (2) and put it there.

That's probably clear as mud, but VLOOKUP is great and I suggest you research it a bit if you use Excel at all.
posted by jeather at 1:31 PM on October 6, 2015 [5 favorites]


I think there are only 10 different routes since going from A to B is the same as going from B to A.

Also Data Validation will allow you to make a list so you just have a pull down menu of all the possibilities.
posted by Confess, Fletch at 1:53 PM on October 6, 2015


There are 10 routes, but it's easier to double it up on the other end so you don't have to remember which order to write them in.
posted by jeather at 1:55 PM on October 6, 2015 [1 favorite]


Why not just use a mileage tracking app? MileIQ is one, but there are others too.
posted by judith at 2:17 PM on October 6, 2015


Response by poster: jeather--I will look into that. I forgot my charger for my work computer at work, so I won't be able to until tomorrow, but that sounds like something I've used in the past.

judith--I could use a mileage tracking app, but the way I have to submit my mileage requires me to list all the different places I've gone and how many miles per day and print that out. I think excel would give me something close to what they want. (with the odometer starting at "0" every day also). I guess if there is a mileage tracking app that could be that specific I could use it.
posted by aetg at 5:16 PM on October 6, 2015


« Older Do I need a desktop or laptop?   |   Seeking dentist in Round Rock/Cedar Park TX area Newer »
This thread is closed to new comments.