PDA

View Full Version : [SOLVED] statistical function using VBA



frade
08-02-2005, 02:23 AM
Hello!

I would like to know if it's possible to use this statistical function"LOGNORMDIST"
directly into my VBA code

(LOGNORMDIST returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev)

Thanks in advance,

Regards,

Fran?ois

Bob Phillips
08-02-2005, 02:39 AM
I have no idea how LOGNORMDIST works, but you could use Evaluate, like


myval = Evaluate("LOGNORMDIST(4,3.5,1.2)")

frade
08-02-2005, 03:23 PM
another idea maybe?

LOGNORMDIST is rather complex, with 2 parameters.

I am able to use "Evaluate" with VBA
For example, my code contains already some instructions like this:



Dim iLastRow As Long
Dim i As Long
For i = 2 To iLastRow
sFormula = "SQRT((N" & i & "))"
Cells(i, "O").Value = Evaluate(sFormula)
Next i




But with LOGNORMDIST, it's different because 2 parameters
have to be used (mean and standard_dev)
These parameters can be calculated separately in two news columns
But how could I integrate theses values into sFormula?

Regards,

Fran?ois

clvestin
08-06-2005, 05:03 AM
This is an Excel application function w/o a VBA equivalent.
How about simply


Application.WorksheetFunction.LogNormDist(whatever)

JonPeltier
08-13-2005, 12:13 PM
Tested and it works:


WorksheetFunction.LogNormDist(x, mean, stdev)