Polynomial trendline graph in Excel

[Jeroen Vrijmoed]

In an Excel spread sheet I have a small table with 3 data sets. I have made a trendline graph of this data. The Polynomial trendline is what I would expect the shape of the line to be.

Data sets: 12, 24, 36 on the first row and below that 110, 130, 175 on the 2nd row.
So at 12 months the figure is 110, at 24 it is 130 and at 36 months it is 175.

In the help menu of Excel I learned that the formula used is:
y=b+(c1)x+(c2)x2+(c3)x3+...+(c6)x6 so the exponent goes from 1 to 2 to 3 etc.
The formula with these 3 data sets I got is:
y = 0,0868x2 - 1,4583x + 115

My question is: How did the program get the figures 0,0868 and 1,4583 and 115 thus c1 and c2 and b?
I understand that 115 is b and that this is the y-intercept.

I am trying to figure this out because I have some other data sets with only 2 data sets and this gives a trendline which is more or less streight, which does not correspond with my expectations of the real situation. I would like to adapt their formulas to get a trendline shape more or less similar with the 3 data set trendline


Would appreciate any comments. To repeat the question: how does Excel calculate c1, c2 and b of the polynomial trendline

 

[tkhunny]

You have asked a very large question.

1) Normally, what you have would be called "points" not "data sets". The whole collection of points might be called a "data set".
2) You can always create a polynomial of one degree less than the number of points. 3 points leads to a quadratic. 2 points leads to a line.
3) The line should be obvious. How many points determines a unique line? Two!
4) You will have to learn "Function Notation", 'Substitution", and "Solution of Multiple Simultaneous Equations" in order to solve this problem.

Where does that leave us?

Note: You can do other types of things, too, but you'll have to get up to speed on "Least Squares Approximation" to wander down other paths.