1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Homework Help: Fitting a Gauss curve to lab data

  1. May 12, 2012 #1
    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:

    [itex]y(x) = \frac{1}{\sqrt{\pi \sigma^{2}}}e^{\frac{-(x - x_{0})^2}{2\sigma^{2}}}[/itex]

    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. jcsd
  3. May 12, 2012 #2


    User Avatar
    Science Advisor

    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.
  4. May 12, 2012 #3
    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.
  5. May 12, 2012 #4
    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?
  6. May 12, 2012 #5
    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
  7. May 12, 2012 #6
    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.

  8. May 12, 2012 #7
  9. May 12, 2012 #8
    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 !
  10. May 12, 2012 #9
    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.

    Last edited: May 12, 2012
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook