Interpolation in Excel: features, procedures and examples

Excel table processor allows not only to quickly perform various calculations, but also to solve rather complex tasks. For example, it can be used to perform mathematical modeling on the basis of a set of discrete values of a function, including finding the intermediate value of functions by interpolation. In Excel, for this purpose, various tools are provided, which this article will teach.

Interpolation method: what is it?

In computational mathematics, the method of finding the intermediate unknown values of the function Y (X) by a discrete set of already known ones is called this way.

Interpolation of the function Y (X) can be carried out only for those arguments that are inside the interval [X ₀ , X _n ], such that the values Y (X ₀ ) and Y (X _n ) are known.

If X does not belong to [X ₀ , X _n ], then the extrapolation method can be used.

In the classical formulation of the interpolation problem, it is required to find an approximate analytic function φ (X) whose values at the node points X _i coincide with the values Y (X _i ) of the original table, that is, the condition φ (X _i ) = Y _i (i = 0,1,2, ..., n).

Linear interpolation in Excel

In the most famous table processor from Microsoft, there is a very useful operator, PRESCASE.

Consider the data placed in the table below.

The first column contains the arguments x, and in the second column the corresponding values of some linear function f (x). Suppose we want to know the value for the argument x = 28. For this:

• Allocate any empty cell on the sheet of the table processor, where the result will be output from the actions performed, for example C1;
• Click on the icon "fx" ("Insert function"), located to the left of the formula line;
• In the window of the "Masters of Functions" they enter the category "Mathematical";
• Find the operator "PRESCASE" and click on "OK".

There are 3 fields in the arguments window. In the first enter the value of the argument from the keyboard (in a specific task this is 28). To fill the "Known values _ y" field, click on the icon with the red arrow to the left of the corresponding window and select the corresponding area on the sheet. In a particular case, this is part of column B with addresses from the B2: B7 range.

Similarly, fill in the field "Known values _ x" and click on the "OK" button.

As a result, the value in the highlighted cell C1 is 176, which is the result of the interpolation procedure.

Graphical method: preparation

Interpolation in Excel, an example of which is presented above, is by no means the only way to find out the intermediate unknown values of the function Y (X) from a discrete set of already known ones. In particular, a graphical method can be applied. It can be useful if the corresponding function value is not specified in the table to one of the arguments, as in the one presented below (see cell with address B9).

Interpolation in Excel in this case begins with the plotting. For this:

• In the "Insert" tab allocate a tabular range;
• In the tool box "Diagrams" select the "Graph" icon;
• In the list that appears, select the one that is best suited for the solution of a specific task.

Since cell B9 is empty, the graph is broken. In addition, it has an additional line X, which is not necessary, and on the horizontal axis, instead of the argument values, items are listed in order.

Interpolation in Excel: a graphical solution

We will deal with the processing of the graph. To do this, select a solid blue line and delete it by pressing the Delete button, which is on the keyboard.

Then:

• Allocate the plane on which the graph is located;
• In the context menu select the button "Select data ...";
• In the "Select Data Source" window in the right block, click "Change";
• Click on the icon with the red arrow to the right of the "Axis Signature Range" field;
• Allocate the range A2: A11;
• Click on the "OK" button;
• Again, the "Select data source" window is opened;
• Click on the button "Hidden and empty cells" in the lower left corner;
• In the line "Show empty cells" the switch is moved to the "Line" position and click "OK";
• Confirm these actions in the same way.

If everything is done correctly, the break will be deleted, and by hovering the cursor on the desired point of the graph you can see the corresponding values of the argument and function.

Using a special LP function

Now that you know how to do interpolation in Excel using the graphical method or with the help of the PRESCASE operator, solving many practical problems for you will not be a big deal. However, that's not all. A table processor from Microsoft presents the possibility of finding an unknown value of a function using the ND function.

Suppose that the graph has already been built, the correct scale signatures have already been installed on it. Let's try to close the gap. For this:

• Select a cell in the table where the function value is missing;
• Select the "Insert function" icon;
• In the "Function Wizard" in the "Categories" window find the string "Full alphabetical list" (in some versions of the processor "Checking properties and values");
• Click on the "ND" entry and press the "OK" button.

After that, the error value "# N / D" appears in cell B9. However, the interruption of the graph is automatically eliminated.

You can do even easier: from the keyboard in cell B9 the characters "# N / D" (without the quotes).

Bilinear interpolation

The circle of problems for which it is possible to use modeling by means of functions of one variable is rather limited. Therefore, it makes sense to consider how the double interpolation formula in Excel is used. Examples can be very different. For example: there is a table (see below).

It is required to calculate the wind pressure at a span of 300 m at an altitude of 25 m.

Add new entries to the table as shown in the figure (see below).

As you can see, cells for height and span in J1 and J2 are added to it.

By the reverse successive substitution, the "mega formula" needed to find the wind pressure at specific parameters is "collected". For this:

• Copy the formula text from the cell with address J17 to cell J19;
• Replace the reference to J15 with the value in cell J15: J7 + (J8-J7) * J11 / J13;
• Repeat these actions until the required formula is obtained.

Using spline

The previous method is rather cumbersome, so in some cases spline interpolation is preferable. In Excel, its essence lies in finding the interpolating function φ (X) from formulas of the same type for different subsets of the argument. Next, the values φ (X) and its derivatives are joined at the boundary values of the arguments of each of the subsets. In Excel, for this purpose, special functions are provided, and it is also possible to write macros on VBA. However, they should be created for a specific task, so their study in general form does not make sense.

Now you know how to write the double interpolation formula in Excel correctly, or find the unknown value of a linear function by means of built-in operators or a graph. We hope that this information will help you in solving many practical problems.