Gaussian Fit

[Harif]

Hi.
How do I taking array of data points and doing to them a gaussian fit.
the result I need is a graph & the width of the graph at half height.

tnx.

[MrShorty]

As with a lot of questions like this, I find it much easier if I understand the math behind the problem before trying to program into Excel (or other programming language). My first suggestion would be to review the Gaussian function and its properties. Wikipedia and Wolfram both seem to have decent articles describing it.

As for the regression, there are basically two approaches to regression in Excel:

1) For a function that is "linear" (in the linear algebra sense, so it is more than just straight lines), you can use the LINEST function. Wikipedia indicates that the Gaussian function is basically a quadratic in log. This suggests that you can rearrange the Gaussian function so it is in a form like ln(y)=A+Bx+Cx^2 where A, B, and C are functions of your Gaussian parameters. Once you have the function in a "linear" form, you can use the LINEST function to generate A, B, and C, then find a, b, and c for the Gaussian function

2) For cases where the function cannot be "linearized," or when it is too much algebra to bother linearizing the function, or when you want to use a different objective function other than the standard least squares OF, you can use Solver to obtain the coefficients. In your spreadsheet, build a table with the known y's and known x's. Guess at the coefficients, and create a column in the table that will calculate y at each point (using those assumed parameters). Then calculate your desired objective function (=sumxmy2() works for a least squares OF). Then call Solver and tell it to minimize the OF by changing the parameters. If you've given it reasonable initial guesses for the parameters and set it up right, it should find your best fit parameters.