KNOWLEDGEBASE - ARTICLE #1870

NORMSINV() as a user-defined Prism function

Last modified July 22, 2013

The Excel function NORMSINV() takes in a fraction and tells you how many standard deviations from the mean you must be on a Gaussian distribution for a tail to contain that fraction of the entire area. In other words, NORMSDIST( c ) is the probability that a value selected from a standard normal distribution (mean = 0; SD = 1.0) is less than c, or P( Z < c ).

Remember the magic number 1.96. If you go 1.96 standard deviations from the mean in both directions, you will include 95% of a Gaussian population. Thus each tail beyond 1.96 standard deviations encloses 2.5% of the entire area. So NORMSDIST(1.96) = 0.025.  

NORMSDIST(0) = 0.5 because half of the Gaussian distribution is symmetrical around 0. Half of the distribution is on the tail where Y is less than zero.  

This function is also called the "normal inverse cumulative distribution function" or "inverse of the normal cumulative distribution function" or the "inverse Gaussian distribution". 

Prism does not include such a function in its list of functions you can use when writing user-defined transforms (let us know if you'd find this helpful), but several approximations have been published. The code below is said to be accurate to at least 0.001. Paste that code into a user-defined Y transform in Prism and it should just work.  The source of the algorithm is in the comments.

 

;This code computes Normsinv(Y) within a Prism user-defined transform
 
;Code adapted from:  
;http://fmforums.com/forum/topic/82726-normsinv-probability-function-recreate-from-excel/
 
; The algorithm  came from  M. Abramowitz and I. Stegun, Handbook of Mathematical 
; Functions: with Formulas, Graphs, and Mathematical Tables, Formula 26.2.23. 
; They claim it is accurate to plus or minus 4.5e-4
 
 
q = If (Y < 0.5 , Y , 1 -Y )
t = Sqrt ( Ln( 1 / q^2 ) ) 
 
c0 = 2.515517 
c1 = 0.802853 
c2 = 0.010328 
 
d1 = 1.432788 
d2 = 0.189269 
d3 = 0.001308 
 
c = c0 + c1 * t + c2 * t^2 
d = 1 + d1 * t + d2 * t^2 + d3 * t^3
F= t - c / d
 
Y=If ( Y < 0.5 , -1*F, IF( Y = 0.5, 0 , F ))

Explore the Knowledgebase

Analyze, graph and present your scientific work easily with GraphPad Prism. No coding required.