ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

### Help With Excel 2010 Formulae?

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]

petertenthije
Posts: 3331
Joined: Tue Jul 10, 2001 10:00 pm

### RE: Help With Excel 2010 Formulae?

= (mileage) / (traveltime) / 24

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

Rara
Posts: 2296
Joined: Wed Jan 17, 2007 7:41 am

### RE: Help With Excel 2010 Formulae?

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.

ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

### RE: Help With Excel 2010 Formulae?

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]

flood
Posts: 1045
Joined: Sat Feb 14, 2009 7:05 pm

### RE: Help With Excel 2010 Formulae?

In J3, try using:

=I3/(24*D3)

petertenthije
Posts: 3331
Joined: Tue Jul 10, 2001 10:00 pm

### RE: Help With Excel 2010 Formulae?

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!

ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

### RE: Help With Excel 2010 Formulae?

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

petertenthije
Posts: 3331
Joined: Tue Jul 10, 2001 10:00 pm

### RE: Help With Excel 2010 Formulae?

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

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

Rara
Posts: 2296
Joined: Wed Jan 17, 2007 7:41 am

### RE: Help With Excel 2010 Formulae?

Yap... getting there though.

This one works:

Samson was a biblical tough guy, but his dad Samsonite was even more of a hard case.

jamincan
Posts: 572
Joined: Mon Aug 21, 2006 9:28 am

### RE: Help With Excel 2010 Formulae?

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.

ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

### RE: Help With Excel 2010 Formulae?

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.

jamincan
Posts: 572
Joined: Mon Aug 21, 2006 9:28 am

### RE: Help With Excel 2010 Formulae?

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]

ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

### RE: Help With Excel 2010 Formulae?

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?

ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

### RE: Help With Excel 2010 Formulae?

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

Now for cost per mile....

petertenthije
Posts: 3331
Joined: Tue Jul 10, 2001 10:00 pm

### RE: Help With Excel 2010 Formulae?

 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!

jamincan
Posts: 572
Joined: Mon Aug 21, 2006 9:28 am

### Who is online

Users browsing this forum: TheF15Ace and 2 guests

### Popular Searches On Airliners.net

Top Photos of Last:   24 Hours  •  48 Hours  •  7 Days  •  30 Days  •  180 Days  •  365 Days  •  All Time

Military Aircraft Every type from fighters to helicopters from air forces around the globe

Classic Airliners Props and jets from the good old days

Flight Decks Views from inside the cockpit

Aircraft Cabins Passenger cabin shots showing seat arrangements as well as cargo aircraft interior

Cargo Aircraft Pictures of great freighter aircraft

Government Aircraft Aircraft flying government officials

Helicopters Our large helicopter section. Both military and civil versions

Blimps / Airships Everything from the Goodyear blimp to the Zeppelin

Night Photos Beautiful shots taken while the sun is below the horizon

Accidents Accident, incident and crash related photos

Air to Air Photos taken by airborne photographers of airborne aircraft

Special Paint Schemes Aircraft painted in beautiful and original liveries

Airport Overviews Airport overviews from the air or ground

Tails and Winglets Tail and Winglet closeups with beautiful airline logos