How to Create a Fuel Cost Calculator Using an Excel Formula (2 Examples)

Steps:

Create a data chart of odometer readings, state-wise fuel cost per gallon, and added fuel across the states.

Creating Dataset for Fuel Cost Calculator

We will need to subtract the current odometer reading from the 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

Giving Formulas find out Miles

AutoFill the rest to find distance traveled in each refuel date interval by subtracting first and last odometer readings. The formula will be:

=C6-C5

Giving Formulas find out Miles

We will get the full mile crossed on different dates.

Inserting Formulas to find out Miles

=F5*E5

Finding Total Cost of Fuel

Finding Total Cost of Fuel

Finding Total Cost of Fuel

=G5/D5

Finding Cost Per Mile

Finding Fuel Cost Per Mile

Determining Cost Per Mile

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

Selecting Options for Creating Combo Chart for Fuel Cost

Select Combo, and set the Chart Type to Line and Scatter for the Series ‘Total Cost’ and Cost per Mile.
Click OK.

Selecting Options for Creating Combo Chart for Fuel Cost

We will get the chart.

Obtained Chart

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

We can see that the axis data range is a bit large, so we want to modify it.
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