estimate values between two data points<\/strong>, if the data points are familiar.<\/p>\n\n\n\nIn Excel, we can use linear interpolation with functions and formulas. In the example below, we will show how to do it.<\/p>\n\n\n\n
Linear Interpolation in Excel<\/h2>\n\n\n\n For our example, we will have two data points: (xy, y1) and (x2,y2).<\/strong> Our job will be to find the y-value<\/strong> corresponding to a certain x-value<\/strong>, that is located between x1 and x2<\/strong>.<\/p>\n\n\n\nTo explain better, we will have the following table:<\/p>\n\n\n\n <\/figure>\n\n\n\nBy looking at the table below, we could assume that following the logic of linear interpolation, the y2 number, located in cell A5 will be 20<\/strong> since the progression of x is 2<\/strong>, and the progression of y is 10<\/strong>. To confirm this by formula, and generally to find the way to calculate it automatically, we will use the following formula in cell B5<\/strong>:<\/p>\n\n\n\n=FORECAST(A5, B2:B3, A2:A3)<\/code><\/pre>\n\n\n\nFORECAST formula has three parameters: x, known_ys, and known_xs<\/strong><\/p>\n\n\n\nIn our case, x is number 4, known_ys are 10 and 30, and known_xs are numbers 2 and 6<\/strong>.<\/p>\n\n\n\nThis is the result we will end up with:<\/p>\n\n\n\n <\/figure>\n\n\n\nInstead of the FORECAST formula<\/strong>, we could also use INTERCEPT<\/strong>. Our formula in cell B6<\/strong> will be:<\/p>\n\n\n\n=(A5-A2)*(B3-B2)\/(A3-A2)+B2<\/code><\/pre>\n\n\n\nTo break down this formula:<\/p>\n\n\n\n
\nA5-A2 calculates<\/strong> the difference between the desired x-value and the lower known x-value.<\/li>\n\n\n\nB3-B2 calculates<\/strong> the difference between the upper known y-value and the lower known y-value.<\/li>\n\n\n\nA3-A2 calculates<\/strong> the difference between the upper known x-value and the lower known x-value.<\/li>\n<\/ul>\n\n\n\nThis formula then multiplies the first two differences<\/strong> that we have and divides this result by the third difference<\/strong> in order to find the slope of the line connecting our two data points.<\/p>\n\n\n\nIn the end, the formula adds the result to the lower known y-value<\/strong> to find the interpolated y-value<\/strong> for the given x-value<\/strong>.<\/p>\n\n\n\nWe will get the same result<\/strong> using this formula, as we did with FORECAST<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nJust remember- linear interpolation demands a straight-line relationship<\/strong> between the data points. If the data points are not linear, then we should use other interpolation methods.<\/p>\n","protected":false},"excerpt":{"rendered":"If we talk about linear interpolation in Excel, we first need to understand what this term is. Linear interpolation is a method from…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170],"yoast_head":"\n
Linear Interpolation in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n