May 31 2021 10:55 PM - edited May 31 2021 11:27 PM
Hello. A function is required to perform interpolation (finding intermediate values). The FORECAST formula not only finds an incorrect intermediate value, but also, as the example shows, inserts an already known value incorrectly. Thank you in advance.
Jun 01 2021 12:49 AM
If you want linear interpolation between the two nearest points:
=FORECAST(F160,INDEX(B160:B239,MATCH(F160,A160:A239,1)):INDEX(B160:B239,MATCH(F160,A160:A239,1)+1),INDEX(A160:A239,MATCH(F160,A160:A239,1)):INDEX(A160:A239,MATCH(F160,A160:A239,1)+1))
Jun 01 2021 12:51 AM
Click on the link, you will find instructions and an example.
The example is also attached as a file
If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.
Hope I was able to help you with this information.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Jun 01 2021 01:54 AM - edited Jun 01 2021 01:56 AM
Assuming you are estimating y=f(x) at the point x=z by linear interpolation
= LET(
x₁, XLOOKUP(z,x,x,,-1),
x₂, XLOOKUP(z,x,x,,1),
y₁, XLOOKUP(z,x,y,,-1),
y₂, XLOOKUP(z,x,y,,1),
w₁, (x₂-z)/(x₂-x₁),
w₂, (z-x₁)/(x₂-x₁),
w₁*y₁+w₂*y₂)