This is possible to do in Excel. Even better, your problem is linear,
which means that you can use the standard Excel Solver to do the work -
no need to upgrade. BUT REMEMBER, if you try a different equation, your
problem might become non-linear, in which case you are not guaranteed
the most optimal solution. Depending on what settings you choose in the
Solver, it will converge on some local optimum, which may not be the
global optimum.
With all the legal stuff out of the way, proceed as follows:
A B C D E F Delta
X Y Z Zcalculated m1 m2
1 5 1 =$e$2*A2+$f$2*b2 1 1
=abs(d2-c2)
2 4 1 =$e$2*A3+$f$2*b3
=abs(d3-c3)
Then do a =sum(G2:G3), and using the solver, minimise the cell, with
cells E2:F2 as variables.
So, what are we doing? We are specifying random starting points for m1
and m2, and then trying to get the best least-squares fit.
To check the fit, it is always a good idea to plot on an x-y graph Z
versus Z-calculated. Fit a straight-line curve to the data, and display
the r2 fit on the graph. FOR YOUR PARTICULAR data SET, you need to
decide what is a suitably acceptable r2 value. Secondly, you need to
ensure that your prediction actually represents reality. So draw a line
from the bottom left corner to the top right hand corner of the graph.
Your fitted line should overlay this line, or at least be close to it,
or at least have the same slop. When the slope is substantially
different, you may want to reconsider your equation, because your
prediction will then only be accurate in a small range (where the two
lines cross, or are closest together.