Fitting a Gauss curve to lab data

[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, x₀ the mean and σ the standard deviation from the mean, but it doesn't go bell-shaped although normdist gives a nice gaussian.

 

[HallsofIvy]

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.

 

[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.

 

[Chestermiller]

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, x₀ the mean and σ the standard deviation from the mean, but it doesn't go bell-shaped although normdist gives a nice gaussian.

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?

 

[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

 

[Chestermiller]

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

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

 

[Dickfore]

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{\mathbf{2} \pi \sigma^{2}}}e^{\frac{-(x - x_{0})^2}{2\sigma^{2}}}$

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

Fix'd.

 

[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 -

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

yes, sorry I forgot the 2 !

 

[Chestermiller]

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