Things to Remember About Interpolate in Excel So for 28 days, we estimated or forecasted the sales of the firm to be 1120. Similarly, we can estimate the sales of the firm for a different number of days using this FORECAST function. the second parameter in it should be 0 as the dependent value is desired on the same column that is selected.
The MATCH function used as a parameter in the OFFSET function is used to generate the position of value that is to be forecasted and so calculate the number of rows. The second OFFSET function used as the third parameter in the FORECAST function is used to select the known_x’s (independent values, i.e. So the first OFFSET function used as the second parameter in the FORECAST function is used to select the known_y’s (dependent values, i.e. Now, if we wish to estimate the sales for, let’s say, 28 days, then we use these above functions as follows: For the value -1, the function will find the smallest value that is greater than or equal to the lookup_value, and the value should be in descending order. For value 0, the MATCH function finds the first value that is exactly equal to the lookup_value. For value 1, the MATCH function will find the largest value that is less than or equal to the lookup_value, and the value should be in ascending order. The default value for this match_type is 1. match_type: Optional this can take values 1, 0,-1.lookup_array: This is the array or range of cells in which the lookup_value is to be searched.lookup_value: This is the value that needs to be matched or looked upon from the lookup_array.The MATCH function returns the relative position of a lookup value in a row, column, or table that matches the specified value in a specified order. width: Optional Out of the returned reference, this is the columns’ width.height: Optional Out of the returned reference, this is the rows’ height.cols: This is the number of columns to offset right from the starting reference cell.rows: This is the number of rows to offset below the starting reference cell.reference: This is the starting point from where the count of rows and columns will start.OFFSET function returns a cell or range of cells specified in rows and columns, depending on the height and width in specified rows and columns. Let us first see the syntax of the OFFSET function and MATCH function: Now let us see how we use the FORECAST function when the known_y’s and known_x’s are calculated using OFFSET and MATCH function: The sales are linear in this case (which can also be verified manually or using a line graph). a number of units sold in those days) as below: Now let us say we have a dataset of a retail firm, with a number of days and the corresponding sales by the firm in those days (i.e. So we can see in the above screenshot that the FORECAST function also works well for this. So let us now see in the below screenshot what happens when we apply this FORECAST function to interpolate the given x value: known_x’s: This is the known range of x values.known_y’s: This is the known range of y values.
x: This is the value or data point whose corresponding value we wish to interpolate or predict.Now let’s say we wish to interpolate the same value in Example1 using the FORECAST function.įORECAST function estimates a value based on existing values along with a linear trend. So, the FORECAST function can be used in such cases. There can be times when it becomes difficult to memorize the formula. So we can see in the above screenshot that we have interpolated a value with two known values of x and y’s.