Set up four parallel columns in the spreadsheet:
* X has the x-values.
* Y has the y-values.
* Fit computes the Gaussian values (based on the x-values and three parameters).
* Residual is the difference between the y-values and the fits.
In order to compute the fit, you need to create three cells holding the three gaussian parameters. The formula for the fit must be identical to that used by the other software so you can compare your results with its. The example below names the three parameters kappa0, kappa1, and kappa2--just as in the documentation. The formula in the second row, where the x-value is in cell A2, is
=Kappa0 * EXP(-1*(A2 - Kappa1)^2 / Kappa2)
It is copied down to all the other rows.
This is enough to check the software's results simply by plugging in its reported values of kappa0, kappa1, and kappa2. To see whether they are correct, compute the sum of squared residuals (SSR). A formula for this uses the SUMSQ function ("=SUMSQ(D2:D32)" in the example). If you think a better combination of the parameters will work, plug in that new combination and see whether SSR decreases: if it goes down, the new values are better.
Spreadsheet
You can have this automated for you using Excel's "Solver" tool. Specify that you want to minimize the SSR by varying the three kappas. Start with the solution given by the other software. Solver will try systematically to improve its solution.
Solver dialog
The same method--suitably adapted--works well for least squares, maximum likelihood, and other optimization procedures in statistics, provided the objective function is well-behaved (i.e., differentiable and convex) and you can obtain an excellent starting value. Otherwise, Solver is perfectly capable of reporting inferior solutions or failing altogether: it is wise not to use it as the sole method to solve a problem.
share|improve this answer
answered Jun 4 '11 at 14:50
whuber♦
©
