How to Create Fuel Cost Calculator Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

In this article, we are going to create a Fuel Cost Calculator using Excel formula. Here, we are going to give you a very easy way to find out the cost of your fuel used to travel various places. You can find out that fuel price is different in different states and you will add unique or same gallons of fuel to your car. That’s why we are going to make an easy Fuel Cost Calculator taking all the variables accountable. Here is an overview of a sample fuel cost calculator for domestic purpose.

Overview for Fuel Cost CalculatorSEO


Why Is Fuel Cost Calculator Necessary?

If you want to run a business, you will need to ship products from one place to another. Now you have to find out the total cost of fuel and how you are going to reduce it to improve your business. That’s why we are giving you an easy way to find out fuel cost for your business.


How to Create Fuel Cost Calculator Using Excel Formula: 2 Practical Examples

Suppose we have to go from Los Angeles to different states for shipping your product. Now we are going to consider that different states have different fuel cost and you have to cross different miles to pass those states. Different dates of shipping are considered to make it more practical because individual shipment can happen among those states where you can pay your employee to deliver goods by bicycle where fuel costs are not needed.

Then we are going to create a Fuel Cost Calculator for domestic Purposes. We will use Toyota car with MPG (Miles per Gallon) of 23.


1. Creating Fuel Cost Calculator for  Business Purpose

In this first example we are going to use cost per gallon of different states to find total cost in different states. We have given easy to follow steps to create your own calculator by your own data. We had our initial reading in odometer 4000.

Steps:

  • First we have to create a data chart of odometer readings, state wise fuel cost per gallon and added fuel to cross the states.

Creating Dataset for Fuel Cost Calculator

  • We will need to subtract current odometer reading to previous odometer reading to find out the total miles crossed to pass Los Angeles. So we have used the following formula.

=C5-$C$12 

Giving Formulas find out Miles

  • Then press Enter and we will get the total miles passed on that date.

Giving Formulas find out Miles

  • Now we will AutoFill the rest to find distance traveled in each refuel date interval by subtracting first and last odometer readings. So the formula will be

=C6-C5

Giving Formulas find out Miles

  • After that, we will get the full mile crossed on different dates.

Inserting Formulas to find out Miles

  • Now to find total cost we are going to multiply Gallons Fuel added to Cost per gallon. For that purpose, write down the formula below.

=F5*E5

Finding Total Cost of Fuel

  • Then we are going to press Enter, we will get the result of the Total cost.

Finding Total Cost of Fuel

  • Finally, after we AutoFill we will get the rest.

Finding Total Cost of Fuel

  • In order to find cost per mile, we have to divide the total cost divided by Miles.

=G5/D5

Finding Cost Per Mile

  • Then, we are going to press Enter.

Finding Fuel Cost Per Mile

  • Finally we are going to AutoFill it to find out Cost per Mile.

Determining Cost Per Mile

  • To create a chart we have to select Refuel Date, Total Cost, Cost Per Mile. Long press Ctrl key and select the ranges B4:B10, G4:G10 and H4:H10.
  • After that, we go to Insert and select Create Custom Combo Chart.

Selecting Options for Creating Combo Chart for Fuel Cost

  • Then, we select Combo, set the Chart Type to Line and Scatter for the Series ‘Total Cost’ and Cost per Mile.
  • Later, click OK.

Selecting Options for Creating Combo Chart for Fuel Cost

  • Then, we will get the chart.

Obtained Chart

  • Now, let’s format the chart a little bit. Removing gridlines makes the chart simple, so we click on the plus icon at the top right side of the chart and uncheck Gridlines.

Formatting the Fuel Cost Calculator Chart

  • Thereafter, we can see that the range of the axis data is a bit large, so we want to modify it. For that reason, double click on the Fuel Cost data in the chart and the Format Axis window will appear. Set the Minimum Bounds according to your convenience. The Maximum Bound will automatically update as we set it to Auto.

Setting up Bounds for Total Cost

  • Similarly, we can set the Bound for the Cost per Mile.

Setting up Bounds for Cost per Mile

  • After giving the title Fuel Cost Graph, we get the chart below. Finally, this graph will show the cost variation on different dates.

Fuel Cost Calculator Visualization by Excel Chart

Thus we can create a fuel cost calculator using Excel formula for business purposes. This will help us to estimate the transportation cost.

Read More: How to Create Electricity Cost Calculator in Excel


2. Creating Fuel Cost Calculator for Domestic Purpose

Here we are going to use how many trips you make to go to work and grocery and their fuel cost of all trips individually and total.

Steps:

  • First, we have to create a data table for fuel cost for domestic purposes. The colored Total Cost in cell B12 represents the gross fuel cost.

Creating Dataset for Fuel Cost for Domestic Purpose

  • Then, we have to find out Cost Per Mile dividing Fuel Cost by MPG.

=D5/C5

Finding Out Cost Per mile

  • Then, we will press ENTER.

finding Out Cost Per mile

  • Now, we will get the individual  Total cost by multiplying Total Miles with Cost Per Mile.

=$E$5*E8

Total cost for Particular Place

  • After pressing Enter, we will the find total cost.

Total cost for Particular Trip

  • After that, we will Autofill the total cost.

Total cost for Particular Place

  • Thereafter, we are going to use the SUM function to find the total fuel cost of that month.

=SUM(F8:F10)

Total cost for full trips

  • Finally, we will get the result of total Fuel Cost in Excel.

Total cost for full trips

Read More: Truck Operating Cost Calculator in Excel


Download Practice Workbook


Conclusion

At the end of the article, we can consider that you will learn some basic tips to create a Fuel Cost Calculator in Excel using formulas. If you have any questions and feedback, please note them in the comment section.


Related Articles


<< Go Back to Cost Calculator | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo