Linest / Logest any polynomial trendlines 2

[remeng]

Hi all

I am looking into linest and logest and there uses. I have read several articles here and elsewhere on the use of linest and logest. I Have performed the examples with the articles and have gotten the proper outputs for a linear trendline. The question that I have: Is there a way to perform the same output function with linest / logest that you get for a linear trendline that you can also do for a second order polynomial trendline? If so how?

A follow up question if it is possible, can this be added to a dynamic range that has over 16 x values for Excel 2003?

Thanks all!

 

[SkipVought]

Hi,

I don't know the answer, but I'd plot some data in a chart and then play with the trends and use the macro recorder. You might discover something.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Skip,

I've already tried the macro recorder, but it does something in the background that just adds the trendlines. This is the same with the Linest function.

Thank you for the quick response. I know that your a library of information and was hoping that you might have had an idea.

remeeng

 

[mintjulep]

Your question is pretty vague - you don't really tell us what you've done, or what you get, or what you want to get.

So, shot in the dark.

Linest and Logest are array functions.

 

[SkipVought]

Check out the TREND function.

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.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

What I am looking to do is if the formula is:

y = 0.1018x^2 + 1.7125x - 1.5643

like linest, be able to pull out this:

0.1018
1.7125
- 1.5643

now this is done with linest for a linear trendline, but can not be used for a polynomial trendline. Is there a way to do this without using copy and paste since this is going to be in a macro and be used quite allot.

 

[SkipVought]

Please explain your problem completely, as the FRAGMENT that you have posted seems to be the tip of the iceberg.

For instance you state, "now this is done with linest for a linear trendline, but can not be used for a polynomial trendline."

Why NOT?

"Is there a way to do this without using copy and paste since this is going to be in a macro and be used quite allot."

Yes.

But we have to know a bit more in context.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Ok Skip. Sorry for being out there.

The idea is to generate a dot plot, apply the polynomial trendline then display the r^2 and equation. After that, break out the equation as I have shown as well as the r^2 and place them as values in a defined cell. Normally the Linest function would work, but I only understand that it can be used for a linear trend line such as y=mx+b and not a polynomial such as y=nx^2+zx+c .

n,z, and c are what I need.

I hope this makes allot more sences.

 

[SkipVought]

"n,z, and c are what I need."

Can't these values be derived?

Can you post some sample data that we can work with and ALSO what you expect to get from that data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

As posted on a Tek-Tips sister site by member clyde38:


These equations assume that your sheet has two named ranges: x and y.

Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic Trendline
Equation: y = (c * LN(x)) - b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN,LN(x),,),1,2))
b: =INDEX(LINEST(LN,LN(x),,),1)

Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN,x),1,2))
b: =INDEX(LINEST(LN,x),1)

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas

 

[SkipVought]

So you want to be able to do...
[tt]
2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1,1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)
[/tt]
where the array in the {BRACES} is some dynamic range?

YES?

Could we see some data that you might use to calculate these values, and what you expect to get?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I dummied up some data, added a 2nd Order Polynomial Trendline via the Chart Wizard and then used your formulas for C1, C2 & B to generate source data. I then plotted the generated data and the new series and the 2nd Order Polynomial Trendline are indistinguishable in the chart. So I guess it works!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Hey Skip,

Didn't have a chance to reply late yesturday due to being in a meeting on this project. Guess what, the manager's changed the scope of the project. While the tool is still needed the calculation has changed. I need to do some research on what is still needed due to the requirements and will post back. I did a workaround however that seems to generate the second order polynomial.

To generate the first order or linerar, one enters an X and Y Value. So if Y is a Location and X is a collected value. Linest will generate the first order or linear outputs. To generate a second order polynomial simply add another column and square the X values ie: X*X = X^2

Using the X^2 value and the Y value, use linest to generate the second order polynomial. This is similar to what Skip has already stated.

 

[lionelhill]

Excel's Solver offers an alternative approach that works for all equations you'd care to fit.

Next to your plotted data, put in a new column containing data calculated from whatever equation you care to use (anything is possible!). For all the parameters of this equation (c1, c2, etc.) refer to some suitable cells on the worksheet.

At the moment the values calculated in your new column will be total rubbish because the c1 etc. cells don't contain the right values, obviously.

Sum the squared differences between the calculated values and the values you'd like to fit, placing the result in one cell. You can do this however you want.

Now use Tools-Solver to minimise the cell containing the sum of squared differences by manipulating the cells containing c1, c2 etc.

Excel's solver is not too shabby, and will usually arrive at some sort of sensible values provided you are fitting a realistic equation to adequate data.

Note, however, that it doesn't have any chance to warn you of hideous pathologies going on in your data. If, to take an obvious example, you try to fit a curve such as y = ax/(b+x) to data that lie on a perfect straight line, you will get bad results for a and b.