Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017