• Using Forecast Dynamically

    From David Cuthill@21:1/5 to All on Fri Jun 4 13:32:47 2021
    I am trying to use Forecast dynamically by limiting the knownx and knowny range based on the value of a given X.

    =FORECAST(X,knownY,knownX)

    If the X value falls within the knownX's (or on a knownX directly) it is not returning the corresponding knownY that seems appropriate since it appears to be factoring in the surrounding values.

    X Y
    18.00 0.000
    10.00 -0.001
    2.00 -0.002
    1.50 -0.024
    1.20 -0.085

    As an example if X is 2.00 then it does not return -0.002 but rather -0.034. And if X is 1.60 then it returns -0.036. What I would like to do (if it makes sense) is to use FORECAST but to limit the knownX and knownY to the the adjacent values above and
    below the X value then it will limit the FORECAST to interpolate between the 2 values. If X is less than 1.20 (the lowest X known value) then use the last 2 values or perhaps the complete range??

    I hope that makes sense. Or maybe there is a better function or approach to use?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Cuthill@21:1/5 to David Cuthill on Sun Jun 6 23:18:17 2021
    On Friday, 4 June 2021 at 14:32:49 UTC-6, David Cuthill wrote:
    I am trying to use Forecast dynamically by limiting the knownx and knowny range based on the value of a given X.

    =FORECAST(X,knownY,knownX)

    If the X value falls within the knownX's (or on a knownX directly) it is not returning the corresponding knownY that seems appropriate since it appears to be factoring in the surrounding values.

    X Y
    18.00 0.000
    10.00 -0.001
    2.00 -0.002
    1.50 -0.024
    1.20 -0.085

    As an example if X is 2.00 then it does not return -0.002 but rather -0.034. And if X is 1.60 then it returns -0.036. What I would like to do (if it makes sense) is to use FORECAST but to limit the knownX and knownY to the the adjacent values above and
    below the X value then it will limit the FORECAST to interpolate between the 2 values. If X is less than 1.20 (the lowest X known value) then use the last 2 values or perhaps the complete range??

    I hope that makes sense. Or maybe there is a better function or approach to use?


    Looks like the function referenced here will do the trick after flipping the ordering of the X's

    https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/sbinterp

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)