The dataset below showcases a Milage Log of 10 days in May 2022.
Column B contains the Date, column C includes Odometer Readings, column D shows Distance Travelled (in Miles), and column E the Fuel Consumption (in Gallons).
The mathematical formula to calculate the MPG is:
MPG = Total Distance Travelled / Total Fuel Consumed
Step 1 – Determine the Distance
- Subtract the odometer reading of the previous day from the odometer reading of the current day to get the mileage. Here, C11 is subtracted from C12:
=C12-C11
- Drag down the Fill Handle to see the result in the rest of the cells.
Step 2 – Calculate the Daily MPG
- Select F11 and enter the formula below.
- Press ENTER.
=D11/E11
- Drag down the Fill Handle to see the result in the rest of the cells.
Step 3 -Find the Total Distance and Total Fuel Consumption
- Select D16 and enter the formula below in the Formula Bar.
- Press ENTER.
=SUM(D11:D15)
The SUM function is used in D11:D15.
Step 4 – Calculate the Average MPG
- Select D17 and use the formula below.
- Press ENTER.
=D16/E16
Step 5 – Calculate the Average MPG Using the AVERAGE Function
An alternative to determine the Average MPG is to average the daily MPG values in F11:F15.
- Select F17, and enter the formula below.
- Press ENTER.
=AVERAGE(F11:F15)
The AVERAGE function calculates the Average MPG in F11:F15.
Download the Excel workbook.
<< Go Back to Distance | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!