In the process of calculating values outside a known area of numerical data, Microsoft Excel comes to the rescue. To automate the calculation process, it is enough to take the available data as a basis and perform a few simple steps. Here is a step-by-step instruction with explanations on how to extrapolate data in Excel below.
Tabular Data Extrapolation
For example, we use the data from the following table, which displays the dependence of the function values on the x argument.
In this case, this is the number of manufactured units of output over a certain time in minutes.
As we see, for our example, the number of units of output manufactured every 15 minutes of time from 0 to 120 minutes (2 hours) inclusive is known.
To extrapolate this data in Excel, you must perform the following steps consistently.
- We determine the x number, in relation to which it is necessary to make a forecast.
In our case, this is a time period for which it is necessary to determine the number of manufactured products.
For example, we are interested in a period of 5 hours 15 minutes, or 315 minutes. We add the following value of the argument we are interested in: 315 in the “x” column.
- Select the cell in which we want to get the desired result, and click on the function wizard icon.
- Enter “forecast” in the search box of the function wizard, and click on the found function. Press “Enter” or click on the “Insert function” button.
In the window of the function values opened, enter the following data:
- In the “x” field: the address of the cell with the “x” value of our interest (it is 315 in our case) – for this purpose, we click on the corresponding cell;
- In the “known_y’s” field, we enter the range of values in the f (x) column (in our case, from 100 to 800 pieces) – by also selecting this range with the mouse;
- In the “known_x’s” field, we enter the corresponding range of values of the “x” column (in our case, from 15 to 120 minutes), as in the previous subparagraph.
- Press “Enter” on the keyboard or click the “Done” button.
- And the desired result will appear in the corresponding cell – in our example, this is the number 2,100, i.e. 2,100 units of products will be manufactured in 3 hours 15 minutes using the extrapolation method.
- In the same way, we can calculate the value of f (x) for any desired value of x. To do this, you should add the known time values (in our case) to the “x” value column, and copy the formula that we entered into the corresponding cell in step 2 into the following cells next to the x values.
So, we figured out how to extrapolate data in Excel. As it turned out, this is not a complex task even for a basic user. It is enough to know the necessary function and have the corresponding numerical data for calculations. Following the steps described above, all the steps for calculating extrapolation in Excel will be simple and straightforward.