Skip to main content
J
JBlackhole16-Pearl
16-Pearl
February 27, 2015
Solved

Getting pnorm() for 1s,2s and 3s only

  • February 27, 2015
  • 4 replies
  • 2427 views

To all

I am trying to do a sanity check against a xls doc set-up by a colleague. It uses the xls function NORMDIST()

The aim is to get the cum. Distribution for 1s, 2s and 3s (s=sigma) for a mean=0 and std dev. = 1

The xls was set up as follows

Sigmavalue=1 a=2*NORMDIST(sigmavalue,0,1)-1

Sigmavalue=2 b=2*NORMDIST(sigmavalue,0,1)-(1+a)

Sigmavalue=3 c=1-(a+b)

I believe the mcad function pnorm() does the same but I can’t quite figure out how to get the pnorm value for 1s, 2s and 3s without
setting up the same equations (see below). I f I do I get the same answers but I think there is a quicker way of doing it

a=2*pnorm(1,0,1)-1

b=2*pnorm(2,0,1)-1-a

c= 1-(a+b)

Thanks

Regards

JXB

Best answer by RichardJ

Then I guess I would create a function (which of course could also be done in Excel, but only by resorting to VBA)

4 replies

R
RichardJ19-Tanzanite
19-Tanzanite
February 27, 2015

It's not clear to me what you are trying to calculate. 2*pnorm(1,0,1)-1 is the total probability between -1 sigma and 1 sigma. 2*pnorm(2,0,1)-1 is the total probability between -2 sigma and 2 sigma. 2*pnorm(3,0,1)-1 is the total probability between -3 sigma and 3 sigma. So what you calculate as a is the total probability between -1 sigma and 1 sigma. What you calculate as b is the total probability between -1 sigma and -2 sigma plus the total probability between 1 sigma and 2 sigma. Is that what you want? What you calculate as c has nothing to do with 3 sigma, because the cumulative probability to 3 sigma is not 1. Perhaps you want 2*pnorm(3,0,1)-(a+b)?

J
JBlackhole16-PearlAuthor
16-Pearl
February 27, 2015

HI Richard

Apologies for the lack of clarity in my 1st email. I am after the probability of a "sigma range"

a = total probability between 0 sigma and 1 sigma (x2)

b = total probability between 1 sigma and 2 sigma (x2)

c = total probability between 2 sigma and 3 sigma (x2) (or hsoudl it be bet. 2 & infinite?)


This is done so that a value (stress) is multiplied by the probability (at a later stage). Mind you thinking about it I am not sure why this value are not "hard coded". These probability are fixed value after all (for mean=0,st dev=1), are they not? p(1) =0.68269 ; p(2)=.27181; p(3) = 0.04550

JXB

R
RichardJ19-TanzaniteAnswer
19-Tanzanite
February 27, 2015

Then I guess I would create a function (which of course could also be done in Excel, but only by resorting to VBA)

Terms & ConditionsAccessibility statement