Stata equivalent of MS Excel's -NORM.INV-

[Tim Evans]

Hi all,

I'm using Stata 14 on Windows and am trying to recreate some work that has been done in Excel in Stata as I need to automate the process. One of the formulas that is used in Excel is '=NORM.INV()' which requires as input a probability, a mean and a sd. I have all of these components in my Stata data file, but I'm unsure which function, if any, I can use in Stata or whether I can break it down and do this calculation by hand in Stata - for which I'm not familiar with how this would be constructed.

I did find something similar in this post : https://www.statalist.org/forums/for...-norm-dist-cdf where Clyde Schechter made a helpful suggestion for NORM.DIST and wondered if there was a workaround for NORM.INV.

Thanks in advance.

Tim

[Mike Lacy]

A good strategy in Stata is to use -search- with keywords connected to your topic, which will often (not always) lead where you want to go. In this case, -search normal inverse- will lead you to invnormal(). This function yields a z value as a function of your chosen cumulative probability, from which you can calculate any raw score you like using your mean and sd. More broadly, -help functions- will give you a sense of the possibilities.

[Tim Evans]

Thanks for the reply Mike. I've resorted to the Statalist because I have exhausted the -search- with keywords which did lead me to invnormal(), but I'm struggling to understand how I apply this to the data I have. At the moment I am trying to convert the following excel formula into Stata:

=EXP(NORM.INV(0.34,-6.837,0.228))/(1+EXP(NORM.INV(0.34,-6.837,0.228)))*20000

Where 0.34 is the probability, -6.837 is the mean and 0.228 is the sd - the mean and sd are on the log scale. The answer is 19.5
When I try this by building things step by step

display 0.340+invnormal(-6.837)*0.228

Stata returns a blank line because -invnormal- does not like a value that falls outside 0 and 1.

Any help appreciated.
Thanks Tim

[Mike Lacy]

OK, no problem. You're dealing with interpreting the -help- (didn't know this--many people don't seem to know help exists or are not used to trying it). Interpreting the help can definitely be an issue, though. The thing here is that invnormal() wants a probability p as its argument, so it complains when you give it a value outside the range of 0/1. (The help assumed--wrongly in my view--that the reader will automatically know that it is using "p" to denote a probability.) Anyway, "Inverse" here means "give it a cumulative probability, and it will return a standard normal value." You'd want:

// raw score = mean + z * sd
display -6.837 + invnormal(0.34) * 0.228

[Tim Evans]

Thanks Mike for your help in solving this.

In answer to my original problem, this Stata code

display exp(-6.837 + invnormal(0.34) * 0.228)/1+exp(-6.837 + invnormal(0.34) * 0.228)*20000

is exactly the same as this formula in Excel:

=EXP(NORM.INV(0.34,-6.837,0.228))/(1+EXP(NORM.INV(0.34,-6.837,0.228)))*20000

Both give an answer of 19.5