ajd1992 From , joined Dec 1969, posts, RR: Posted (2 years 1 month 1 week 2 days 10 hours ago) and read 1312 times:
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, 2168 posts, RR: 2
Reply 2, posted (2 years 1 month 1 week 2 days 9 hours ago) and read 1312 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.