something on excel driving me nuts

[PAULM03]

I've simulated a gaussian distribution on excel using the 'normdist' function like this:
B1 = normdist(A1,0,1,false)

then in B1 to B60 i've got the numbers -3 - +3 in steps of 0.1.

When I plot A1 against B1 it's fine I get a normal curve.

But I'm having a problem with the standard deviation.
It was easy enough to calculate the mean (which should be zero) by doing C(x) = A(x)*B(x) (where x = row index) then the mean is just the average of column C.

But when I come to calculate the standard deviation I get a value of 0.153, when it should be 1. I know I'm overlooking something embarrasingly simple but what is it?

[slapdash]

Originally Posted by PAULM03

I've simulated a gaussian distribution on excel using the 'normdist' function like this:
B1 = normdist(A1,0,1,false)

then in B1 to B60 i've got the numbers -3 - +3 in steps of 0.1.

When I plot A1 against B1 it's fine I get a normal curve.

I think you've muddled A and B here, but I assume that's just a typo?
A goes from -3 to +3, and B is the height of the normal curve?

But I'm having a problem with the standard deviation.
It was easy enough to calculate the mean (which should be zero) by doing C(x) = A(x)*B(x) (where x = row index) then the mean is just the average of column C.

But when I come to calculate the standard deviation I get a value of 0.153, when it should be 1. I know I'm overlooking something embarrasingly simple but what is it?

Could you post how you calculated the standard deviation?

[PAULM03]

Originally Posted by slapdash

I think you've muddled A and B here, but I assume that's just a typo?
A goes from -3 to +3, and B is the height of the normal curve?

Could you post how you calculated the standard deviation?

Sorry, yes it is just a typo.

Standard deviation was calculated 2 ways, 1 using the excel function:

D1 = stdev(column C), gives 0.153

secondly

D(x) = C(x)^2
E(x) = sqrt(sum(D)/60) = 0.153

[slapdash]

Originally Posted by PAULM03

Sorry, yes it is just a typo.

Standard deviation was calculated 2 ways, 1 using the excel function:

D1 = stdev(column C), gives 0.153

secondly

D(x) = C(x)^2
E(x) = sqrt(sum(D)/60) = 0.153

If the mean is zero, the standard deviation is the square root of the expected value of A(x)^2, so this is the average value of A(x)^2, weighted
by the probability distribution in column B, so what you should be
calculating is

sqrt[ (sum(A^2 * B) / sum(B) ]

sum(B) should turn out to be about 10, since you're using steps of 1/10.

[PAULM03]

Cheers