Excel tutorials

Measurement Quantity of Fuel or Gasoline in the Tank at a Gas Station in Excel

Automatic calculation of the results of measuring quantities fuel based on the value of the measuring lath, in Excel

Tutorials of the automation of measuring quantities of fuel in the tank in Excel, in this part of the tutorial will show variant and the way in which we can quickly get the result of quantities of fuel for the measured height of the upper levels of fuel (gasoline/diesel) using data directly from the table manufacturer.

Thus, the final formula for the desired results in this variant does not use any auxiliary elements in the calculation. In the formulas are inserted all the functions that load and calculate data directly from the worksheet 'TableS7' and 'corrections' from the manufacturer.

How to in Excel Automate the Calculation of Quantities of Fuel in the Tank on Gas Pump

Block diagram arrangement worksheets in Excel and calculation

Figure 1.

Block scheme arrangement worksheets in Excel and calculation Fuel Tank 

Excel workbook contains the following worksheets.

  • Worksheet - Calculation => where we input two basic values (temperature and measured the height of the measuring lath), the formula takes data directly from the table without additional elements.
  • Worksheet - TableS7 => original or base table with the quantities in whole liters to a certain height measurements.
  • Worksheet - Corrections => worksheet that contains the coefficients for correction at a specific temperature.

Worksheet TableS7 - Figure 2.

This worksheet is the basis of all calculations and calculating quantities of fuel in the tank based on measured height measuring lath. This table is provided by the manufacturer that produced certain tank structures.

Text 'S7' in A1 cell is a personal label is No. 7, So do not tie this label 'S7' with some of the math calculation. There I could write 'tankSeven' or the  'barrel' word.

In the first column of Table A2:A27 note numbers 10-250. There are "Tens" for the measured height.

First of all, the range of cells A1:K1 (firstRowTable) are the numbers from 1-9. These are the "Units" for the measured height. You notice the 'C4' cell and value in 1802, the cell is the intersection of the measured height of the measuring lath and in this case it is 21 cm (20+1). Here missing the millimeters.

These milliliters, we wil calculate using the formulas in the worksheet 'Calculation' you will see in the further examination of this tutorial.

The data or values located in the B2:K27 range of cells are the respective quantities of liters for the height above the level of fuel in the fuel tank.

Figure 2.

The source table specified quantity in the tank based on the measured height of gasoline or diesel 

On this worksheet used the following ranges of cells that are named (Define Name).

  • C2 cells on the worksheet 'Calculation' => Calculation!$C$2 => named 'cellC2'
  • Cell range A2:K27 in the worksheet 'TableS7' => TableS7!$A$2:$C$27 => named 'table'
  • Range of cells A1:K1 on the worksheet 'TableS7' => TableS7!$A$1:$C$1 => named 'firstRowTable'

Also, on the worksheet 'TableS7' I used Conditional Formattind (CF) to indicate that colored the cell, from which the formulas in the worksheet 'calculation' in 'C2' cell get the value of the measured height. The formula for CF is applied to a range of cells B2:K27 and the formula is as follows:

- The formula for CF using named cell ranges

=B2=VLOOKUP(VALUE(TEXT(ROUNDDOWN(cellC2;-1);0));table;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(cellC2;0);0);1));firstRowTable;0);0)

- Here I want to mention that you can not in the formula for CF use absolute cell ranges (Calculation!$C$2) because then the formula does not work. With respect to C2 cell is on another worksheet 'Calculation' and CF formulas in the respective worksheet 'TabeS7' you must use the naming 'cellC2' cells.

Thus, the formula above can be created in this way (formula uses a combination of appointed and absolute cell address):

=B2=VLOOKUP(VALUE(TEXT(ROUNDDOWN(cellC2;-1);0));TableS7!$A$2:$K$27;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(cellC2;0);0);1));TableS7!$A$1:$K$1;0);0)

Worksheet Corrections - Figure 3.

On the worksheet 'Corrections' are temperature coefficients and correction with respect to the corresponding temperature for a particular type of fuel.

Column 'A' is the range of A2:A102 cells (temperature) I used to Drop-Down Menu on the worksheet 'Calculation' in column 'B', as seen in 'B2' cell. The name 'temperature' is used in the Data Validation on the worksheet 'calculation' in 'B2:B5' cells. I assume that these values provided by the manufacturer of fuel tanks - or liquids. The temperature range in the column 'a' is from -15 ° C to +35 ° C with an interval of 0.5 ° C.

In column 'B' you note the correction coefficients for a given temperature. I assume that these coefficients are also issued by the manufacturer of the tank. I'm in this tutorial used for gasoline named 'Euro Super 95'

On this worksheet used the following ranges of cells that are named (Define Name).

  • range of cells 'A2:A102' on worksheet 'Corrections' => Correction!$A$2:$A$102 => named 'temperature'
  • range of cells 'A2:B102' on the worksheet 'Corrections' => Correction!$A$2:$B$102 => named 'correctionSuper95'

Coefficients for a certain temperature when measuring the level of fuel

From this worksheet, the Vlookup function/formulas taking correction factor for the temperature entered in cell B2 on the worksheet 'calculation'.

On this worksheet, no formulas. There are only a formula for Conditional Formatting to indicate that. colored the cell in column 'B', which contains an identical value in cell B2 on the worksheet 'calculation' which is in fact the temperature at the time of measurement

- The formula for CF using named cell ranges

=$B2=VLOOKUP(cellB2;correctionSuper95;2;FALSE)

- Here I want to mention that you can not in the formula for CF use absolute cell ranges (Calculation!$B$2) because then the formula does not work. Given that cell B2 is on another worksheet 'Calculation' and CF formulas in the respective worksheet 'Corrections' must use appointment cells cellB2.

- So the formula can be created in this way (formula uses a combination of named cells and absolute address):

=$B2=VLOOKUP(cellB2;Corrections!$A$2:$B$102;2;FALSE)

Worksheet Calculation - Figure 4.

This worksheet 'Calculation' is our active worksheet in which we input the temperature at the time of measurement and the measured value and automatically have the desired results, quantities of fuel without correction and the correction. These basic values are:

  • measured the height of the measuring lath
  • temperature at the time of measurement
  • quantity liters tank in relation to the measured height without correction
  • quantity liters tank in relation to the measured height and the temperature during measurement with the correction

We are entering only data in the 'B' and 'C' column, in this case, 'B2' and 'C2' cells. In the cells of 'D' and 'E' columns, Excel automatically calculates the value of a quantity liters of fuel on the basis of conditions in 'B2' and 'C2' cells.

'B2' cell (this cell was named as 'cellB2') is related to the calculation in cell 'E2' of the same worksheet. The 'C2' cell (this cell was named as 'cellC2') is related to the calculation in 'D2' cell of the same worksheet

Figure 4.

How to automate, measure the auantity of fuel or gasoline Euro Super 95 in the tank at a gas station

  • Column 'A' contains a list of petroleum products that are in containers that we need to measure the amount of volume in liters (Euro Super 95, Super 98 Euro, Euro Diesel, Fuel Oil). In the this tutorial processed only the first petroleum derivative gasoline 'Euro Super 95' which is applied table base quantity of fuel volume based on measured height measuring lath (more on the particular table on the worksheet 'TableS7').
  • In column 'B', you notice a drop down menu containing the list of potential temperature at the time of measurement (this list has a range from -15 ° C to +35 ° C. This temperature is important to calculate quantities of fuel with the corrections. This temperature choose from drop-down menu that we created in the Data Validation as shown below. Notice that we are here for the 'Source' typed named range of cells 'A2:A102' on worksheet 'Corrections' and I named him as the 'temperature'. See description above for 'Corrections' sheet.

Figure 5. (please see Data Validation for help)

Set Data Validation in Excel

  • 'C' column value that is in fact the height of the measuring lath after measuring quantities of fuel in the tank. In the this tutorial cell 'C2' I am named as "cellC2." In the beginning of this tutorial worksheet 'TableS7' you see that we have used Conditional Formatting. For setting the CF, it is this named cell 'C2'.
  • 'D' column you see the result of quantities of fuel which is calculated the Excel with regard to the measured height of the 'C' column and the base table manufacturers in the worksheet 'TableS7'.
  • 'E' column you notice as a result of the quantity of fuel in the tank based on the temperature coefficient of the correction for the respective temperature. 

Excel formulas to automate the measurement of the quantity of fuel in the tank gas station

D2 cells in the worksheet 'Calculation'.

- The formula in 'D' column cell 'D2' using named cell ranges.

This long formula not nice for the eye, but this formula is automatically used directly the desired data from a worksheet 'TableS7' and automatically performs all calculations necessary to obtain the desired result in 'D2' cell. Due to the length formulas I is to parts in multiple rows. If you copy this formula for your calculations then copy the first in Notepad and assemble/rows to be in one line. The formula must be in one line. After that copy into Excel from Notepad. The same applies to a formula that contains the absolute address. This formula as the original database, use the data in the worksheet 'TableS7' no additional or auxiliary elements (columns, rows, worksheets).

=(VLOOKUP(VALUE(TEXT(ROUNDDOWN(ROUNDUP(cell2;0);-1);0));table;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(ROUNDUP(cell2;0);0);0);1));firstRowTable;0);0)-VLOOKUP(VALUE(TEXT(ROUNDDOWN(ROUNDDOWN(cell2;0);-1);0));table;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(ROUNDDOWN(cell2;0);0);0);1));firstRowTable;0);0))/10*RIGHT(TEXT(cell2;"0,0"))+VLOOKUP(VALUE(TEXT(ROUNDDOWN(ROUNDDOWN(cell2;0);-1);0));table;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(ROUNDDOWN(cell2;0);0);0);1));firstRowTable;0);0)

- The formula in column 'D' cell 'D2', which uses absolute cell ranges

=(VLOOKUP(VALUE(TEXT(ROUNDDOWN(ROUNDUP(C2;0);-1);0));TableS7!$A$2:$K$27;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(ROUNDUP(C2;0);0);0);1));TableS7!$A$1:$K$1;0);0)-VLOOKUP(VALUE(TEXT(ROUNDDOWN(ROUNDDOWN(C2;0);-1);0));TableS7!$A$2:$K$27;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(ROUNDDOWN(C2;0);0);0);1));TableS7!$A$1:$K$1;0);0))/10*RIGHT(TEXT(C2;"0,0"))+VLOOKUP(VALUE(TEXT(ROUNDDOWN(ROUNDDOWN(C2;0);-1);0));TableS7!$A$2:$K$27;MATCH(VALUE(RIGHT(TEXT(ROUNDDOWN(ROUNDDOWN(C2;0);0);0);1));TableS7!$A$1:$K$1;0);0)

This formula below, also returns the same result as the previous (This formula uses a combination of other functions)

=HLOOKUP(FLOOR(C2-FLOOR(C2;10);1);TableS7!$A$1:$K$27;MATCH(FLOOR(C2;10);TableS7!$A$1:$A$27;1);FALSE)+(HLOOKUP(IF(FLOOR(C2-FLOOR(C2;10);1)<9;FLOOR(C2-FLOOR(C2;10);1)+1;0);TableS7!$A$1:$K$27;IF(FLOOR(C2-FLOOR(C2;10);1)<9;MATCH(FLOOR(C2;10);TableS7!$A$1:$A$27;1);MATCH(FLOOR(C2;10);TableS7!$A$1:$A$27;1)+1);FALSE)-HLOOKUP(FLOOR(C2-FLOOR(C2;10);1);TableS7!$A$1:$K$27;MATCH(FLOOR(C2;10);TableS7!$A$1:$A$27;1);FALSE))*(C2-FLOOR(C2;10)-FLOOR(C2-FLOOR(C2;10);1))*10/10

E2 cell on the worksheet 'Calculation'.

- The formula in 'E' column 'E2' cell which uses the named cell ranges.

=VLOOKUP(B2;correctionSuper95;2;FALSE)*D2

- The formula in 'E' column 'E2' cell, which uses absolute cell ranges

=VLOOKUP(B2;Corrections!$A$2:$B$102;2;FALSE)

This formula, as the source database uses the data in the worksheet 'Corrections' and seeks information that is associated conditions in B2 worksheet 'Calculation'. In this case it looks like this:

The formula finds the condition of 'B2' ie. temperature value of 5 °C in column 'A' range of cells '$A$2:$B$102' on the worksheet 'Corrections' and finds him in a 'A42' cell on the worksheet 'Corrections' and returns a value from the same row but in column 2 (the second column of the range of the 'B' column), which is the number that is the coefficient 1.0118. The result is 1908.25 liters

This number 1908.25 we have math so we found the coefficient multiply it with the number in cell C2 (1886*1.0118=1908.25 liters).

Finally look at the Name Manager respective Workbook

Figure 6.

Name Manager in Excel

Here is link for DOWNLOAD EXAMPLE FILE