Anybody into statistics? I have a question for you then! (in General)

As some of you might know I am working on figuring out some spells.

I now have 2 ways of calculating something I believe is the same, and they get a bit different results.

The first forrmula to is:

2 *Standard Deviation
-------------------------
square root of the number of data points


The second formula is one used by excel:


CONFIDENCE(alpha,standard_dev,size)

the calculation behind it i don't know exactly but it's a calculation in which the alpha determines what % of confidence is calculated:

an alpha of 0.05 makes the calculation predict conficence that 95% of the measurement points fall within the average + or - the calculated number.

explanation in numbers:

CONFIDENCE(0.05,2.5,50) equals 0.692951.

In other words, the average equals 30 ± 0.692951. or average = 29,31 - 30.69 with 95% confidence.

I think the formula excel uses would be more accurate, however I am not sure.

Some data to look at:

Average Damage: 156,8
Margin of Error: 5,01
Confidence Level: 4,91
Standard Deviation: 30,69
Amount of measurements: 150

So the Confidence level is smaller, if only a bit, than the mean error.

Shot in the dark guess, because I don't have Excel, but often time sqrt(n-1) is used instead of sqrt(n). Often it gives an unbiased estimator when taking measurements from a sample. Perhaps that explains the small discrepancy.

n-1 is used to calculate Standard Deviation of a sample and n is used to calculate the Standard Deviation of an entire population. Since we are trying to find the Standard Deviation of a sample here n-1 is probably more appropriate.

Also strictly speaking 1.96 should be used not 2 for a 95% confidence interval.

Try comparing the formula 1.96* (SD / sqr (n-1)) to the Excel number.

Try comparing the formula 1.96* (SD / sqr (n-1)) to the Excel number.

new formula: 4,927835085
excel number: 4,91

Bump!

Some information on how Excel calculates it:



CONFIDENCE

Returns the confidence interval for a population mean. The confidence interval is a range on either side of a sample mean. For example, if you order a product through the mail, you can determine, with a particular level of confidence, the earliest and latest the product will arrive.

Syntax

CONFIDENCE(alpha,standard_dev,size)

Alpha is the significance level used to compute the confidence level. The confidence level equals 100*(1 - alpha)%, or in other words, an alpha of 0.05 indicates a 95 percent confidence level.

Standard_dev is the population standard deviation for the data range and is assumed to be known.

Size is the sample size.


Remarks:
If any argument is nonnumeric, CONFIDENCE returns the #VALUE! error value.
If alpha < 0 or alpha > 1, CONFIDENCE returns the #NUM! error value.
If standard_dev < 0, CONFIDENCE returns the #NUM! error value.
If size is not an integer, it is truncated.
If size < 1, CONFIDENCE returns the #NUM! error value.


If we assume alpha equals 0.05, we need to calculate the area under the standard normal curve that equals (1 - alpha), or 95 percent. This value is 1.96. The confidence interval is therefore:

X(average) +- 1.96 (sigma / root of n)

Example

Suppose we observe that, in our sample of 50 commuters, the average length of travel to work is 30 minutes with a population standard deviation of 2.5. We can be 95 percent confident that the population mean is in the interval:

30 +- 1.96 (2.5 / root of 50)

or:

CONFIDENCE(0.05,2.5,50) equals 0.692951. In other words, the average length of travel to work equals 30 +- 0.692951 minutes, or 29.3 to 30.7 minutes.



So I think Excel uses the right calculation, except for the N -1 part which for measurements over 150 shouldn't make much of a difference anyway.