# Fitting a Gauss curve to lab data

1. May 12, 2012

### sunrah

I have 100 data points that I want to fit in Excel I can do this with NORMDIST no problem but I'm also trying to do it without it to no avail!

I'v tried plotting the data using the Gauss function:

$y(x) = \frac{1}{\sqrt{\pi \sigma^{2}}}e^{\frac{-(x - x_{0})^2}{2\sigma^{2}}}$

where x is a data point, x0 the mean and σ the standard deviation from the mean, but it doesn't go bell-shaped although normdist gives a nice gaussian.

Last edited: May 12, 2012
2. May 12, 2012

### HallsofIvy

Staff Emeritus
If you are trying for an exact fit you won't be able to find it. The "Central Limit Theorem" implies that any large body of data will approximate some Gaussian distribution- and how good the approximation is depends upon the size fo the data sample. "Normdist", I believe, does a "least squares" approximation to the data.

3. May 12, 2012

### sunrah

Hi, I'm not really after an exact fit, concave and reasonably bell-shaped would do. I just want to know if I'm doing it right because I'm getting this View attachment bell1.pdf . I also attach the same data set with normdist View attachment bell2.pdf .

with normdist I used the bins as the x values, when I do this with the gauss function I get a very round U plot.

4. May 12, 2012

### Staff: Mentor

You must have plotted the graph wrong. Your plot doesn't look anything like the funcationality implied by the above equation. What was the syntax of the expression you used in excel?

5. May 12, 2012

### sunrah

EXP(-(A1-$C$1)^2 / (2*$C$2^2))/SQRT(2*PI()*$C$2^2)

where C2 is stdev(A1:A100) and C1 is average(A1:A100)

data in range A1:A100

6. May 12, 2012

### Staff: Mentor

This coding looks OK. But the function in your diagram doesn't seem to match the functionality implied by the coding. Tell me the values of c1 and c2 you used, and I'll try it with my excel.

Chet

7. May 12, 2012

### Dickfore

Fix'd.

8. May 12, 2012

### sunrah

Hi this is quite strange. I got this to work by breaking the formula down into incremental steps, e.g. first calculating A1-$C$1 in one column, squaring it in another etc. and it works! but when I try it all together I get those dodgy graphics - :grumpy:

c1 = 3678 (mean)
c2 = 51.8 (stdev)

yes, sorry I forgot the 2 !

9. May 12, 2012

### Staff: Mentor

Try EXP(-((A1-$C$1)^2 / (2*$C$2^2)))/SQRT(2*PI()*$C$2^2)

There is an extra set of parenthesis here. I don't know why this is necessary in excel, but it seemed to work when I tried it.

Chet

Last edited: May 12, 2012