Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.