How to Calculate Gas Mileage in Excel
Though your car manufacturer probably lists how many miles per gallon (MPG) your car gets, the actual number may vary widely, depending on whether you drive mainly on highways or mainly on city streets, whether you have a lead foot or whether your tires are properly inflated. To calculate your actual gas mileage in Excel, follow the steps below.
Things You'll Need
- Microsoft Excel
- How many gallons your gas tank holds
- Log of your mileage
Calculating Gas Mileage Using Excel
Fill your tank with gas. Make note of your mileage with the full tank. Also make note of how many gallons your tank holds (you can probably find this information in your vehicle's manual).Wait until your tank shows it's three-quarters full, half full or a quarter full. Make note of how many gallons were used and the new mileage.
Open Microsoft Excel, and insert the column headings "Starting mileage" (in cell A1), "Ending mileage" (in cell B1), "Miles traveled" (in cell C1), "Est. gallons used" (in cell D1) and "Miles per gallon" (in cell E1).
Insert your starting mileage in cell A2. Insert your ending mileage in cell B2.
In cell C2, type the following: =B2-A2. Press "Enter." This will subtract starting mileage from ending mileage to give you total miles traveled.
In cell D2, insert the number of gallons used. This will be an estimate. In the example given, it says 8 gallons, which assumes the tank holds 16 gallons and the driver has used half a tank.
In cell E2, type the following: =C2/D2. Press "Enter." This divides miles traveled by gallons used. The result is your gas mileage.
Finding Average Miles Per Gallon
Complete all the steps in the preceding section ("Calculating Gas Mileage Using Excel") before moving to this section. To find the average miles per gallon your car gets, record several more starting and ending mileage data points, and insert them into columns A and B. Also insert the corresponding gallons used in column D.
Select cell C2, and press "CTRL+C" (or click on the Copy icon on the Home tab) to copy the formula in that cell.
Then, select cells C3 through C10 by clicking "C3" and, while holding down the mouse button, moving the cursor down to cell "C10." Press "CTRL+V" (or click on the Paste icon on the Home tab) to paste the formula into those cells.
You'll want to repeat this copy-and-paste procedure for the formula in cell E2. Select "E2," and press "CTRL+C." Select cells "E3" through "E10," and press "CTRL+V."
In cell E11, type the following: =AVERAGE(E2:E10). Press "Enter." This gives you the average miles per gallon.
Tips & Warnings
- In a formula, rather than typing in the cell number (e.g. "D2"), you may simply click on the cell. It will automatically insert the cell number into your formula. (This also works for a range of cells. Rather than typing "E2:E10," you can select all those cells, and the formula will input the cell numbers automatically.)