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:

Odometer	Gallons	Date	Mileage
27568	0	9/28/13	0
27759	6.299	10/5/13	191
27934	6.393	10/14/13	175
28196	10.158	10/26/13	262
28477	9.482	11/9/13	281
28784	10	11/14/13	307
29100	9.363	11/22/13	316
29349	9.277	12/3/13	249
29636	9.588	12/12/13	287
29860	8.757	12/28/13	224
29944	2.622	12/31/13	84
30092	5.659	1/8/14	148
30281	7.008	1/19/14	189

<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

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]

Average MPG	28.67683	(MAX(A:A)-MIN(A:A))/SUM(B:B)
Average Mileage/day	24.00885	(MAX(A:A)-MIN(A:A))/(MAX(C:C)-MIN(C:C))
Average Gallon/day	0.837221	SUM(B:B)/(MAX(C:C)-MIN(C:C))
Total(Mileage) since purchased	2713	MAX(A:A)-MIN(A:A)
Total Calendar days since purchased	113	MAX(C:C)-MIN(C:C)
Total Gallons	94.606	SUM(B:B)

<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>
</tbody>

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:

Odometer	Gallons	Date	Mileage
27568		9/28/13
27759	6.299	10/5/13	191
27934	6.393	10/14/13	175
28196	10.158	10/26/13	262
28477	9.482	11/9/13	281
28784	10	11/14/13	307
29100	9.363	11/22/13	316
29349	9.277	12/3/13	249
29636	9.588	12/12/13	287
29860	8.757	12/28/13	224
29944	2.622	12/31/13	84
30092	5.659	1/8/14	148
30281	7.008	1/19/14	189

<tbody>
</tbody>

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]

Average MPG	28.67683	(MAX(A:A)-MIN(A:A))/SUM(B:B)
Average Mileage/day	24.00885	(MAX(A:A)-MIN(A:A))/(MAX(C:C)-MIN(C:C))
Average Gallon/day	0.837221	SUM(B:B)/(MAX(C:C)-MIN(C:C))
Total(Mileage) since purchased	2713	MAX(A:A)-MIN(A:A)
Total Calendar days since purchased	113	MAX(C:C)-MIN(C:C)
Total Gallons	94.606	SUM(B:B)

<tbody>
</tbody>

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:

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

<tbody>
</tbody>

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.