MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### P value in excel

• Follow

```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
```
 0
Reply Utf 2/18/2010 9:52:01 AM

```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: &#963;2 )

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

=VAR(A2:A11)

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180334

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0
Reply joel 2/18/2010 11:31:04 AM

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

"Boss" <Boss@discussions.microsoft.com> wrote in message
news:FC2247AF-195C-430B-BE21-244EFEE56F57@microsoft.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

```
 0
Reply Mike 2/18/2010 9:31:16 PM

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

"Boss" wrote:

> 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
```
 0
Reply Utf 2/19/2010 12:52:01 PM

3 Replies
2033 Views

(page loaded in 0.079 seconds)

8/1/2012 6:18:26 PM

 Reply: