2

I need to implement the below excel function in python and need to create 10k such calculations. How can I implement this logic in scipy ? Can I use lognormal api of scipy to get lognormal inverse ?

B10 - Probability event will happen C10 - Lower bound D10 - Upper bound

=IF(RAND()<B10,LOGNORM.INV(RAND(),(LN(D10)+LN(C10))/2, (LN(D10)-LN(C10))/3.29),0)

Bill Goldberg
  • 1,699
  • 5
  • 26
  • 50

1 Answers1

3

The parameterization of SciPy's lognorm implementation of the log-normal disribution is not the same as that of Excel's LOGNORM functions. Excel uses the mean and standard deviation of the underlying normal distribution. To convert those to the SciPy shape s, location loc and scale scale, use:

s = standard_dev
loc = 0
scale = np.exp(mean)

LOGNORM.INV(p, mean, standard_dev) computes the inverse of the CDF. The corresponding method of SciPy's lognorm object is ppf (the percent-point function; scroll down in the reference page to find the discussion of the percent point function).

So to compute the equivalent of LOGNORM.INV(p, mean, standard_dev), you can use

lognorm.ppf(p, standard_dev, loc=0, scale=np.exp(mean))

See Calculate Scipy LOGNORM.CDF() and get the same answer as MS Excel LOGNORM.DIST for a similar question.

Warren Weckesser
  • 110,654
  • 19
  • 194
  • 214