software

How to perform an interpolation on a microsoft excel spreadsheet? Step-by-step guide

The degree of scope that Excel has, to study known relationships and work with functions, is quite wide . Therefore, many people do not know all the power that this calculation tool has .

A somewhat complicated task is to estimate values ​​based on known intervals. That is, to establish as precisely as possible what the number is, taking as a reference certain values.

Because of this there is the data interpolation technique , which we will talk about in the following paragraphs. We will teach you the steps you will take to estimate values ​​quickly and safely. Don’t miss out on anything.

What is a data interpolation and what is it for in my spreadsheet?

Data interpolation is a mathematical tool used to estimate data within a known range. By knowing the extreme values, you can interpret those that exist within that set. The opposite of interpolation is known as extrapolation and is data that is outside the known range .

There are different methods of knowing the internal points. Therefore, the results will be approximate estimates that will vary according to the type of method. Among the best known are linear, quadratic or parabolic interpolation, and polynomials .

These methods are useful in practice for estimating values ​​from known data . For example, the number of tourists who visited Barcelona in 2019 can be estimated from specific data obtained in 2017. 

Therefore, it can be applied to any type of information . Whether it is the environmental temperature in a certain area of ​​our country or Europe, the housing density, students enrolled in an educational establishment and even the number of years of average life discriminated by gender of the people.

This means that Microsoft Excel is an excellent tool to use and obtain estimates through data interpolation .

Types of interpolation What are all the ones that exist and can be done in Excel?

The types of interpolation that you can use in Excel to estimate values ​​within a known set are as follows:

Linear

Among the different types of interpolation that exist, the easiest method is linear . This is because the possible solutions of the variable X is only one . Furthermore, by joining two points on a graph, the line can already be drawn, so we can find the pairs X 0 ; Y 0 and X 1 ; And 1 has fewer steps.

This does not mean that this method is not effective or that it is much better than the others, since it is not possible to determine exactly which is better if the behavior of the curve is not taken into account.

For this function the formula is used:

  • Y 0 + {[(XX 0 )] / (X 1 -X 0 )]} * (Y 1 -Y 0 ) .

You can also know the equation of the line by means of the following function:

  • AXIS ( known_y ; known_x) to determine the constant a .
  • SLOPE ( known_y ; known_x) to set the slope, that is b.

Parabolic

The parabolic function, also known as quadratic or level 2 polynomial, presents a graph with a minimum or maximum and has the equation formula y = a + bx + cx2 . The function is a polynomial that is used to estimate values ​​is y = a + b (xx 0 ) + c (xx 0 ) (xx 1 ).

To work in Excel, you can replace the values ​​of the equation, or use formulas such as MATCH and INDEX .

Polynomial Level 3

The function y = anxn + … + a1x + ao is used; with n = 3 . In this way we can determine that a is a0, a1, a2, a3 and x is x0, x1, x2, x3 .

To use Excel and establish the vector a, this formula must be created:

  • = LINEST (y, x ^ (1,2,3,4))

Polynomial Level 4

Like the level 3 polynomial, the “number n” of polynomials can be executed , so the treatment is the same from level 3 to the number n .

In this way we can conclude that the method of degree 4 has the same steps and identical characteristics to solve a value within a known interval as that of degree 3 .

Steps to interpolate data or ranges in a Microsoft Excel worksheet

Below, you will be able to find the steps you will have to perform to interpolate data within a known range using Microsoft Excel . We will show you a guide for a linear function, a parabolic function and for polynomials of level 3 and 4.

Let’s see them:

Linear

To use a linear interpolation in Excel you will have to divide the task into two stages.

The first thing you will have to do are these steps:

  • Go to the “Formula” tab and then look in the “Function Library” menu for the “Search and Reference” tool .
  • When you click on it, a window will open, you will have to choose the “MATCH” function . Since in this way you will find a relative position within the matrix .
  • Choose as the searched value a cell in which you want to obtain the data.
  • Next, select the X- axis matrix .
  • In type of coincidence you will have to choose the number 1, since if you choose 0 it will be exact or if you choose number -1 you will have a higher value result .

The value you will get is equal to 1 . This means that the values ​​of Y correspond to the estimate you are looking for.

Now you will need to use the INDEX formula to set the values X 0 , Y 0 and X 1 , Y 1 .

To do this, follow these steps:

  • Use the formula INDEX (array; value of Y when it is the first term ) .
  • In this way you will be able to obtain the values ​​you are looking for .
  • When you have the four values you will have to replace in the formula Y 0 + (((XX 0 )) / (X 1 -X 0 ))) * (Y 1 -Y 0 ) .
  • You get the Y value . That means it is the estimated value when X is between 2 values.

Parabolic

When looking for the type of equation y = a + bx + cx2 we will do it graphically to find the parabolic method . You can also do it as we get it with the MATCH and INDEX function, but we want to show you a simpler method for simple cases.

The steps to follow are those:

  • Once you have entered the X and Y values, select the matrix .
  • Go to “Insert” and then click on “Recommended Charts” .
  • Find the option “Dispersion (XY)” and click on it.
  • Choose the chart that suits you best.
  • Right click on the line that was drawn .
  • Click on the option “Add trend line …” .
  • Choose the “Polynomial” function and then write “2” in the “Degree:” field .
  • Scroll down and check the boxes “Point to intersection”, “Present equation on graph” and “Present R-squared value on graph” . This will be able to get all the data you need to find the values you want from Y .

Polynomial Level 3

The steps you must do are the same as those shown for the parabolic function. Once you have all the data loaded into a table you will actually insert a drawing and then establish the trend line.

To finish you must activate the same boxes and thus you will obtain the formula you need to obtain any value of Y.

Polynomial Level 4

The same thing happens with the level 2 polynomial, or parabolic, and level 3, you will have to do the same steps for a level 4 function .

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button