P value in excel

B

Boss

Hi,

Is there a method/code to find out the p value in excel. I have 30 or more
data points and i wish to find out is the data normal or not based on the p
value.

Same can be done in Minitab, please help me doing same in excel.

Thansk!
Gaurav
 
J

joel

Read the wikpedia webpage below

'Normal distribution - Wikipedia, the free encyclopedia
(http://en.wikipedia.org/wiki/Normal_distribution)

Read sections
1) Standardizing normal random variables
2) Moments
3) And at the top of the webpage : (variance: σ2 )


The p value is based on sigma square as shown in the table i th
Moments Section above. depending on the momentum you want (p value) i
is simply a function of sigma. You need to calculate sigma. Again
sigma square is the "variance" which you can calculate with functions i
excel.

=VAR(A2:A11)
 
M

Mike Middleton

Gaurav or Boss -

One approach is to use a chi-square goodness-of-fit test.

There's no built-in wizard for goodness-of-fit in Excel, so you have to set
it up yourself and then use Excel's CHIDIST worksheet function to get the
p-value.

For examples, use Google or your favorite search engine to look for "excel
goodness-of-fit test" (without the quotes).

- Mike
http://www.MikeMiddleton.com



Hi,

Is there a method/code to find out the p value in excel. I have 30 or more
data points and i wish to find out is the data normal or not based on the p
value.

Same can be done in Minitab, please help me doing same in excel.

Thansk!
Gaurav
 
D

Dave Ramage

Gaurav,

The Anderson-Darling goodness of fit test is most commonly used to determine
if a sample of data fits a Normal distribution (or more accurately, it will
tell you if it is unlikely that it does not!). This array formula will
calculate the Anderson-Darling goodness of fit result (A^2) for a normal
distribution for a sample of data in named range "AD_Data":

=-ROWS(AD_Data)-SUM((ROW(OFFSET($A$1,0,0,ROWS(AD_Data),1))*2-1)*(LN(NORMDIST(SMALL(AD_Data,ROW(OFFSET($A$1,0,0,ROWS(AD_Data),1))),AVERAGE(AD_Data),STDEV(AD_Data),TRUE))+LN(1-NORMDIST(LARGE(AD_Data,ROW(OFFSET($A$1,0,0,ROWS(AD_Data),1))),AVERAGE(AD_Data),STDEV(AD_Data),TRUE))))/ROWS(AD_Data)

This is an array formula, so hold Ctrl+Shift as you enter it.

This formula gives the raw test result. You will most likely need to adjust
the result to compensate for sample size. You then need to compare it with
the critical test value for the alpha/power that you require. Different
sample size adjustments and critical values are published. More details here:
http://www.itl.nist.gov/div898/handbook/eda/section3/eda35e.htm

It is possible to obtain a p-value from the test result, but I have never
managed to find how this is done (not just a chisqr test etc.). Pehapse
someone else can assist here...

Cheers,
Dave
 
Top