Excel

Occasional Visitor

Excel

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.

3 Replies

Re: Excel

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))

Create a forecast in Excel for Windows

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.

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.

Re: Excel

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₂)``````