Microsoft Excel - How to Interpolate/extrapolate for a value in a function and match trendline equation

I have a set of data, as follows:

X     Y 1.0   0.710

0.8   0.868

0.6   1.072

0.4   1.340

0.2   1.724

When charted in Excel yields a trendline with an equation:

How do I forecast the X value in a function that will yield the same value as if I manually solved for it at a given Y (assume Y=0.1) from the above equation?

Hello Dan

If you put the formula into a cell referring to another cell you can use Solver to adjust your reference cell to give the answer you want in your formula cell.

I don't think there is any way to do it accurately in the chart itself though you could get an approximation by using the Forecast function on the trendline options.

There are also FORECAST and GROWTH functions in excel but they are only linear and exponential.

Hope this helps.

Harry

Hello again Dan,

The following four formulae will get the equation parameters for you without having to transcribe them from the trend line equation.

=INDEX(LINEST(B3:B7,A3:A7^{1,2,3}),1)
=INDEX(LINEST(B3:B7,A3:A7^{1,2,3}),2)
=INDEX(LINEST(B3:B7,A3:A7^{1,2,3}),1,3)
=INDEX(LINEST(B3:B7,A3:A7^{1,2,3}),1,4)

Regards

Harry

Thanks Harry!  I'm going to play around with this a bit and see if I hit any more walls...

• You can use TREND for polynomial curve fitting by regressing against the same variable raised to different powers. For example, suppose column A contains y-values and column B contains x-values. You can enter x^2 in column C, x^3 in column D, and so on, and then regress columns B through D against column A.

Hi Dan,

To use the TREND you have to select the columns with your y's and various powers in them and also have as many x-values in an array as power columns (three in this case) that you want to predict the value of y at (they can all be the one value).

I think that your best bet is to use the formulae that I gave you in combination with the Solver option.

Regards

Harry

Harry,
I'm pretty lost.  Can you show in more detail how to use the TREND?

Also, I'm not really understanding the Help guidance in Excel for the Solver option (part of that may be that my Solver window is coming up in Portugese...).  Can you explain how you plug the formulae into Solver?  I'm assuming I need to get those into the Objective cell - Is this right?

Thanks again for your help!

Hi Dan

This spreadsheet has it.

extrapolate.zip

When you open solver the relevant cells should be selected for you.  You can also use goal seek but it is not quite as accurate in my experience.  The trend usage is also there but in its current configuration it gives you a value for Y for a particular value of X which as I understand it is not what you want.  It will also likely give you a much different answer the further you extrapolate.

One other thing, is there a particular reason for using such a high order relationship and extrapolating so far.  there is potential to get some really weird results depending on what your variables are supposed to be.  I would suspect that some of the exponent terms are not statistically significant.

Regards

Harry