10
$\begingroup$

How do I find the formula when I only know some data points ?
Usually I just use the Trendline option for diagrams in Excel, but this one eludes me.
I expect it to be something like : Ax^2 + or - Bx + or - C.

Sample data:

X   Y 1   4 2   8 3   13 4   18 5   24 6   30 7   37 8   44 9   51 10  60 11  68 12  78 13  88 14  99 15  110 16  122 17  136 18  150 19  166 20  180 21  197 22  216 23  235 24  255 25  277 26  300 27  325 28  351 29  378 30  408 

1 Answers 1

5

From plotting you data (Wolfram|Alpha link), it does not look linear. So it better be fit by a polynomial. I assume you want to fit the data:

X   Y 1   4 2   8 3   13 4   18 5   24 .. 

using a quadratic polynomial $y = ax^2 + bx + c.$ If so, then put your data in a matrix form (note that $x^0, x^1, x^2, y$ below are not actually in the matrix. They're just comments for your understanding): $ \begin{pmatrix} \color{red}{x^0} & {\color{red} x} & \color{red}{x^2} \\ 1 & 1 & 1 \\ 1 & 2 & 4 \\ 1 & 3 & 9 \\ & \ldots & \end{pmatrix} \begin{pmatrix} c \\ b \\ a \end{pmatrix} = \begin{pmatrix} {\color{red} y} \\ 4 \\ 8 \\ 13 \\ \ldots \end{pmatrix} \tag{1} $

Now, equation $(1)$ is really of the following form: $ Xv = y \tag{2}$ where each row encodes $cx^0 + bx+ax^2 = y$ for a particular pair of $(x,y)$ values. And we're looking for a solution vector $v^{T} = \begin{pmatrix} c & b & a \end{pmatrix}$ that gives the coefficients of that best fitting polynomial $ax^2 + bx+c$.

To solve for $v$, multiply $(2)$ both sides by $X^{T},$ we have $X^{T}Xv= X^{T}y,$ or $ v = (X^{T}X)^{-1} X^{T}y.$

This is a called linear least squares method because best means minimize the squared error. Several software packages can handle that for you. Luckily, Wolfram|Alpha can do.


To repeat for a polynomial of degree, say 4, construct $ \begin{pmatrix} \color{red}{x^0} & {\color{red} x} & \color{red}{x^2} & \color{red}{x^3} & \color{red}{x^4} \\ 1 & 1 & 1 & 1 & 1\\ 1 & 2 & 4 & 8 & 16 \\ 1 & 3 & 9 & 27 & 81 \\ & \ldots & \end{pmatrix} \begin{pmatrix} e \\ d\\ c \\ b \\ a \end{pmatrix} = \begin{pmatrix} {\color{red} y} \\ 4 \\ 8 \\ 13 \\ \ldots \end{pmatrix} \tag{1} $ and solve for $v$, this should give you the parameters $a,b,c,d,e$ s.t. $ax^4+bx^3+cx^2+dx+e$ best fits your data.

  • 0
    In case you're looking for **the best integer polynomial that fits your data**, then I advice you to post a new question here, and seek more answers from the community.2012-03-18