Sleeper: Linear interpolation in Excel

[volabos]

Hi,

I am using FORECAST function in Excel to estimate values using linear interpolation. My input data is as follows




0.04

0.50%



0.08

0.65%



0.25

0.80%



0.50

1.05%



1.00

1.40%



2.00

1.80%



3.00

2.10%



5.00

2.50%



10.00

3.05%



15.00

3.50%



20.00

4.00%



30.00

4.50%



Now I estimate the values at below points




0.50



1.00



1.50



2.00



2.50



3.00




However using FORECAST() function I get below estimates




0.50

1.27%



1.00

1.34%



1.50

1.40%



2.00

1.47%



2.50

1.53%



3.00

1.60%



This looks strange to me. Because, as per the input data, the values corresponding to x = 1 is 1.40%. However FORECAST() function estimates 1.34%. This also differs from corresponding estimates from VBA'a LINTERP() function which gives estimate as 1.40%.

Am I doing any mistake correctly using FORECAST() function?

Any pointer will be very helpful.

Thanks for your time.

[Paul_Hossler]

Looks expected, but not too accurate since FORECAST assumes linear interpolation, but your data look expotential

30364

[arnelgp]

there is also a tutorial here using Forecast() on linear interpolation:
Linear Interpolation in Excel | How to do Linear Interpolation with Examples (wallstreetmojo.com) (https://www.wallstreetmojo.com/linear-interpolation-in-excel/#popmake-95356)

[p45cal]

I am using FORECAST function in Excel to estimate values using linear interpolation. My input data is as follows
You could write a formula to do linear interpolation between only the two points above and below the x value you're trying to get the y value of, but it still wouldn't be very accurate.
In the attached, I've plotted your data and added a trendline to the chart and the trendline which best fits your data is a Power trendline. I've also shown the equation and the R-squared (0.9984) on the chart which means it's a good fit.
The equation shown on the chart has had a few digits added to it using the method described here: https://www.ablebits.com/office-addins-blog/add-trendline-excel/#show-more-digits-trendline-equation
I've copied those values to cells C15:C16 and used them in the formulae in cells C17:C23. The results are good. A fiddly solution.

Another way is to dispense with the equation on the chart (and the chart) and calculate for ourselves the 2 coefficients, that's been done in cells E15:E16. Cells E17:E23 do the interpolation. Same results (near enough).

You can incorporate the coefficients calculation and interpolation into a single formula, see cells G17:G23.

If you have office 365 you can convert this formula into a lambda formula (see the Names in the workbook) which makes things a lot easier for the user. It makes a new function Forecast.Power which you use in exactly the same way as you use FORECAST and you don't anything else. This has been done in cells H17:H23. What's more, with Excel's spilling formulae you can make this into a single cell formula, see cell I17 (green highlight) which spills down:

=Forecast.Power(A17:A23,B1:B12,A1:A12)

[p45cal]

So nice when 3 people have gone to significant effort to help volabos and he can't even be bothered to feed back whether anything has helped, sigh.

[Aussiebear]

So nice when 3 people have gone to significant effort to help volabos and he can't even be bothered to feed back whether anything has helped, sigh.

Sadly, it seems that there are people out there who either don't acknowledge the assistance received from others or respond to members asking for clarification to a question. My initial thinking was "Volabos is busy" and will get back to the forum, but..., in reviewing the threads created by Volabos, it seems that he creates a thread by asking a question, and then in most cases never ever responds. Since assistance is provided within the forum on a voluntary basis, (and we are extremely grateful to those who do so), then it's at your discretion as to whether any further assistance is provided. Eventually the message will get through, when very few if any, members offer to help that person.

[melbawab]

Per the documentation, the FORECAST() Function is a linear regression, not an interpolation function.
https://support.microsoft.com/en-us/office/forecast-and-forecast-linear-functions-50ca49c9-7b40-4892-94e4-7ad38bbeda99

The regression line does not necessarily go through each data-point.