Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X
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
Solved! Go to Solution.
Then I guess I would create a function (which of course could also be done in Excel, but only by resorting to VBA)
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)?
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
Then I guess I would create a function (which of course could also be done in Excel, but only by resorting to VBA)
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!