Excel Interpolation: Features, Procedure, and Examples

The Excel spreadsheet processor not only allows you to quickly perform various calculations, but also solve quite complex problems. For example, with its help it is possible to carry out mathematical modeling on the basis of a set of discrete values ​​of a particular function, including finding the intermediate value of functions by interpolation. Excel provides a variety of tools for this, which this article will teach you to use.

Interpolation method: what is it?

In computational mathematics, the so-called method of finding intermediate unknown values ​​of the function Y (X) from a discrete set of already known.

The 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 ​​of 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) for which the values ​​at the nodal points X _i coincide with the values ​​Y (X _i ) of the original table, i.e., 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 an extremely useful operator "FORECAST".

Consider the data posted in the table below.

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

• select any empty cell on the sheet of the table processor, where the result from the actions taken will be displayed, for example C1;
• Click on the “fx” icon (“Insert Function”), located to the left of the formula bar;
• in the window “Masters of functions” go to the category “Mathematical”;
• find the operator "FORECAST" and click on "OK".

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

In the same way, fill in the "Known values ​​_ x" field and click on the "OK" button.

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

Graphic Method: Preparation

Interpolation in Excel, an example of which is presented above, is far from the only way to find out 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 table for one of the arguments does not indicate the corresponding function value, as in the one presented below (see cell with address B9).

In this case, interpolation in Excel begins with plotting. For this:

• in the tab "Insert" highlight the table range;
• in the Charts toolbox, select the Chart icon;
• in the list that appears, select the one that is best suited to solve a particular problem.

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

Excel interpolation: graphical solution

Let's deal with the processing of the chart. To do this, select a solid blue line and delete it by pressing the Delete button, which is located on the keyboard.

Then:

• select the plane on which the graph is located;
• in the context menu select the button "Select data ...";
• in the window "Select data source" in the right block, click "Change";
• click on the icon with a red arrow to the right of the "Range of axis labels" field;
• distinguish the range A2: A11;
• click on the “OK” button;
• Re-open the window "Select data source";
• Click on the "Hidden and empty cells" button 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 gap will be deleted, and by moving the cursor to the desired point in the graph, you can see the corresponding values ​​of the argument and function.

Using the special function ND

Now that you know how to make interpolation in Excel using the graphical method or through the “PREDICT” operator, solving many practical problems will not be difficult for you. However, this is not all. The table processor from Microsoft provides an opportunity to find an unknown value of a function using the ND function.

Suppose that the chart is already built, the correct scale signatures are already installed on it. Let's try to close the gap. For this:

• a cell is selected in the table in which there is no function value;
• select the icon "Insert function";
• in the "Function Wizard" in the "Categories" window find the line "Complete alphabetical list" (in some versions of the processor "Check properties and values");
• click on the entry "ND" and click on the "OK" button.

After that, the error value “# N / A” appears in cell B9. However, a break in the schedule is automatically fixed.

You can do even easier: enter the characters “# N / A” (without quotation marks) from the keyboard in cell B9.

Bilinear interpolation

The range of tasks for which modeling can be used by means of functions of one variable is quite limited. Therefore, it makes sense to consider how the double interpolation formula is used in Excel. 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.

New entries are added 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 reverse sequential substitution, the mega-formula necessary for finding the wind pressure at specific parameters is “collected”. For this:

• copy the text of the formula from cell with address J17 to cell J19;
• replace the reference to J15 in the formula with the value in cell J15: J7 + (J8-J7) * J11 / J13;
• repeat these steps until the necessary 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) using formulas of the same type for different subsets of the argument. Next, the values ​​of φ (X) and its derivatives are docked on the boundary values ​​of the arguments of each of the subsets. Excel provides special functions for these purposes, and it is also possible to write macros in VBA. However, they should be created for a specific task, so their study in general does not make sense.

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