How to determine average monthly milage/mpg?

[charlesstricklin]

I'm trying to set a personal budget, but I'm having a hard time wrapping my head around how to determine the average monthly mileage and miles per gallon. I suppose it's mainly because the days in between fill-ups vary so much. Maybe I'm just being dense, but nevertheless, I'm struggling.

Given the following data:

[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 65"]Odometer[/TD]
[TD="class: xl64, width: 65"]Gallons[/TD]
[TD="class: xl64, width: 65"]Date[/TD]
[TD="class: xl64, width: 65"]Mileage[/TD]
[/TR]
[TR]
[TD="class: xl65"]27568[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl66"]9/28/13[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]27759[/TD]
[TD="class: xl65"]6.299[/TD]
[TD="class: xl66"]10/5/13[/TD]
[TD="class: xl65"]191[/TD]
[/TR]
[TR]
[TD="class: xl65"]27934[/TD]
[TD="class: xl65"]6.393[/TD]
[TD="class: xl66"]10/14/13[/TD]
[TD="class: xl65"]175[/TD]
[/TR]
[TR]
[TD="class: xl65"]28196[/TD]
[TD="class: xl65"]10.158[/TD]
[TD="class: xl66"]10/26/13[/TD]
[TD="class: xl65"]262[/TD]
[/TR]
[TR]
[TD="class: xl65"]28477[/TD]
[TD="class: xl65"]9.482[/TD]
[TD="class: xl66"]11/9/13[/TD]
[TD="class: xl65"]281[/TD]
[/TR]
[TR]
[TD="class: xl65"]28784[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]11/14/13[/TD]
[TD="class: xl65"]307[/TD]
[/TR]
[TR]
[TD="class: xl65"]29100[/TD]
[TD="class: xl65"]9.363[/TD]
[TD="class: xl66"]11/22/13[/TD]
[TD="class: xl65"]316[/TD]
[/TR]
[TR]
[TD="class: xl65"]29349[/TD]
[TD="class: xl65"]9.277[/TD]
[TD="class: xl66"]12/3/13[/TD]
[TD="class: xl65"]249[/TD]
[/TR]
[TR]
[TD="class: xl65"]29636[/TD]
[TD="class: xl65"]9.588[/TD]
[TD="class: xl66"]12/12/13[/TD]
[TD="class: xl65"]287[/TD]
[/TR]
[TR]
[TD="class: xl65"]29860[/TD]
[TD="class: xl65"]8.757[/TD]
[TD="class: xl66"]12/28/13[/TD]
[TD="class: xl65"]224[/TD]
[/TR]
[TR]
[TD="class: xl65"]29944[/TD]
[TD="class: xl65"]2.622[/TD]
[TD="class: xl66"]12/31/13[/TD]
[TD="class: xl65"]84[/TD]
[/TR]
[TR]
[TD="class: xl65"]30092[/TD]
[TD="class: xl65"]5.659[/TD]
[TD="class: xl66"]1/8/14[/TD]
[TD="class: xl65"]148[/TD]
[/TR]
[TR]
[TD="class: xl65"]30281[/TD]
[TD="class: xl65"]7.008[/TD]
[TD="class: xl66"]1/19/14[/TD]
[TD="class: xl65"]189[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

How would I go about figuring a) what my average MPG is, and b) what my average mileage is? Any help is appreciated.

 

[mumps]

Hi Charles. Put this formula in cell E3: =(A3-A2)/B3 and this formula in cell F3: =D3/(C3-C2). Then copy the formulae down to the last row with data. Column E will give you the miles/gallon and column F will give you the average miles travelled during that time period. I hope I interpreted what you wanted correctly.

 

[caabyyc]

[TABLE="width: 460"]
<colgroup><col style="width: 197pt; mso-width-source: userset; mso-width-alt: 9358;" width="263"> <col style="width: 48pt;" width="64"> <col style="width: 215pt; mso-width-source: userset; mso-width-alt: 10183;" width="286"> <tbody>[TR]
[TD="class: xl110, width: 263, bgcolor: transparent"]Average MPG[/TD]
[TD="class: xl110, width: 64, bgcolor: transparent"]28.67683[/TD]
[TD="class: xl110, width: 286, bgcolor: transparent"](MAX(A:A)-MIN(A:A))/SUM(B:B)[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Average Mileage/day[/TD]
[TD="class: xl110, bgcolor: transparent"]24.00885[/TD]
[TD="class: xl110, bgcolor: transparent"](MAX(A:A)-MIN(A:A))/(MAX(C:C)-MIN(C:C))[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Average Gallon/day[/TD]
[TD="class: xl110, bgcolor: transparent"]0.837221[/TD]
[TD="class: xl110, bgcolor: transparent"]SUM(B:B)/(MAX(C:C)-MIN(C:C))[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Total(Mileage) since purchased[/TD]
[TD="class: xl110, bgcolor: transparent"]2713[/TD]
[TD="class: xl110, bgcolor: transparent"]MAX(A:A)-MIN(A:A)[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Total Calendar days since purchased[/TD]
[TD="class: xl110, bgcolor: transparent"]113[/TD]
[TD="class: xl110, bgcolor: transparent"]MAX(C:C)-MIN(C:C)[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Total Gallons[/TD]
[TD="class: xl110, bgcolor: transparent"]94.606[/TD]
[TD="class: xl110, bgcolor: transparent"]SUM(B:B)[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to set a personal budget, but I'm having a hard time wrapping my head around how to determine the average monthly mileage and miles per gallon. I suppose it's mainly because the days in between fill-ups vary so much. Maybe I'm just being dense, but nevertheless, I'm struggling.

Given the following data:

[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Odometer
[/TD]
[TD="class: xl64, width: 65"]Gallons
[/TD]
[TD="class: xl64, width: 65"]Date
[/TD]
[TD="class: xl64, width: 65"]Mileage
[/TD]
[/TR]
[TR]
[TD="class: xl65"]27568
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]9/28/13
[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]27759
[/TD]
[TD="class: xl65"]6.299
[/TD]
[TD="class: xl66"]10/5/13
[/TD]
[TD="class: xl65"]191
[/TD]
[/TR]
[TR]
[TD="class: xl65"]27934
[/TD]
[TD="class: xl65"]6.393
[/TD]
[TD="class: xl66"]10/14/13
[/TD]
[TD="class: xl65"]175
[/TD]
[/TR]
[TR]
[TD="class: xl65"]28196
[/TD]
[TD="class: xl65"]10.158
[/TD]
[TD="class: xl66"]10/26/13
[/TD]
[TD="class: xl65"]262
[/TD]
[/TR]
[TR]
[TD="class: xl65"]28477
[/TD]
[TD="class: xl65"]9.482
[/TD]
[TD="class: xl66"]11/9/13
[/TD]
[TD="class: xl65"]281
[/TD]
[/TR]
[TR]
[TD="class: xl65"]28784
[/TD]
[TD="class: xl65"]10
[/TD]
[TD="class: xl66"]11/14/13
[/TD]
[TD="class: xl65"]307
[/TD]
[/TR]
[TR]
[TD="class: xl65"]29100
[/TD]
[TD="class: xl65"]9.363
[/TD]
[TD="class: xl66"]11/22/13
[/TD]
[TD="class: xl65"]316
[/TD]
[/TR]
[TR]
[TD="class: xl65"]29349
[/TD]
[TD="class: xl65"]9.277
[/TD]
[TD="class: xl66"]12/3/13
[/TD]
[TD="class: xl65"]249
[/TD]
[/TR]
[TR]
[TD="class: xl65"]29636
[/TD]
[TD="class: xl65"]9.588
[/TD]
[TD="class: xl66"]12/12/13
[/TD]
[TD="class: xl65"]287
[/TD]
[/TR]
[TR]
[TD="class: xl65"]29860
[/TD]
[TD="class: xl65"]8.757
[/TD]
[TD="class: xl66"]12/28/13
[/TD]
[TD="class: xl65"]224
[/TD]
[/TR]
[TR]
[TD="class: xl65"]29944
[/TD]
[TD="class: xl65"]2.622
[/TD]
[TD="class: xl66"]12/31/13
[/TD]
[TD="class: xl65"]84
[/TD]
[/TR]
[TR]
[TD="class: xl65"]30092
[/TD]
[TD="class: xl65"]5.659
[/TD]
[TD="class: xl66"]1/8/14
[/TD]
[TD="class: xl65"]148
[/TD]
[/TR]
[TR]
[TD="class: xl65"]30281
[/TD]
[TD="class: xl65"]7.008
[/TD]
[TD="class: xl66"]1/19/14
[/TD]
[TD="class: xl65"]189
[/TD]
[/TR]
</tbody>[/TABLE]

How would I go about figuring a) what my average MPG is, and b) what my average mileage is? Any help is appreciated.

 

[charlesstricklin]

I rearranged the columns, added price/gallon and miles/gallon, and tried to revisualize everything, and here's what I came up with.

In October I averaged 27.83 mpg and traveled 628 miles.
In November I averaged 31.36 mpg and traveled 904 miles.
In December I averaged 28.6 mpg and traveled 844 miles.

I think what I'm having trouble with is the varying dates that I fill up. If I filled up the 1st of every month, I could accurately get the mileage and MPG for each month, but because I may wait a week or a few days into each month, those miles fall into the previous month.

Actually, that might not be a bad idea: Just make it a point to fill up the tank as soon as possible the first of every month.

 

[charlesstricklin]

[TABLE="width: 460"]
<tbody>[TR]
[TD="class: xl110, width: 263, bgcolor: transparent"]Average MPG[/TD]
[TD="class: xl110, width: 64, bgcolor: transparent"]28.67683[/TD]
[TD="class: xl110, width: 286, bgcolor: transparent"](MAX(A:A)-MIN(A:A))/SUM(B:B)[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Average Mileage/day[/TD]
[TD="class: xl110, bgcolor: transparent"]24.00885[/TD]
[TD="class: xl110, bgcolor: transparent"](MAX(A:A)-MIN(A:A))/(MAX(C:C)-MIN(C:C))[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Average Gallon/day[/TD]
[TD="class: xl110, bgcolor: transparent"]0.837221[/TD]
[TD="class: xl110, bgcolor: transparent"]SUM(B:B)/(MAX(C:C)-MIN(C:C))[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Total(Mileage) since purchased[/TD]
[TD="class: xl110, bgcolor: transparent"]2713[/TD]
[TD="class: xl110, bgcolor: transparent"]MAX(A:A)-MIN(A:A)[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Total Calendar days since purchased[/TD]
[TD="class: xl110, bgcolor: transparent"]113[/TD]
[TD="class: xl110, bgcolor: transparent"]MAX(C:C)-MIN(C:C)[/TD]
[/TR]
[TR]
[TD="class: xl111, width: 263, bgcolor: white"]Total Gallons[/TD]
[TD="class: xl110, bgcolor: transparent"]94.606[/TD]
[TD="class: xl110, bgcolor: transparent"]SUM(B:B)[/TD]
[/TR]
</tbody>[/TABLE]

I apologize... I'd missed this response when it was first posted. It's great! Thank you so much!

 

[charlesstricklin]

Is it possible to find for the average gallons/day without having the Milage column?

Here's why I ask: I use an app for the iPhone called Car Care, and it exports data for gas entries like so:

[TABLE="width: 811"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]H[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Odometer[/TD]
[TD]Entry Type[/TD]
[TD]Entry Subtype[/TD]
[TD]Quantity[/TD]
[TD]Quantity Units[/TD]
[TD]Is Full Tank[/TD]
[TD]Reset[/TD]
[TD]Currency[/TD]
[TD]Price[/TD]
[TD]Notes[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]35141[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.851[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]2.799[/TD]
[TD]-[/TD]
[TD]10/22/14 13:09[/TD]
[TD]30.218164, -97.689125[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]34916[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.726[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]2.929[/TD]
[TD]-[/TD]
[TD]10/10/14 20:07[/TD]
[TD]30.386377, -97.694809[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]34656[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.547[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.099[/TD]
[TD]-[/TD]
[TD]10/5/14 10:32[/TD]
[TD]32.360996, -97.419258[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]34356[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]5.314[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.069[/TD]
[TD]-[/TD]
[TD]10/1/14 14:01[/TD]
[TD]30.386057, -97.694641[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]34198[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.378[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.069[/TD]
[TD]-[/TD]
[TD]9/25/14 23:03[/TD]
[TD]30.386150, -97.695374[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]33931[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.718[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.199[/TD]
[TD]-[/TD]
[TD]9/14/14 13:55[/TD]
[TD]30.443686, -97.741646[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]33650[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]7.888[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.219[/TD]
[TD]-[/TD]
[TD]9/1/14 8:24[/TD]
[TD]30.408522, -97.698151[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]33440[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.438[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.169[/TD]
[TD]-[/TD]
[TD]8/16/14 19:18[/TD]
[TD]30.386383, -97.695534[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]33187[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]4.878[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.299[/TD]
[TD]-[/TD]
[TD]8/1/14 19:29[/TD]
[TD]30.424030, -97.671234[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]33057[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.095[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.299[/TD]
[TD]-[/TD]
[TD]7/23/14 3:48[/TD]
[TD]30.386169, -97.694801[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]32828[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.576[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.429[/TD]
[TD]-[/TD]
[TD]7/1/14 0:50[/TD]
[TD]30.408087, -97.678246[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]32605[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.906[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.369[/TD]
[TD]-[/TD]
[TD]6/16/14 11:38[/TD]
[TD]30.218279, -97.689339[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]32379[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]6.565[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.433[/TD]
[TD]-[/TD]
[TD]6/2/14 12:34[/TD]
[TD]30.408186, -97.696396[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]32201[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.289[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.399[/TD]
[TD]-[/TD]
[TD]5/17/14 19:31[/TD]
[TD]30.408266, -97.696533[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]31941[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.021[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.399[/TD]
[TD]-[/TD]
[TD]5/1/14 10:05[/TD]
[TD]30.408264, -97.696190[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]31679[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.904[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.369[/TD]
[TD]-[/TD]
[TD]4/14/14 15:13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]31427[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]0.638[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.299[/TD]
[TD]-[/TD]
[TD]4/1/14 19:17[/TD]
[TD]30.409906, -97.716026[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]31412[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.52[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.329[/TD]
[TD]-[/TD]
[TD]3/31/14 15:35[/TD]
[TD]30.414553, -97.746513[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]31148[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.397[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.099[/TD]
[TD]-[/TD]
[TD]3/14/14 21:15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]30889[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]2.884[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.199[/TD]
[TD]-[/TD]
[TD]3/1/14 10:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]30800[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.59[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.129[/TD]
[TD]-[/TD]
[TD]2/22/14 19:18[/TD]
[TD]30.386127, -97.695267[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]30522[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.872[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.039[/TD]
[TD]-[/TD]
[TD]2/10/14 12:04[/TD]
[TD]30.407784, -97.678207[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]30281[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]7.008[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.049[/TD]
[TD]-[/TD]
[TD]1/19/14 13:59[/TD]
[TD]30.443974, -97.743088[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]30092[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]5.659[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.059[/TD]
[TD]-[/TD]
[TD]1/8/14 8:31[/TD]
[TD]30.218386, -97.689247[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]29944[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]2.622[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.079[/TD]
[TD]-[/TD]
[TD]12/31/13 19:34[/TD]
[TD]30.440369, -97.669991[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]29860[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]8.757[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.099[/TD]
[TD]-[/TD]
[TD]12/28/13 19:18[/TD]
[TD]30.386106, -97.695290[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]29636[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.588[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.129[/TD]
[TD]-[/TD]
[TD]12/12/13 21:27[/TD]
[TD]30.458389, -97.822784[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]29349[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.277[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.099[/TD]
[TD]-[/TD]
[TD]12/3/13 21:38[/TD]
[TD]30.386435, -97.695351[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]29100[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.363[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.099[/TD]
[TD]-[/TD]
[TD]11/22/13 21:02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]28784[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]10[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]2.959[/TD]
[TD]-[/TD]
[TD]11/14/13 16:30[/TD]
[TD]29.731188, -95.382492[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]28477[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]9.482[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]2.879[/TD]
[TD]-[/TD]
[TD]11/9/13 19:36[/TD]
[TD]30.406054, -97.673576[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]28196[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]10.158[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]3.099[/TD]
[TD]-[/TD]
[TD]10/26/13 19:43[/TD]
[TD]30.330137, -97.715271[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]27934[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]6.393[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]2.999[/TD]
[TD]-[/TD]
[TD]10/14/13 2:00[/TD]
[TD]30.486288, -97.650986[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]27759[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]6.299[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]2.989[/TD]
[TD]-[/TD]
[TD]10/5/13 21:19[/TD]
[TD]30.518166, -97.651291[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]27568[/TD]
[TD]Gas[/TD]
[TD]Regular[/TD]
[TD]0[/TD]
[TD]gal[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]9/28/13 9:43[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd like to do the calculations with as little additional coding as possible. If the mileage column needs to be added, so be it, but it there's a way to do the calculation with only the data as provided, then that'd be preferable.

Thanks in advance for taking a look at this!

 

[charlesstricklin]

You know what? Disregard that last question. I think I like just having the daily average, and I can extrapolate things from there.

Consider this thread closed. And again, thanks for the help, you guys are awesome!

 

[grd]

Hi Charles

I know you said thread closed but…..

You need to ensure that your date/time column is configured as date/time.

Call Col M “Mileage” and In cell M3: =a2-a3 and copy down

Call Col N “Monthly Miles” and in cell N2: =SUMPRODUCT((MONTH($K$2:$K$36)=MONTH(K2))*((YEAR($K$2:$K$36)=YEAR(K2))*($M$2:$M$36))) and copy down

Call Col O “Monthly Cost” and in cell O2: =SUMPRODUCT((MONTH($K$2:$K$36)=MONTH(K2))*((YEAR($K$2:$K$36)=YEAR(K2))*($I$2:$I$36))) and copy down

I’ll leave you to play around whether you want to manually delete duplicated monthly rows or not.

Call Col P “Average gallons/day” and in P2: =D2/(K2-K3) and copy down.

Any other calcs should be easy to set up from this.