Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2402692%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2402692%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20A%20function%20is%20required%20to%20perform%20interpolation%20(finding%20intermediate%20values).%20The%20%3CSPAN%3EFORECAST%3C%2FSPAN%3E%20formula%20not%20only%20finds%20an%20incorrect%20intermediate%20value%2C%20but%20also%2C%20as%20the%20example%20shows%2C%20inserts%20an%20already%20known%20value%20incorrectly.%20Thank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Zhenya770_0-1622527108680.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285241i251A4A878AA7C741%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Zhenya770_0-1622527108680.png%22%20alt%3D%22Zhenya770_0-1622527108680.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2402692%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2403028%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2403028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1068018%22%20target%3D%22_blank%22%3E%40Zhenya770%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20linear%20interpolation%20between%20the%20two%20nearest%20points%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DFORECAST(F160%2CINDEX(B160%3AB239%2CMATCH(F160%2CA160%3AA239%2C1))%3AINDEX(B160%3AB239%2CMATCH(F160%2CA160%3AA239%2C1)%2B1)%2CINDEX(A160%3AA239%2CMATCH(F160%2CA160%3AA239%2C1))%3AINDEX(A160%3AA239%2CMATCH(F160%2CA160%3AA239%2C1)%2B1))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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