Interpolation values from a curve data

  • I have the values for 0, 30, 60, 90, 120, 150, 180 degree and its results values in a table. a continues curves can be plot from those value.


    If I would like the results values for (Let say 160 degree) as a input with my other macro. What is the best way to do?


    It will be ideal if the values lay within the curves. but if that is too complex. a linear interpolation between results values wil be fine.


    I belived someone have done this before. just not able to find it.



    Many thanks

  • Re: Interpolation values from a curve data


    see if the FORECAST function helps you


    as per the help file


    FORECAST


    Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.


    also check out GROWTH and TREND

  • Re: Interpolation values from a curve data


    This the the data I have


    Degree Results
    0 0.015
    30 0.013
    60 0.011
    90 0.092
    120 0.022
    150 0.013
    180 0.017


    Tried the forecast function, but the results gives 0.2459 when I put degree = 0. which could nto be correct. Please help

  • Re: Interpolation values from a curve data


    are you sure


    using the INTERCEPT function I am getting 0.02432


    (the value of "a" in Y=a+bx and Y=a if x=0)


    ...the same value I am getting even if I solve for Y=a+bx manually or if I use FORECAST


    if you want I will upload the workbook


    just to check your Degree col contains X-values (independent function) and "Results" column contain Y-values (dependent function) is it not???

  • Re: Interpolation values from a curve data


    From the following data


    _____A_______B
    1__Degree__Results
    2 ___0______0.015
    3 ___30_____0.013
    4 ___60_____0.011
    5 ___90_____0.092
    6___120_____0.022
    7___150_____0.013
    8___180_____0.017


    Below is how I put in formula bar. FORECAST(x,known_y's,known_x's)
    which is =FORECAST(0,B2:B8,A2:A8), and it gives 0.02459.


    where I am expecting a value of 0.015.


    Did I do somthings wrong with the formula?


    Many thanks

  • Re: Interpolation values from a curve data


    the FORECAST equation works on Y=a+bx+e where e is the error distributed normally with mean zero and StdDev of 1...therefore historical values of Y will have error values since any forecast value of Y is affected by other data pairs


    to clarify if for x=180 you were to assign some other value of Y then your regression equation will change...a and b params will get some other values


    AFAIK regression equations hardly give exact predicted values for historical data pairs since they are best fit equations....


    pangolin

  • Re: Interpolation values from a curve data


    emm.. then I need to look for solution outwhere then degree = 15 and can not gives a result between 0.015 and 0.013.


    I know the answer is out there. anyone please?

  • Re: Interpolation values from a curve data


    Quote from Yoman

    emm.. then I need to look for solution outwhere then degree = 15 and can not gives a result between 0.015 and 0.013.


    I know the answer is out there. anyone please?


    I'd use a slightly different approach...


    What's the corresponding formula or result for each degree input?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Interpolation values from a curve data


    The results is a input from other worksheet which can't fit a curve into it.


    I found a solution from other post.


    http://www.ozgrid.com/forum/sh…7&highlight=interpolation


    think it is linear, but I belive it work. Just one problem, when I put degree = 0, it gives error. when user input 0 degree, I change it to 0.0000001. It seen working and does not effect my result.


    [vba]Function Linearinter22d(inputs As Range, X As Double, Y As Double) As Double

    Dim nx As Long, ny As Long
    Dim lowerx As Long, lowery As Long, upperx As Long, uppery As Long, i As Long

    nx = inputs.Rows.Count
    ny = inputs.Columns.Count

    If X < inputs(2, 1) Then
    lowerx = 2
    upperx = 2
    ElseIf X > inputs(nx, 1) Then
    lowerx = nx
    upperx = nx
    Else
    For i = 2 To nx
    If inputs(i, 1) >= X Then
    upperx = i
    lowerx = i - 1
    Exit For
    End If
    Next
    End If

    If Y < inputs(1, 2) Then
    lowery = 2
    uppery = 2
    ElseIf Y > inputs(1, ny) Then
    lowery = ny
    uppery = ny
    Else
    For i = 1 To ny
    If inputs(1, i) >= Y Then
    uppery = i
    lowery = i - 1
    Exit For
    End If
    Next
    End If

    Dim XL As Double, XU As Double, YL As Double, YU As Double
    Dim temp1 As Double, temp2 As Double

    XL = inputs(lowerx, 1)
    XU = inputs(upperx, 1)
    YL = inputs(1, lowery)
    YU = inputs(1, uppery)
    temp1 = (inputs(lowerx, lowery) * (XU - X) _
    + inputs(upperx, lowery) * (X - XL)) / (XU - XL)
    temp2 = (inputs(lowerx, uppery) * (XU - X) _
    + inputs(upperx, uppery) * (X - XL)) / (XU - XL)
    Linearinter22d = (temp1 * (YU - Y) + temp2 * (Y - YL)) / (YU - YL)

    End Function


    [/VBA]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!