ajd1992 From UK - England, joined Jul 2006, 2645 posts, RR: 6 Posted (6 months 2 weeks 21 hours ago) and read 516 times:
Hey,
I want to create a car mileage tracker spreadsheet, and I had a crack at creating my own, but I'm awful with formulae and things like that.
I found this on the Office Templates site (http://office.microsoft.com/en-us/te...lates/mileage-log-TC006088458.aspx) and was wondering - could I add an extra time column so it could be entered as start and end time, then output that to a separate column to give a total driving time (this I can do, just), but take the data from that cell and work out an average speed from the distance driven cell (which is derived from the start/end cells for the odometer)?
I tried this but I think it failed to work because it was trying to take data from a cell that had a formula in it and was a destination cell for a sum. I'd quite like to work out a system to show average MPG (UK Gallons) and cost per mile but I can't find anything online telling me how to do it, nor can I work out how to do it myself.
Rara From Germany, joined Jan 2007, 1829 posts, RR: 3 Reply 2, posted (6 months 2 weeks 20 hours ago) and read 516 times:
Sure, no problem.
In your template, I take column K and label it Start Time, column L ist End Time, column M is Duration and column N ist Avg Speed. I define the data cells in column K and L as "time", and column M and N as regular numbers.
In K and L you just put in the times in a regular hour format, like 12:15.
Column M is "=24*(L7-K7)" - that gives you the time in hours.
Column N is "=J7/M7", giving you the average speed in mph or kph, whatever you use.
Average MPG and cost is more difficult, but there should be templates for that available online.
Samson was a biblical tough guy, but his dad Samsonite was even more of a hard case.
petertenthije From Netherlands, joined Jul 2001, 3231 posts, RR: 13 Reply 14, posted (6 months 2 weeks 19 hours ago) and read 516 times:
Quoting ajd1992 (Reply 13): I know you shouldn't double post but I got it to work,
This will only work when you arrive on the same day you depart. To counter that problem either add the dates in the cells with the times, or include the function jamincan mentioned in reply 11.
For cost per mile include a column with the price per liter (or gallon). Multiply the costs per liter by the amount of liter used, and divide by the distance travelled.
Assume price per liter is listed in column Q and the costs per mile in column R. The formula in column R becomes =Q3*N3/J3.
Sponsor Message: 
From UK - England, joined Jul 2006, 2645 posts, 
From Netherlands, joined Jul 2001, 3231 posts, 
From Germany, joined Jan 2007, 1829 posts, 
From United States of America, joined Feb 2009, 1054 posts, 
From Canada, joined Aug 2006, 764 posts, 
Printer friendly format 
