Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel

Copper Contributor

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.

Zhenya770_0-1622527108680.png

 

3 Replies

@Zhenya770 

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

@Zhenya770 

 

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.

 

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.

@Zhenya770 

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