How to find curve equation from data?

Question

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

Answer 1

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.