The purpose of this post is to show that you can use Excel to perform linear interpolation of a value, which is chosen from a data set, this will employ the following functions: VLOOKUP, row, column, direction, indirect and prognosis. At the end you will find a link to download the example.
To make it easy to understand, first of all explain the use of the functions to be used.
VLOOKUP (valor_a_buscar; rango_donde_buscar; index, boolean) ; valor_a_buscar is the value to be searched in the rango_donde_buscar, index is the column value in the row of "match" where you take the value. This function searches the first column of the range, the value being sought. Boolean that takes the values \u200b\u200btrue or false, if false search for the exact match, otherwise it takes the value of the previous. For example, in the example in the following chart, result is 5.48. Since the desired value is not within the values \u200b\u200bof x.
row and column functions, return the number of row and column respectively in a cell, so for example row (F4) results and column 4 (G6) returns 7. And if we use the function in the cell address C5 with the same values, as follows: C5 = e (4.7) this returns $ G $ 4. And by using an indirect role in C6, as follows: C6 = indirect (C5) returns the value that is within the cell $ G $ 4.
these functions we now know how to perform the interpolation of any value within the range of x values \u200b\u200bshown in the picture above. For example if we find the value and x = 2, this will have to interpolate between pairs x, y: (1.8, 5.48) and (2.5, 11.50) and if you want to find the value and for x = 4, will have to interpolate between the pairs: (3.6, 24.92) and (5, 49). It is clear that the solution will have to take care of finding the pair to perform interpolacióm. Found after calculating the corresponding value for x with the forecast function.
explain textually conceptualization to perform this task, to better understand the attachment download here. First, insert a column only has rows for each value x, with the use of the VLOOKUP function is the row number immediately preceding the searched value. Then you have the row number "i" of the previous value and because we know the number of column "j". Other cells are known where the couple needed for interpolation. Check the file for better understanding.
0 comments:
Post a Comment