so I am currently working on a project for work that takes 700ish data points and uses the LNEST function in Excel to produce a regression of that line. The output from y = 2.59846E-14x^6 + -6.4E-11x^5 + 6.2E-8x^4 + -2.9E-5x^3 + 0.00689x^2 + -0.81814x^1 + 918.821 is the result from =LINEST(K1:K710, J1:J710^{1,2,3,4,5,6}).
Now I am sort of clueless as to how the values were produced. I looked up LINEST on the MS website and it tells me that it is using the "least squares" method to produce a line that best fits the data. After some research I know somewhat of the least squares method to find a line but I don't know how I would apply that with a 6th degree polynomial? I have the data and end result but I don't know what goes on in between as I need to know this in order to write a program to replicate this process. Here is the data points I am talking about, Data Points
I have tried to do some math by drawing out a matrix but I have seem to fall short on that one so if you have any insights that would be awesome.
In you have $n $ data points and a polynomial of degree $6$ then you can use matrices. Let's say the regression function is $y=\beta_0+ \beta_1x^1+\beta_2x^2\beta_3x^3+\beta_4x^4+\beta_5x^5+\beta_6x^6$ and you want to estimate the $7$ parameters $b_j$. Then next we define the following matrices:
$\mathbf y=\begin{bmatrix} y_1\\ y_2\\ y_3 \\ \vdots \\ y_n \end{bmatrix},$ $\mathbf X= \begin{bmatrix} 1 & x_1 & x_1^2 & \dots & x_1^6 \\ 1 & x_2 & x_2^2 & \dots & x_2^6 \\ 1 & x_3 & x_3^2 & \dots & x_3^6 \\ \vdots & \vdots & \vdots & \ddots & \vdots \\ 1 & x_n & x_n^2 & \dots & x_n^6 \end{bmatrix},$ $\hat b=\begin{bmatrix} \hat \beta_0\\ \hat \beta_1\\ \hat \beta_2\\ \vdots \\ \hat \beta_6 \end{bmatrix}$
Here $\hat \beta_0, \hat \beta_1, ..., \hat \beta_6$ are the estimated coefficients. In your case the number of data points $n=710$. To obtain the estimated parameters use the following matrix formula:
$$\hat b= (\mathbf{X}^\mathsf{T} \mathbf{X})^{-1}\; \mathbf{X}^\mathsf{T} \textbf y$$
$\mathbf{X}^\mathsf{T}$ is the transpose of $\mathbf{X}$ and $(\mathbf{X}^\mathsf{T} \mathbf{X})^{-1}$ is the inverse of $\mathbf{X}^\mathsf{T} \mathbf{X}$.
Comprehensible? Matrix calculation can be well done with Excel. A difficulty maybe is the large number of data points.
For instance, the first row of $\mathbf X$ is $\mathbf X= \begin{bmatrix} 1 & 916.405 & 916.405^2 & 916.405^3 & 916.405^4 &916.405^5& 916.405^6 \\ \end{bmatrix}$