# How to find curve equation from data?

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

-

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.

-
Can Wolfram|Alpha compute a less complex formula if its results can be rounded ? Like to integers (round.down), natural rounding or round.up rules. Using fit int <numbers> did not work. –  Kim Mar 18 '12 at 15:35
In general, the resulting polynomials will not have integer coefficients. The coefficients approximate the actual polynomial. That's why it's called fitting. In our case, W|A returns $3$ different polynomials of degrees $4, 3,$ and $2.$ I guess you want a quadratic polynomial. Just take: $0.423357 x^2 + 0.220974 x + 10.7468$ and round it down as you wish. Rounding down to integers will compromise the accuracy though. –  user2468 Mar 18 '12 at 16:11
Oh.. oh. I guess I now got what you mean. You're looking for the best integer polynomial that fits your data. I think this problem is computationally hard. If I'm not wrong, this problem is akin to either finding the shortest lattice vectors or integer programming. –  user2468 Mar 18 '12 at 16:12
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. –  user2468 Mar 18 '12 at 16:14