I have some numerical data tables, with a column of calculated results based on three inputs in each row. How can I find the formula to apply to the inputs and recalculate the same result ?
the data series is formatted as such (excel file attached):
data1, data2, data3 : result
(there are 330 rows in the tables, hence 990 inputs and 330 results)
examples from the actual data serie :
- 13, 12, 11 : 857
- 12, 11, 1 : 771
- 2, 9, 6 : 91
- 1, 3, 2 : 0
Excel File : DataSerie
As was mentioned in the comments, without a specific form of function, there is no way to know in general. However, based on your comment, I will write what to do in case it is an affine function.
Let $x_i,y_i,z_i$ be the values from input columns $1,2,3$ on row $i$ in your dataset. Let $v_i$ be the value in the calculated columns. We speculate that there exist numbers $a,b,c,d$ such that $v_i=ax_i+by_i+cz_i+d$ for each row $i$. Choosing the first four rows of data (but any four will do, if our assumption that it is an affine function is correct), we create a matrix equation $$\begin{pmatrix} x_1 & y_1 & z_1 & 1 \\ x_2 & y_2 & z_2 & 1 \\ x_3 & y_3 & z_3 & 1 \\ x_4 & y_4 & z_4 & 1 \end{pmatrix}\begin{pmatrix}a\\b\\c\\d\end{pmatrix}=\begin{pmatrix}v_1\\v_2\\v_3\\v_4\end{pmatrix}.$$
With the four rows you gave, we have
$$\begin{pmatrix} 13 & 12 & 11 & 1 \\ 12 & 11 & 1 & 1 \\ 2 & 9 & 6 & 1 \\ 1 & 3 & 2 & 1\end{pmatrix}\begin{pmatrix}a\\b\\c\\d\end{pmatrix}=\begin{pmatrix}857\\771\\91\\0\end{pmatrix}.$$
Letting a computer algebra system calculate $M^{-1}\vec{v}$ is one way. In fact, you can google "matrix equation solver" and find free sites into which you can easily type this in.
If the system has no solutions, that means it isn't an affine function. If the system has multiple solutions, you can start over with four new rows.