How to Calculate MPG in Excel (Step-by-Step Guide)

Get FREE Advanced Excel Exercises with Solutions!

This article shows you how to calculate MPG in Excel. The manufacturer will almost always include the MPG when you buy a car. But it varies with the different operating conditions. So calculating it by yourself gives the most accurate measurement of MPG. Here, we will take you through some easy and convenient steps on how to calculate MPG in Excel.


What Is MPG?

Before starting further calculations, first, we need to know about MPG. Do you know what MPG is? If the answer is no, then go through this section carefully.

MPG, or miles per gallon, is the distance measured in miles, a car can drive per gallon of gasoline. MPG is also the most used metric for determining a car’s fuel efficiency: the greater the MPG, the more fuel-efficient the vehicle is.

The EPA (The U.S. Environmental Protection Agency) gives each vehicle three different MPG ratings:

  • Highway MPG
  • City MPG
  • Combined MPG

In this article, we won’t focus on highway or city MPG, rather we will calculate the combined MPG.


How to Calculate MPG in Excel: 5 Steps

Calculating the MPG of cars is very important. Because the higher the MPG, the more cost-effective a vehicle is. In simple terms, a high MPG shows you can go the same distance while using less fuel. As a result, you should have to refuel less frequently than vehicles with lower MPG, assuming you have the same size fuel tank.

Here, we’ve a Milage Log of the last 10 days of May’2022 of an Employee named Haward Mark of ABC Corporation.

How to Calculate MPG in Excel Dataset

Here, column B contains the Date, column C includes Odometer Readings, column D means Distance Travelled (in Miles), and column E accommodates Fuel Consumption (in Gallon). From this, we’ll calculate MPG in Excel.

Before starting to show the steps of calculating MPG in Excel, we should know the mathematical formula to determine MPG. Here it is.

MPG = Total Distance Travelled / Total Fuel Consumed

As an example, if a car travels 50 miles in a day and consumed 2 gallons of fuel. Then the MPG would be = 50 / 2 = 25 miles/gallon.

Now, to calculate this mpg through Excel, follow our steps carefully.


Step 01: Determine Distance

At first, we’ve to calculate the distance traveled by car. For this, we have to subtract yesterday’s odometer reading from today’s odometer reading to get today’s mileage. To calculate 23-May’s total distance, we subtracted cell C11 from C12. The formula is as follows.

=C12-C11

How to Calculate MPG in Excel Determining Distance

  • Secondly, use the Fill Handle tool and drag it down to cell D15 to get the results of other cells of column D.

How to Calculate MPG in Excel Determining Distance

Thus, we got the distance of other days as well.

Read More: How to Calculate Mileage in Excel


Step 02: Calculate Daily MPG

Next, select cell F11. Write down the formula below and press ENTER.

=D11/E11

How to Calculate MPG in Excel Calculating Daily MPG

Use the Fill Handle tool to get copy the formula in the lower cells and get results similarly. We already calculated our Daily MPG from the daily distance and fuel consumption. Now, we’ll determine the Average MPG.


Step 03: Figure out Total Distance and Total Fuel Consumption

Next, select cell D16 and enter the formula below in the Formula Bar. Then, press ENTER.

=SUM(D11:D15)

Here, we’ve used the SUM function to determine the total distance in the D11:D15 range.

Calculating Total Distance and Total Fuel Consumption

Similarly, compute the total fuel consumption of these 5 days using the SUM function.


Step 04: Evaluate Average MPG

Furthermore, divide the total distance by the total fuel consumption to get the Average MPG. Select cell D17 and paste the formula below. Then, press ENTER.

=D16/E16

Evaluating Average MPG


Step 05: Calculate Average MPG Using AVERAGE Function

An alternative way to determine the Average MPG is to average the daily MPG values in cell range F11:F15. To do this, select cell F17, and type down the formula below. Now, press ENTER.

=AVERAGE(F11:F15)

Here, we’ve used the AVERAGE function to calculate the Average MPG from cell F11 to cell F15.

Calculate Average MPG using AVERAGE Function


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

Thank you for reading this article. We hope this was helpful. Also, you can download the Template and use it as MPG calculator. Please let us know in the comment section if you have any queries or suggestions.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo