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

Tutorials on the

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.

Tutorials on 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.

- 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 cell A1 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 word 'barrel'.

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 cell C4 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 range of cells B2:K27 are the respective quantities of liters for the height above the level of fuel in the tank

Figure 2.

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

- C2 cells on the worksheet 'Calculation' => Calculation!$C$2 => named name is '
**cellC2**' - cell range A2:K27 in the worksheet 'TableS7' => TableS7!$A$2:$C$27 => named name is '
**table**' - range of cells A1:K1 on the worksheet 'TableS7' => TableS7!$A$1:$C$1 => named name is '
**firstRowTable**'

Also, on the worksheet 'TableS7' I used Conditional Formatting to indicate that colored the cell, from which the formulas in the worksheet 'calculation' in cell 'C2' 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 cell C2 is on another worksheet 'Calculation' and CF formulas in the respective worksheet 'TabeS7'

**you must use the naming cells 'cellC2'**.

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 cells A2:A102 (

**temperature**) I used to Drop-Down Menu on the worksheet 'Calculation' in column 'B', as seen in cell B2. The name 'temperature' is used in the Data Validation on the worksheet 'calculation' in cells B2:B5. 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 name is '
**temperature**' - cell range A2:B102 on the worksheet 'Corrections' => Correction!$A$2:$B$102 => named name is '
**correctionSuper95**'

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

- The formula for CF using named cell ranges

=$B2=VLOOKUP(

- 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 '

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

=$B2=VLOOKUP(

This worksheet '

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
- the 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 cell column B and C, in this case, B2 and C2. In the cells of columns D and E, Excel automatically calculates the value of a quantity liters of fuel on the basis of conditions in B2 and C2 cells.

Cell B2 (this cell was named as '

The cell C2 (this cell was named as '

Figure 4.

Cell B2 (this cell was named as '

**cellB2**') is related to the calculation in cell E2 of the same worksheetThe cell C2 (this cell was named as '

**cellC2**') is related to the calculation in cell D2 of the same worksheetFigure 4.

- 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 chooses from the drop-down menu that we created in the Data Validation as shown below. Notice that we are here for the 'Source' typed named range A2:A102 on worksheet 'Corrections' and I named him as the 'temperature'. See description above for Sheet 'Corrections'.

- Column 'C' value that is, in fact, the height of the measuring lath after measuring quantities of fuel in the tank. In 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'. - Column 'D' you see the result of quantities of fuel which is calculated the Excel with regard to the measured height of the column 'C' and the base table manufacturers in the worksheet 'TableS7'.
- Column 'E' 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 column 'D' cell 'D2'

**using named cell ranges**

This "Kilometre 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 cell D2. 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 column 'E' cell 'E2' which uses the named cell ranges

=VLOOKUP(B2;

**correctionSuper95**;2;FALSE)*D2

- The formula in column 'E' cell 'E2', 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 cell 'A42' on the worksheet 'Corrections' and returns a value from the same row but in column 2 (the second column of the range of the column "B"), 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.