In this Excel tutorial, you will learn in detail about the mileage calculator in Excel, which includes calculating mileage for vehicles and between cities.
We used Microsoft 365 to prepare this article. You can apply the mentioned functions in versions from Excel 2010 onwards.
An Excel mileage calculator is designed to track and calculate the distance traveled, fuel consumption, and vehicle-related expenses. Mileage is the miles a vehicle can travel on one gallon of fuel. It is used to calculate the fuel efficiency of a vehicle. Mileage is expressed as miles per gallon (MPG) and depends on various factors like engine type, driving habits, maintenance, etc.
In the following, you will find an overview of the mileage calculator in Excel.
How to Make Mileage Calculator in Excel
In the following, I have shared several steps to make a mileage calculator in Excel. Suppose we have a dataset of some odometer reading starting and ending points for traveling from one city to another. In addition, we have the fuel consumption for the traveled distances.
- Now, we will compute the total distance from the odometer reading.
- Choose a cell (G5) and apply the subtraction formula below.
- In the final touch, we will drag the Fill handle to fill all the cells.
=E5-D5
- Finally, we will calculate the mileage by dividing the total distance traveled by the fuel consumption.
- Simply, select a cell (H5), apply the formula, and pull the Fill handle down.
=G5/F5
1. Calculating Best Mileage
You can calculate the best mileage using the MAX function in Excel. Calculating the best mileage leads to determining the optimal balance between distance covered and fuel consumption.
- Simply, choose a cell (H14) and apply the below formula.
=MAX(H5:H12)
2. Calculating Worst Mileage
By applying the MIN function, you can determine the worst mileage too. In order to identify the worst mileage, you need to find the lowest fuel efficiency, where a vehicle covers the least distance per unit of fuel consumption.
- For that, select a cell (H14) and put the following formula.
=MIN(H5:H12)
3. Determining Average Mileage
In order to determine the average mileage, we will use the SUM function in Excel. Average mileage refers to the distance a vehicle can travel on a certain amount of fuel.
- Simply, choose a cell (H14) and apply the below formula.
=SUM(G5:G12)/SUM(F5:F12)
How to Calculate Mileage Between Cities in Excel
You can also calculate mileage between cities by using distances in Excel. Here we have a dataset of some cities’ distances row and column-wise.
- First, we will start with creating a data validation to choose the start and end points.
- Next, we will create a drop-down list in cells (C14, C15) by using the Data Validation option from the Data ribbon.
- From the Data Validation window choose List and city names.
- As the city names are the same, we can create the drop-down list from any source.
- In order to determine the total distance we will use the below formula in a cell (C16).
- In this section, we combined the INDEX and MATCH functions to compute the total distance.
- Here the MATCH function provides the cell’s position and the INDEX function returns the cell’s value.
=IFERROR(INDEX(C5:J12,MATCH(C14,B5:B12,0),MATCH(C15,C4:J4,0)),"")
- Now, from the drop-down list choose your desired starting and ending point to get the total distance between cities.
- Let’s assume we have a value of the fuel used for traveling.
- Finally, choose a cell (C19) and write the below formula.
=C16/C18- Thus, we can calculate the mileage by dividing the total distance by fuel consumption.
Now, you can select any start and end city from the data validation in cells C14 and C15. Right after selecting the cities, you will get the mileage calculated automatically through the formulas you applied previously in cells C16 and C19.
Which Things to Remember While Creating Excel Mileage Calculator
- If your mileage calculator relies on external data sources for distance calculations, then implement automatic updates or refresh functionality.
Frequently Asked Questions
1. What information do I need to calculate mileage in Excel?
Answer: You typically need the starting, and ending points, and fuel consumption rate, to calculate mileage in Excel,
2. How can I convert miles to kilometers in an Excel mileage calculator?
Answer: In order to convert miles to kilometers in an Excel mileage calculator, you can use the conversion factor of 1 mile = 1.60 kilometers.
3. Is it possible to calculate the cost of fuel or expenses using a mileage calculator in Excel?
Answer: By inserting additional fields for fuel consumption rate, and fuel prices, you can calculate the total cost based on the mileage.
Download Practice Workbook
Conclusion
In conclusion, creating an Excel mileage calculator can help you track and determine total travelled distances effectively. Starting point, ending point, and fuel consumption are your need to calculate mileage in Excel. You can also calculate mileage between cities too. Please inform us in the comment section about your experience. Stay tuned and keep learning.
Excel Mileage Calculator: Knowledge Hub
- Daily vehicle mileage and fuel report excel
- Zip to zip mileage calculator excel
- Gas mileage calculator excel
<< Go Back to Excel Templates
