Excle Charting with Trend Line

[esskaykay]

I have a question on charting in Excel. I have a spreadsheet tracking investments over the past few years. I include a “Trend Line” in the chart via a polynomial regression. However, I’m confused on the “order” of the trend line. It allows values 2-6. It appears 2 is the most linear with varying degrees of alignment after that. Without getting too technical as I am no mathematician, what does the “order” mean and what order would you recommend?

Thanks,
Steve K.

[esskaykay]

I have a question on charting in Excel. I have a spreadsheet tracking investments over the past few years. I include a “Trend Line” in the chart via a polynomial regression. However, I’m confused on the “order” of the trend line. It allows values 2-6. It appears 2 is the most linear with varying degrees of alignment after that. Without getting too technical as I am no mathematician, what does the “order” mean and what order would you recommend?

Thanks,
Steve K.

[MrShorty]

It should not take becoming a mathematician to understand polynomials -- you should have started learning polynomials back in middle school/secondary school in those early algebra classes.

A refresher page that should help you remember what you learned about polynomials http://www.purplemath.com/modules/polydefs.htm note that she uses "degree" in place of "order". The terms mean the same thing.

Quick summary. order is the largest exponent in the polynomal. 2nd order means the largest exponent is 2 (the classic "quadratic equation" ax^2+bx+c=y). 6th order means the largest exponent is 6 (ax^6+bx^5+cx^4+dx^3+cx^2+dx+e).

As for what you should use? That is a difficult question. Standard rule of thumb I see is to use the lowest order needed to get an adequate fit. What an adequate fit means for you, I can't say. You say that you are trying to track investments (which tend to be very "noisy" from what I can see). I would suggest that, beyond simply learning about the algebra of polynomials that you may want to dive even deeper into how other financial analysts analyze investment trends to get a better feel (beyond the simple, empirical polynomial) of how investments should be analyzed.

[shg]

Cross-posted at https://www.mrexcel.com/forum/excel-...rend-line.html

[esskaykay]

It should not take becoming a mathematician to understand polynomials -- you should have started learning polynomials back in middle school/secondary school in those early algebra classes.

A refresher page that should help you remember what you learned about polynomials http://www.purplemath.com/modules/polydefs.htm note that she uses "degree" in place of "order". The terms mean the same thing.

Quick summary. order is the largest exponent in the polynomal. 2nd order means the largest exponent is 2 (the classic "quadratic equation" ax^2+bx+c=y). 6th order means the largest exponent is 6 (ax^6+bx^5+cx^4+dx^3+cx^2+dx+e).

As for what you should use? That is a difficult question. Standard rule of thumb I see is to use the lowest order needed to get an adequate fit. What an adequate fit means for you, I can't say. You say that you are trying to track investments (which tend to be very "noisy" from what I can see). I would suggest that, beyond simply learning about the algebra of polynomials that you may want to dive even deeper into how other financial analysts analyze investment trends to get a better feel (beyond the simple, empirical polynomial) of how investments should be analyzed.

Thank you Mr. Shorty. Here is an example of Order2 & Order3. The red line is what I was looking at.
Order2.jpg
Order3.jpg

Again, thanks