How to Determine Fuel Consumption in Excel

Fuel consumption is the amount of fuel used by a vehicle over a certain distance or time. It is usually measured in liters per 100 kilometers (L/100km) or miles per gallon (mpg). Knowing the fuel consumption of your vehicle can help you plan your trips, budget your expenses, and reduce your environmental impact.

In this article, we will show you how to calculate fuel consumption in Excel using two methods: the basic formula and the data table. We will also provide a scenario with real data and explain the steps and results.

The Basic Formula

The basic formula for fuel consumption is:

Fuel consumption=Fuel used/Distance traveled​

To use this formula in Excel, you need to have two columns of data: one for the fuel used and one for the distance traveled. For example, suppose you have the following data in cells A1:B5:

To calculate the fuel consumption for each row, you can enter the following formula in cell C2 and drag it down to cell C6:

=A2/B2*100

This will give you the fuel consumption in L/100km. To convert it to mpg, you can multiply it by 0.4251. For example, in cell D2, you can enter:

=C2*0.4251

This will give you the fuel consumption in mpg. You can format the cells to show two decimal places. The final result should look like this:

The Data Table

Another way to calculate fuel consumption in Excel is to use a data table. A data table is a tool that allows you to see how changing one or two variables affects the results of a formula. In this case, we can use a data table to see how different values of fuel used and distance traveled affect the fuel consumption.

To use a data table, you need to have a formula that references two cells that contain the input values. For example, suppose you have the following formula in cell F2:

=E2/G2*100

This formula calculates the fuel consumption in L/100km based on the fuel used in cell E2 and the distance traveled in cell G2. You can also have another formula in cell H2 that converts the fuel consumption to mpg:

=F2*0.4251

Now, you can create a data table that shows the fuel consumption for different values of fuel used and distance traveled. To do this, follow these steps:

1. In a blank area of the worksheet, enter the values of fuel used that you want to test in a column. For example, in cells A10:A14, enter 10, 12, 15, 18, and 20.
2. In the same area, enter the values of distance traveled that you want to test in a row. For example, in cells B9:F9, enter 200, 250, 300, 350, and 400.
3. In the top-left cell of the data table, enter a reference to the cell that contains the formula for fuel consumption in L/100km. For example, in cell B10, enter =F2.
4. Select the entire data table, including the row and column headings. For example, select cells A9:F14.
5. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
6. In the Data Table dialog box, under Row input cell, enter the reference to the cell that contains the distance traveled input value. For example, enter G2.
7. Under Column input cell, enter the reference to the cell that contains the fuel used input value. For example, enter E2.
8. Click OK.

Excel will fill the data table with the results of the formula for each combination of fuel used and distance traveled. You can format the cells to show two decimal places. The result should look like this:

To add another data table for the fuel consumption in mpg, you can repeat the same steps, but use the formula in cell H2 instead of F2. For example, in cell G10, enter =H2, and then create a data table with the same row and column input cells. The result should look like this:

Scenario

To illustrate how to use these methods in a real situation, let’s consider the following scenario:

You are planning a road trip from Los Angeles to San Francisco, which is about 600 km. You have two options for your vehicle: a sedan that consumes 8 L/100km or a SUV that consumes 12 L/100km. You want to compare the fuel costs and the environmental impact of each option.

To do this, you can use the basic formula or the data table to calculate the fuel consumption for each option. For example, using the basic formula, you can enter the following data and formulas in cells A1:D3:

The result should look like this:

Using the data table, you can enter the following data and formulas in cells F1:H3:

Then, you can create a data table with the distance traveled as the row input cell and the fuel used as the column input cell. For example, in cells A5:A9, enter 500, 550, 600, 650, and 700. In cell B5, enter =H2. Then, select cells A4:H9 and create a data table with G2 as the row input cell and F2 as the column input cell. The result should look like this:

The data table shows how the fuel consumption changes for different values of distance traveled and fuel used. You can see that the sedan has a lower fuel consumption than the SUV for any given distance.

To compare the fuel costs and the environmental impact of each option, you can use the following information:

• The average price of gasoline in California is $3.5 per gallon.
• The average carbon dioxide (CO2) emission factor for gasoline is 8.89 kg per gallon.

Using these values, you can calculate the fuel cost and the CO2 emission for each option using the following formulas:

• Fuel cost = Fuel used (in gallons) x Price of gasoline (in dollars)
• CO2 emission = Fuel used (in gallons) x CO2 emission factor (in kg)

For example, using the basic formula, you can enter the following data and formulas in cells E1:G3:

The result should look like this:

Using the data table, you can enter the following data and formulas in cells I1:K3:

Then, you can create a data table with the fuel consumption in mpg as the row input cell and the fuel cost as the column input cell. For example, in cells A11:A15, enter 2, 2.5, 3, 3.5, and 4. In cell B11, enter =K2. Then, select cells A10:K15 and create a data table with I2 as the row input cell and J2 as the column input cell. The result should look like this:

To add another data table for the CO2 emission, you can repeat the same steps, but use the formula in cell L2 instead of K2. For example, in cell C11, enter =L2, and then create a data table with the same row and column input cells. The result should look like this:

The data tables show how the fuel cost and the CO2 emission change for different values of fuel consumption and fuel used. You can see that the sedan has a lower fuel cost and a lower CO2 emission than the SUV for any given fuel consumption.

From the calculations, you can conclude that the sedan is a more economical and eco-friendly option than the SUV for your road trip. You can save money and reduce your carbon footprint by choosing the sedan.