Home |
Search |
Today's Posts |
#1
|
|||
|
|||
linear interpolation function in excel
Given a series of x and y data, how can I interpolate to find y given a value
of x based only on a line between the two adjacent points in the data series? This would be like the TREND() function, only I don't want regression of the entire data series, just the (x,y) data points immediately above and below the input x value. This seems such fundamental and essential function that I've been exasperated for years that Excel doesn't have it or directions how to find it. I realize that Excel calculates these individual lines every time it connects the dots in a graph, but not having a function to achieve the same result on a worksheet is puzzling. Am I missing something? |
#2
|
|||
|
|||
I have good news.
The FORECAST() function, which is usually used to extrapolate outside a range of known points also interpolates for a point between two known points. Checkout help for the function syntax. (this is an un-advertised feature of the function) -- Gary''s Student "tskoglund" wrote: Given a series of x and y data, how can I interpolate to find y given a value of x based only on a line between the two adjacent points in the data series? This would be like the TREND() function, only I don't want regression of the entire data series, just the (x,y) data points immediately above and below the input x value. This seems such fundamental and essential function that I've been exasperated for years that Excel doesn't have it or directions how to find it. I realize that Excel calculates these individual lines every time it connects the dots in a graph, but not having a function to achieve the same result on a worksheet is puzzling. Am I missing something? |
#3
|
|||
|
|||
Gary"s Student, you do not have good news , but maybe you don't understand
the question being asked. Like TREND, FORECAST uses all the data points in the array and finds a best fit considering the entire array of data points. Suppose A1:A4 = 1,2,2,1 (known y values) and B1:B4 = 1,2,3,4 (known x values). FORECAST(2.5,A1:A4,B1:B4) returns a y value of 1.5. The function everybody and their cousin wants - INTERPOLATE(2.5,A1:A4,B1:B4) - would return a y value of 2. Put these numbers in the spreadsheet and try it. Hopefully this makes sense to you so you'll spreading wrong information about interpolation on this board. I really wish you were right, though. "Gary''s Student" wrote: I have good news. The FORECAST() function, which is usually used to extrapolate outside a range of known points also interpolates for a point between two known points. Checkout help for the function syntax. (this is an un-advertised feature of the function) -- Gary''s Student "tskoglund" wrote: Given a series of x and y data, how can I interpolate to find y given a value of x based only on a line between the two adjacent points in the data series? This would be like the TREND() function, only I don't want regression of the entire data series, just the (x,y) data points immediately above and below the input x value. This seems such fundamental and essential function that I've been exasperated for years that Excel doesn't have it or directions how to find it. I realize that Excel calculates these individual lines every time it connects the dots in a graph, but not having a function to achieve the same result on a worksheet is puzzling. Am I missing something? |
#4
|
|||
|
|||
Google groups is your friend. Copy the code below into a code module, and
use as your describe. HTH, Bernie MS Excel MVP Function Interpolate(Xnow As Double, _ XRates As Range, YRates As Range) As Double Application.Volatile Dim hi As Long Dim lo As Long Count = XRates.Count If XRates.Count < YRates.Count Then Interpolate = _ "Ranges need to be the same size" Exit Function End If For hi = 1 To Count If XRates(hi) Xnow Then Exit For Next If hi Count Then Interpolate = YRates(Count) Exit Function End If If hi = 1 Then Interpolate = YRates(hi) Exit Function End If lo = hi - 1 Interpolate = YRates(lo) + (Xnow - XRates(lo)) / _ (XRates(hi) - XRates(lo)) * _ (YRates(hi) - YRates(lo)) End Function "tskoglund" wrote in message ... Gary"s Student, you do not have good news , but maybe you don't understand the question being asked. Like TREND, FORECAST uses all the data points in the array and finds a best fit considering the entire array of data points. Suppose A1:A4 = 1,2,2,1 (known y values) and B1:B4 = 1,2,3,4 (known x values). FORECAST(2.5,A1:A4,B1:B4) returns a y value of 1.5. The function everybody and their cousin wants - INTERPOLATE(2.5,A1:A4,B1:B4) - would return a y value of 2. Put these numbers in the spreadsheet and try it. Hopefully this makes sense to you so you'll spreading wrong information about interpolation on this board. I really wish you were right, though. "Gary''s Student" wrote: I have good news. The FORECAST() function, which is usually used to extrapolate outside a range of known points also interpolates for a point between two known points. Checkout help for the function syntax. (this is an un-advertised feature of the function) -- Gary''s Student "tskoglund" wrote: Given a series of x and y data, how can I interpolate to find y given a value of x based only on a line between the two adjacent points in the data series? This would be like the TREND() function, only I don't want regression of the entire data series, just the (x,y) data points immediately above and below the input x value. This seems such fundamental and essential function that I've been exasperated for years that Excel doesn't have it or directions how to find it. I realize that Excel calculates these individual lines every time it connects the dots in a graph, but not having a function to achieve the same result on a worksheet is puzzling. Am I missing something? |
#5
|
|||
|
|||
On Fri, 9 Sep 2005 12:42:29 -0700, tskoglund
wrote: Given a series of x and y data, how can I interpolate to find y given a value of x based only on a line between the two adjacent points in the data series? This would be like the TREND() function, only I don't want regression of the entire data series, just the (x,y) data points immediately above and below the input x value. This seems such fundamental and essential function that I've been exasperated for years that Excel doesn't have it or directions how to find it. I realize that Excel calculates these individual lines every time it connects the dots in a graph, but not having a function to achieve the same result on a worksheet is puzzling. Am I missing something? If X is your range of X's, Y your range of Y's, and NewX is the new value, then: =TREND(OFFSET(Y,MATCH(NewX,X),0,-2),OFFSET(X,MATCH(NewX,X),0,-2),NewX) This formula assumes your X's are in ascending order. This formula will give an #N/A error if NewX is less than the minimum X. This formula will give a #VALUE! error if NewX is equal to or greater than the maximum X. These errors are in accord with your specifications that the New X be "between" two adjacent points, but I would eliminate the error when it matches the maximum X with this addition to the above formula: =IF(NewX=MAX(X),INDEX(Y,MATCH(NewX,X)), TREND(OFFSET(Y,MATCH(NewX,X),0,-2), OFFSET(X,MATCH(NewX,X),0,-2),NewX)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Slow Function Argument Window | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions | |||
Access Module coded converted to Excel Function | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) |