If you add up my individual tanks, my lifetime consumption should be 50.1 gallons. My spreadsheet says 50.169, but I was only working with the one decimal digit tanks from the database. The database itself might have higher internal precision. But in any case, 50.1 is close enough.

On the other hand, the Gal Gas field in the master car entry says 49.8 gallons. That's like .7% off. According to my spreadsheet, I'm supposed to be 58.4 lifetime MPG but the database says 58.8. Interestingly enough, that's also .7% off...

Andy

Andy,

Based on the information from your tank database, all of the information is correct. I even checked your lifetime mileage by hand.

Your distance figures are stored to the nearest whole number. Your mileage figures are stored to the nearest tenth. For each individual tank, gallons are calculated with these figures and then rounded to the nearest tenth.

To calculate mileage, each value is weighted based on distance. The final figure is indeed 58.8, rounded. You say it "should" be 58.4, but I assume this is based on numbers more exact than the ones in our database. If this is so, the more tanks you put in, the smaller the variance should be.

As far as gallons, the database simply uses the total distance and average mileage to calculate this. I came up with 50.1. Again the only conflict I see is that perhaps your own spreadsheet has numbers a bit more exact.

Hmm, I don't know. In the summary for the whole car, the "Gal Gas" field says 49.8. That is not the same as the 50.1 you get by adding the tanks up. If you divide 2928 miles by 49.8 gallons, you get 58.8mpg, which is what the summary shows. Now if you divide 2928 by 50.1, you have 58.443mpg, which more or less agrees with my spreadsheet. So it is not that I have higher resolution data than the database, but that the database itself is not consistent. And looks like the problem is in the part that adds up the individual tank consumptions.

Andy

By the way, I just did some Excel manipulation of a copy of the greenhybrid tank spreadsheet I downloaded yesterday afternoon. First, I took the "distance" column and divide it by the "mileage" column and rounded it off to one decimal place. This gives me the gas consumption per tank. Then I use Excel subtotal function to give me a total gas consumption per car. Lets look at some real high mile entries in the database:

EMAXX: total gas from db: 307.7, total gas per calculation: 315, mpg difference: 60.1 vs 58.7.
City: total gas from db: 130.7, total gas per calculation: 132, mpg difference: 58.43 vs 90.07.
Little Red Beauty: total gas from db: 318.4, total gas per calculation: 323.6, mpg difference: 91.5 vs 90.07.
Sparky: total gas from db: 221.1, total gas per calculation: 221.4, mpg difference: 57.3 vs 57.26.
Keeps on rollin': total gas from db: 288.8, total gas per calculation: 290, mpg difference: 54 vs 53.82.

So the difference seem to be uneven. Interesting eh?

By the way, I did double check all the individual tank numbers above between the db and my working spreadsheet. They are exactly the same. So difference is completely in summing the tanks to a car total.

Comments?

Andy

Andy,
Please stop trying to undermine the database. It is a computer. It doesn't have a mind of its own.

Gallons are not stored in the database. You cannot calculate gallons and then use gallons to calculate something else. That is completely unscientific and inexact.

An individual tank's gallons will be less accurate than the overall car because there is less data. That's simply all there is to it.

Here is the actual code in the databsae. It's PHP, a serverside language. Enjoy.

Weighting total mileage (loops):

Quote:

---

while ( $weight = mysql_fetch_array($weightresult) ) {

$mileagenumerator = $mileagenumerator + $weight['mileage']*$weight['distance'];

$denominator = $denominator + $weight['distance'];

$mileage = $mileagenumerator/$denominator;
}

---

Calculating total gallons:

Quote:

---

echo number_format(1/$car1['avgmileage']*$car2['sumdistance'],1);

---

Calculating tank gallons:

Quote:

---

echo round(1/$data['mileage']*$data['distance'],1);

---

??? I don't get it. What's that supposed to mean?

Quote:

---

Originally posted by Jason@Sep 8th 2004 @ 9:18 AM
Gallons are not stored in the database. You cannot calculate gallons and then use gallons to calculate something else. That is completely unscientific and inexact.

An individual tank's gallons will be less accurate than the overall car because there is less data. That's simply all there is to it.

---

And why is that? The last time I checked, mpg is total miles travelled divided by total gallon of gas used over those miles. Total gallon of gas used is the sum of all tanks. What's so unscientific and inexact about that?

<!--QuoteBegin-Jason@Sep 8th 2004 @ 9:18 AM
Here is the actual code in the databsae. It's PHP, a serverside language. Enjoy.

Weighting total mileage (loops):

Quote:

---

while ( $weight = mysql_fetch_array($weightresult) ) {

$mileagenumerator = $mileagenumerator + $weight['mileage']*$weight['distance'];

$denominator = $denominator + $weight['distance'];

$mileage = $mileagenumerator/$denominator;
}

---

Calculating total gallons:

Quote:

---

echo number_format(1/$car1['avgmileage']*$car2['sumdistance'],1);

---

Calculating tank gallons:

Quote:

---

echo round(1/$data['mileage']*$data['distance'],1);

---

[/quote]
Well, I'm a EJB type person, but this stuff is close enough to Perl that I'll take a crack at it.

So lets take a concrete example. You have a car that did two tanks. 50mpg for 100 miles on the first tank, and 20mpg for 50 miles on the second tank. From first principle, total distance = 100+50 = 150 miles. Gas use for first tank is 100/50 = 2 gal. Second tank is 50/20 = 2.5 gal. Total gas use is 4.5 gal and overall mpg = 150/4.5 = 33.3mpg.

Now according to your PHP script above, weighted average mpg for the two tank run is (50*100+20*50)/(100+50) = 6000/150 = 40mpg. Now if we go back to first principle, this 40mpg number must come from some distance divided by some gas use. Assume we agree that the total distance is 150 miles, so according to your weighting, we must have used up only 3.75 gallons of gas? So where does the other .75 gallons go??

Andy

P.S. The more the individual tank deviated from the overall average, the bigger the difference is between the two methods. That's why you see a lot more on EMAXX and JHX and much less on sparky etc.

Andy,

Individual tank data is absolutely, positively correct. The distance figures are stored directly. The mileage figure is stored directly. Should the member input gallons instead of mileage, distance/gallons = the mileage that is stored.

Since the database stores the distance and the mileage for each tank (not gallons), I must use calculations to determine the overall weighted mileage for each car. There is, of course, the possibility that my equation is incorrect, and perhaps you have discovered this. However, is the weighted averaging done wrong? Where did the error come from?

If you can provide a better equation to determine weighted mileage and gallons based on the distance and mileage of each tank, I will do my best to implement that equation. I cannot for the life of me, however, understand how the discrepancy happened in the first place.

Since mileage = distance/gallons, we can flip things around and have gallons = distance/mileage. Since these last two are stored, individual tank gallons can always be derived precisely. What's left then is to get the total distance then divide it by total gallons, with something like this:

Code:

---

$numerator = 0;
$denominator = 0;
while ( $weight = mysql_fetch_array($weightresult) ) {
  $numerator += $weight['distance'];
  $denominator += $weight['distance']/$weight['mileage'];
}
$mileage = $numerator/$denominator;

---

Sounds plausible?

<!--QuoteBegin-Jason@Sep 8th 2004 @ 11:22 AM
[...]I will do my best to implement that equation. I cannot for the life of me, however, understand how the discrepancy happened in the first place.[/quote]
I have a gut feeling that weighting based on percentage distance is not enough for the below average tanks. Need some time to think it through. Get back to you in a day or so... :)

Andy

Unless I'm mistaken, what you've written is the same thing as what I have right now.

Current: [ SUM-of-all(tankmileage*tankdistance) ] / totaldistance
Yours: totaldistance / [ SUM-of-all(tankmileage/tankdistance) ]

Actually mine is totaldistance / [SUM-of-all(tankdistance/tankmileage)]. Of course, the nice thing about this is that the number of major math operations in both are the same. Therefore, the new formula should have no significant effect on your server performance.

Now if we plug in the numbers from our previous example, the current comes out to (50*100+20*50)/(100+50) = 6000/150 = 40mpg. Mine is (100+50)/(100/50+50/20) = 150/(2+2.5) = 150/4.5 = 33.3mpg. So they are not the same. Remember, to disprove a theory, one counterexample is enough :)

Andy