Sponsor Message:
Non Aviation Forum
My Starred Topics | Profile | New Topic | Forum Index | Help | Search 
Help With Excel 2010 Formulae?  
User currently offlineajd1992 From , joined Dec 1969, posts, RR:
Posted (1 year 5 months 1 week 6 days 5 hours ago) and read 1122 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.

[Edited 2012-11-11 12:26:52]

15 replies: All unread, jump to last
 
User currently offlinepetertenthije From Netherlands, joined Jul 2001, 3309 posts, RR: 12
Reply 1, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

= (mileage) / (traveltime) / 24

In the cell properties format your cell as standard text instead of hour text.



Attamottamotta!
User currently offlineRara From Germany, joined Jan 2007, 2011 posts, RR: 2
Reply 2, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 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.
User currently offlineajd1992 From , joined Dec 1969, posts, RR:
Reply 3, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

I tried that and all it did was take my time (3h) and my distance (25 miles) and tell me I did an average of 4800mph. I'm guessing that isn't correct https://docs.google.com/spreadsheet/...DMy1jUmZnTDhGTTJrQi16Tmd2dUE#gid=0

I created my own instead but this is what it did.

Edit:

Rara, I tried that and it gave me my time with a minus sign in front of it, I gather I've done something wrong?

[Edited 2012-11-11 13:13:30]

Edit: I put the cells the wrong way around in the formula. Ooops!


[Edited 2012-11-11 13:21:41]

User currently offlineflood From United States of America, joined Feb 2009, 1381 posts, RR: 1
Reply 4, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

In J3, try using:

=I3/(24*D3)


User currently offlinepetertenthije From Netherlands, joined Jul 2001, 3309 posts, RR: 12
Reply 5, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1124 times:

You'll need to include the date in columns B and C. Then put the function I included in reply 1 in column J.


Attamottamotta!
User currently offlineajd1992 From , joined Dec 1969, posts, RR:
Reply 6, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1124 times:

What about this now?

https://docs.google.com/spreadsheet/...N-UdFE0MHNYeG44LUdjT0toeEZXT29FdVE

Guessing the average speed is wrong but I've followed Rara's instructions but it doesn't seem to display correctly.


User currently offlinepetertenthije From Netherlands, joined Jul 2001, 3309 posts, RR: 12
Reply 7, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1124 times:

You've divided miles end with travel time instead of miles completed with travel time.

[Edited 2012-11-11 13:29:52]


Attamottamotta!
User currently offlineRara From Germany, joined Jan 2007, 2011 posts, RR: 2
Reply 8, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1124 times:

Yap... getting there though.


This one works:

https://docs.google.com/spreadsheet/ccc?key=0Anb3gXagqiSodFVRNldCUGc3N09sNmFmVmZtQzU2cXc



Samson was a biblical tough guy, but his dad Samsonite was even more of a hard case.
User currently offlinejamincan From Canada, joined Aug 2006, 775 posts, RR: 0
Reply 9, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

Seems to be correct now, though 25 miles over the course of 3 hours is *very* slow. You might want to try driving a bit faster.  

User currently offlineajd1992 From , joined Dec 1969, posts, RR:
Reply 10, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

Ah, so you're right. Brain fart!

I think it's working now - I just need to rearrange the columns to look a bit better and make it look a tad less boring.


User currently offlinejamincan From Canada, joined Aug 2006, 775 posts, RR: 0
Reply 11, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

Also, I would consider adding if(C2<B2, C2+1, C2) in place of the C2 in the formula in D2 as you had before.

[Edited 2012-11-11 13:35:53]

User currently offlineajd1992 From , joined Dec 1969, posts, RR:
Reply 12, posted (1 year 5 months 1 week 6 days 4 hours ago) and read 1122 times:

Quoting jamincan (Reply 11):
Also, I would consider adding if(C2

I tried that but when I added in a time (I used 13:00 to 14:00) it displayed it as 0.58. Any reason why it might do that?


User currently offlineajd1992 From , joined Dec 1969, posts, RR:
Reply 13, posted (1 year 5 months 1 week 6 days 3 hours ago) and read 1122 times:

I know you shouldn't double post but I got it to work, MPG calculations and all!

https://docs.google.com/spreadsheet/...N-UdDM0WEZ6d1JCbzBqY0sxdkcwb2FwR1E

Now for cost per mile.... 


User currently offlinepetertenthije From Netherlands, joined Jul 2001, 3309 posts, RR: 12
Reply 14, posted (1 year 5 months 1 week 6 days 3 hours ago) and read 1122 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.



Attamottamotta!
User currently offlinejamincan From Canada, joined Aug 2006, 775 posts, RR: 0
Reply 15, posted (1 year 5 months 1 week 6 days 3 hours ago) and read 1122 times:

Here's a link to a copy of the spreadsheet with my correction added:

https://docs.google.com/spreadsheet/ccc?key=0AgkqDK_yJGQAdE16My1QOFktUkFodnA1MXJ2dU1OU1E


Top Of Page
Forum Index

This topic is archived and can not be replied to any more.

Printer friendly format

Similar topics:More similar topics...
Excel Boffins: Help With Formulae? posted Thu Apr 15 2010 11:49:26 by ajd1992
A Little Help With A German Idiom posted Fri Aug 31 2012 18:14:27 by Derico
Help With Buy First Car posted Thu Jul 5 2012 00:13:06 by redrooster3
Help With Wireless Issue posted Sun Jul 1 2012 16:28:16 by einsteinboricua
Help With A Lady posted Fri Sep 10 2010 14:14:28 by N776AU
Help With Apple Mail / Imap posted Sun Aug 22 2010 00:37:34 by Elite
Need Help With Marta (Atlanta) posted Fri Jul 16 2010 13:55:15 by Springbok747
Need Help With Directions In Brussels posted Thu Jun 17 2010 07:25:25 by AirTran737
Help With IPhone And BlackBerry IM Clients posted Tue Jun 1 2010 17:51:03 by Coal
Help With Question For A Final! posted Sun May 9 2010 15:18:52 by flanker