Gas mileage and remaining fuel: unsure how to set up a formula?

[EdNerd]

The gas guage in my car doesn't always read correctly. So I set up a worksheet that allows me to input the parameters from a fill-up (odo and gallons in), and calculates my mileage. Using this, if I input my current odometer, I can see about how many gallons and miles I should have left in my tank.

All this works well. UNLESS ... how do I calculate in a partial fill?? If I just add in 5 gals to get me home to my preferred station, or last me to payday, how should I factor those numbers into the calculations? It seems like a simple math exercise, but it's giving me a bit of the fits! Any help, please?

Ed

 

[jolivanes]

I don't quite get the reasoning. Is any fill-up not a partial fill-up unless your tank is completely dry?
If your average, I assume that is what you are using, is x amount of miles per gallon and you have n gallons left after driving y amount of miles with an average use of z gallons per mile it should not be a problem calculating the mileage to travel left with the amount of fuel left.
If it sounds to simplistic, upload a mini-sheet with your setup for people to have an idea of what you're looking at.

 

[EdNerd]

I don't quite get the reasoning. Is any fill-up not a partial fill-up unless your tank is completely dry?

Yeah -- I didn't word that quite right. I meant to say: if I don't fill it all the way.

My tank is 20 gallons. If I fill to the max, then it's easy to calculate 20 - fillup = gallons used; odo now - odo last = miles driven; miles /gal = mpg.

If I add only 5 gal, but don't completely fill, I'm not sure how to factor that partial fill into everything else. I can use the odo at partial fill to calculate about how many gallons remain (that's what the whole sheet is for - aside from tracking all of this). For some reason, how to fold in this extra number is eluding me. I'm sure it's simple and I can't see it because I'm anticipating hard.

 

[myall_blues]

To calculate mileage from a partial fill, you have to wait until the next complete fill. No need to record the odometer for the partial fill, but when the next full fill occurs, record the total mileage and add the partial fill volume to the volume of the total fill.

eg
Car is full at 2000 miles
Drive a bit and add 5 gallons
Fill car at 2575 miles, needed 18 gallons
Total volume = 23 gallons
Total mileage = 575 miles
Mileage = 25 mpg

 

[EdNerd]

That would work, Murray. But I was hoping to be able to add it to the previous fill and calculate how many miles left. Maybe like:

Fill car at 2000 miles
Volume = 18 gal
Mileage = 270
MPG = 15
Range with available fuel:
20 gal x 15 mpg = 300 miles

Add fuel at 2150 miles
Assuming 15 mpg, actual fuel remaining should be 10 gal
Add Volume = 5 gal
Vol avail calcs to 15 gal
Range avail calcs to 225 miles

Does this sound correct??
I probably should input partial fills into a separate column, yes?? Should make for better formulas.

 

[felixstraube]

How about this:
You will have to enter the values in the first 3 columns and the others will calculate tank state and estimated range. Actually "last avg milage" and "estimated fuel consumed" doesnt need to be shown, we can include them into the formul in the other columns. I left them so the thought process is clearer?
Try it out and let me know.

Milage.xlsx
	A	B	C	D	E	F	G	H
1	Odo [miles]	Fill [gallons]	Complete Fill	Avg Milage [mpg]	last avg milage	Estimated fuel consumed [gallons]	Tank state [gallons]	Estimated range [miles]
2	1500		TRUE				20
3	2000	20	TRUE	25	25	20	20	500
4	2250	5	FALSE		25	10	10	250
5	2575	18	TRUE	25	25	13	20	500
6	2775	5	FALSE		25	8	8	200
7
8
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
G2	G2	=IFERROR(IF(C2, 20, F2), "")
D3:D10	D3	=IF(C3, (A3-$A$2)/SUM($B$2:$B3),"")
E3:E10	E3	=IF((SUM((A3:C3<>"")*1)=3), INDEX($D$2:D3,MAX((ROW($D$2:D3)-ROW($D$1))*$C$2:C3)),"")
F3:F10	F3	=IF((SUM((A3:C3<>"")*1)=3), (A3-A2)/E3, "")
G3:G10	G3	=IF((SUM((A3:C3<>"")*1)=3), IF(C3, 20, F3),"")
H3:H10	H3	=IF((SUM((A3:C3<>"")*1)=3), G3*E3, "")

 

[EdNerd]

Wow!! Y'all are awesome! Thank you for all the help. I'm going to look at all of this and see what I can get to work (because it has to work on my phone).

 

[felixstraube]

I had a mistake in the Tank State formula. When the fill is not complete we will have to take the previous tank state, add the (partial) fill, and subtract the estimated fuel consumtion.
Now it is corrected:

Milage.xlsx
	A	B	C	D	E	F	G	H
1	Odo [miles]	Fill [gallons]	Complete Fill	Avg Milage [mpg]	last avg milage	Estimated fuel consumed [gallons]	Tank state [gallons]	Estimated range [miles]
2	1500		TRUE				20
3	2000	20	TRUE	25	25	20	20	500
4	2250	5	FALSE		25	10	15	375
5	2575	18	TRUE	25	25	13	20	500
6	2775	5	FALSE		25	8	17	425
7	3200	20	TRUE	25	25	17	20	500
8	3550	10	FALSE		25	14	16	400
9	3950	5	FALSE		25	16	5	125
10	4000	17	TRUE	25	25	2	20	500
11
12
13
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=IFERROR(IF(C2, (A2-$A$2)/SUM($B$2:$B2),""),"")
E2:E20	E2	=IF((SUM((A2:C2<>"")*1)=3), INDEX($D$2:D2,MAX((ROW($D$2:D2)-ROW($D$1))*$C$2:C2)),"")
F2:F20	F2	=IF((SUM((A2:C2<>"")*1)=3), (A2-A1)/E2, "")
G2:G20	G2	=IFERROR(IF(C2, 20, G1-F2+B2), "")
H2:H20	H2	=IF((SUM((A2:C2<>"")*1)=3), G2*E2, "")
A7	A7	=+A6+H6
A8	A8	=+A7+350
A9	A9	=+A8+400