Miles per gallon by truck
Open a miles file (trip rows or a summary, with a unit and miles) and a fuel file (fuel-card or fuel log rows, with a unit and gallons). The page adds up each unit's miles, gallons, and fuel amount, divides miles by gallons for each unit and for the fleet, and shows the checks behind every figure. Every rounding is stated.
Both files are read in your browser and stay on this device. Nothing is uploaded.
Both files are read in this browser and are not uploaded. A workbook that keeps miles on a sheet named Miles and fuel on a sheet named Fuel can be opened in both boxes.
Matched from the header words. Check them and change any that are wrong. Rows for the same unit are added together. The file's own total line and lines with no miles are listed as left out, not added.
Only the unit, gallons, amount, and date are used here. The file's own total line and lines with no gallons are listed as left out, not added. With a date column chosen, a row that has gallons and no date is listed as unreadable.
Unit names are compared after trimming, upper-casing, and collapsing spaces: Truck 12 and 12 are two different units. Rows with no unit are added together and shown as (no unit); they stay in the fleet total. This page has no miles-per-gallon band of its own.
Summary
Miles per gallon by unit
These are your own miles and gallons, added up and divided. The page does not say what any figure ought to be.
What gets checked
- Every miles row and every fuel row is in exactly one unit, and the units re-add to all rows in miles, gallons, and dollars, added a second time in a separate pass.
- Every unit with a miles row or a fuel row is in the result exactly once.
- Each unit's miles are its own miles rows added up, the units add up to total miles, and total miles equal the total you typed when you type one.
- Each unit's gallons are its own fuel rows added up, the units add up to total gallons, and total gallons equal the total you typed when you type one.
- Each unit's fuel amount is its own fuel rows added up, and the units add up to the total fuel amount.
- Each unit's miles per gallon is its own miles divided by its own gallons, rounded to 2 decimals, half away from zero. A unit with no gallons has a blank there instead of a figure.
- Fleet miles per gallon is total miles divided by total gallons, rounded to 2 decimals.
- The unit-by-unit checks are made by a separate routine that does no division: every rounded figure is tested by cross-multiplication against that unit's own rows, so gallons moved from one unit to another fail even when every total is unchanged.
- Warnings, not errors: units with miles and no gallons, units with gallons and no miles, units outside the band you set, and rows with no unit. Rows with no unit are added together as (no unit) and stay in the fleet total; they are never dropped.
- Every row with data is accounted for: used, listed as left out (with its row number and the reason), or listed as unreadable. An unreadable row marks the result not verified.
How it works
- Open the two files. Drop the miles file (.csv or .xlsx) in the first box and the fuel file in the second. Both are read in the browser. A workbook that keeps miles on a sheet named Miles and fuel on a sheet named Fuel can go in both boxes.
- Check the column mapping. The unit and miles columns of the miles file, and the unit, gallons, amount, and date columns of the fuel file, are matched from the header words and can be changed. Unit names are compared after trimming, upper-casing, and collapsing spaces.
- Type your own totals or a band (optional). Total miles and total gallons from your own summary are compared with the files. A lowest and a highest miles per gallon, when you type them, list the units outside that band as a warning. The page has no band of its own.
- Review and export. One row per unit with miles, gallons, fuel amount, and miles per gallon, then the fleet total, shown with the verification certificate. Download Excel or CSV, or print.
Worked example
A made-up month for three trucks. The miles file has seven trip rows and the fuel file has seven fuel rows, each with a unit. Both files end with their own total line, which is listed as left out and not added.
| Unit | Miles | Gallons | Fuel amount | Miles per gallon |
|---|---|---|---|---|
| 12 | 6,200.0 | 900.000 | $3,136.50 | 6.89 |
| 14 | 5,125.0 | 800.000 | $2,796.00 | 6.41 |
| 16 | 2,450.0 | 400.000 | $1,410.00 | 6.13 |
| Fleet total | 13,775.0 | 2,100.000 | $7,342.50 | 6.56 |
Unit 12 has trip rows of 2,100.5 + 1,899.5 + 2,200.0 = 6,200.0 miles and fuel rows of 310.250 + 289.750 + 300.000 = 900.000 gallons. 6,200.0 divided by 900.000 is 6.888..., shown as 6.89. Unit 16 is 2,450.0 divided by 400.000 = 6.125, an exact half, which rounds away from zero to 6.13. The fleet figure is total miles divided by total gallons, 13,775.0 divided by 2,100.000 = 6.5595..., shown as 6.56. It is not the average of the three unit figures, which is 6.48.
The example uses made-up names and numbers.
Questions
Which files does it read?
Any .csv or .xlsx with a header row. The miles file has a unit column and a miles column, as trip rows or as a summary. The fuel file has a unit column and a gallons column, and an amount column when there is one. Layouts differ, so the columns are matched from the header words, the choices are shown, and each one can be changed.
How is a truck matched between the two files?
By its unit name. Names are compared after trimming, upper-casing, and collapsing spaces, so a name written in lower case in one file and upper case in the other is one unit. Truck 12 and 12 are two different units to the tool, and each gets its own row. A unit with miles and no gallons, or gallons and no miles, is listed in a warning, which is where a name written two ways shows up.
What happens to a row with no unit?
It is kept. Miles rows and fuel rows that name no unit are added together into one group shown as (no unit), which has its own row and is part of the fleet total. A warning counts them. Nothing is dropped.
How is miles per gallon rounded?
Each unit's miles are divided by that unit's gallons and rounded to 2 decimals, half away from zero, with whole-number math: miles and gallons are held as whole thousandths and dollars as whole cents. Fleet miles per gallon is total miles divided by total gallons, not the average of the unit figures. A unit with no gallons has a blank in that column.
How are the figures checked?
By a separate routine that does no division. Each rounded figure is tested by cross-multiplication against that unit's own miles rows and fuel rows, and the units are added up again and compared with the totals. Gallons moved from one unit to another fail a check even when every total still adds up.
Does it say whether a truck's figure is high or low?
No. The page divides and checks. When you type a lowest and a highest figure, the units outside that band are listed as a warning. The page has no band of its own and makes no judgment about any figure.
What happens to a total line or a row that cannot be read?
A row with nothing in it is skipped. Every other row is used, listed as left out with its row number and the reason (the file's own total line, a line with no miles or no gallons), or listed as unreadable (miles or gallons that are not a number, negative miles, gallons with no date when the file has a date column). An unreadable row marks the result not verified.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.