Skip to content

Instantly share code, notes, and snippets.

Last active October 21, 2018 20:26
What would you like to do?
Determine P value from positive AND negative Z scores

Obtain P value from positive and negative Z scores

My fiancée is working on her master's thesis and her professor recommended a website that gives her P values when she plugs in Z scores.

The website works fine (although it crashes for a while on incorrect input) but it provides no transparency as to the formula used to compute the P value.

More problematic, she had to input hundreds of numbers into it which took forever. I tried to find an Excel formula that could replicate the results of the website predictably so that she could just use that instead.

This formula first checks whether the Z score is a positive number. If it is positive, it will run the NORMSDIST function on it then subtract 1, then multiply the result by two with gives you the result for a two-tailed hypothesis.

# with A1 as the column containing your first Z score value
IF(A1 > 0, (2 × ( 1 − NORMSDIST(A1) )), ( 2 × (NORMSDIST(A1))) )

I have no idea why it's necessary to subtract 1 from all positive values and not from negative values but that's the only thing that provided matching results for positive Z scores. I'm just as confused as to why it's not necessary for negative values, but it works, reliably. Please comment if you find a flaw in the function, or if it can be improved at all.

Copy link

In order to bypass Excel entirely I've started building a simple Z score to P value tool here:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment