Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Function to Interpolate a List
One way:
First - note that you're not really interpolating based on the range of data - you're simply finding the linear interpolation between the two closest values. The result you're seeking, 11, certainly wouldn't fall on a smooth trendline given your data. Public Function FindX(xRange As Range, yRange As Range, _ y As Range, Optional bAscend As Boolean = True) As Double Dim maxX As Double Dim maxY As Double Dim minX As Double Dim minY As Double Dim matchPoint As Long Dim matchType As Long If bAscend Then matchType = 1 Else matchType = -1 End If matchPoint = Application.Match(y, yRange, matchType) If yRange(matchPoint) = y Then FindX = xRange(matchPoint) Else maxX = xRange(matchPoint - bAscend).Value minX = xRange(matchPoint - (Not bAscend)).Value maxY = yRange(matchPoint - bAscend).Value minY = yRange(matchPoint - (Not bAscend)).Value FindX = (maxY - y) / (maxY - minY) * (maxX - minX) + minX End If End Function Call as = FindX(B37:B45,C37:C45,E27,FALSE) Note that I made the default for y values in ascending order. The y values must be sorted. Note that you can accomplish the same thing with worksheet formulae: =(INDEX(Ys,MATCH(Y,Ys,-1))-X)/(INDEX(Ys,MATCH(Y,Ys,-1))-(INDEX(Ys,MAT CH(Y,Ys,-1)+1)))*(INDEX(Xs,MATCH(Y,Ys,-1))-INDEX(Xs,MATCH(Y,Ys,-1)+1) )+INDEX(Xs,MATCH(Y,Ys,-1)+1) where Xs, Ys, and Y are your x-range, y-range and unknown y. In article , (Mark) wrote: Need a function to return a X value corresponding to a Y using a list of known X's and Y's. Example: Find the X for Y=320000 interpolating between the known Y's above and below the Y in question. The list looks like this: X Y 0 832900 5 498008 8 390988 10 340000 12 300000 15 255525 18 222355 20 204822 30 148614 The function should return X=11. Known X's are in B37:45 and known Y's are in C:37:C45. The Y in question is in E37. I'd like the corresponding X to be in cell F37. As I understand how VB functions work, the formula in F37 should be something like =myfunction(B37:B45,C37:C45,E37). Seems like a simple common problem and I'd appreciate suggestions anyone may have. Thanks, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please add an INTERPOLATE function. For vector or array data. | Excel Worksheet Functions | |||
Excel 2007 - Interpolate | Charts and Charting in Excel | |||
excel: how do i interpolate in an x-y data series? | Excel Worksheet Functions | |||
How do I interpolate numbers in Excel? | Excel Discussion (Misc queries) | |||
Excel Function to Interpolate a List | Excel Programming |