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

[JBlackhole]

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

[RichardJ]

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)?

[JBlackhole]

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

[RichardJ · Accepted solution]

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

[JBlackhole]

Thanks for the suggestion. It may do for the moment. Looks like I convince myself there was "quick" way of getting such probabilities. Maybe I was confused with another function!