Remember Me?

#1
June 4th 21, 09:32 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2020 Posts: 11
Using Forecast Dynamically

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?

#2
June 7th 21, 07:18 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2020 Posts: 11
Using Forecast Dynamically

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/sul...tions/sbinterp

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Sam Excel Worksheet Functions 1 May 12th 09 05:03 PM BBAL20 Excel Discussion (Misc queries) 1 July 24th 08 07:15 AM Cham Excel Discussion (Misc queries) 0 June 4th 07 07:59 PM vvn023 Excel Worksheet Functions 1 February 7th 06 05:20 PM Hella Excel Discussion (Misc queries) 4 March 25th 05 06:54 PM

All times are GMT +1. The time now is 02:22 PM.