misbehaving log-normal distribution in Excel

[ajmac]

Hey all,
I've set up a lognormal distribution in Excel, to represent my data, but it is not doing what it should, as its peak is not as high as I know it should be. The following formula I obtained from Wikipedia.

=EXP(-((LN(A5)-$J$5)/$J$6)^2)/A5*$J$5*SQRT(2*PI())

where J5= mean of the raw data &
J6 = standard deviatation

Are there missing parameters that are not accounted for in this formula, and how would I estimate them from my data? I fitted the model to my data in R.

cheers
ajmac

[RobDog888]

Perhaps reading this thread may shed some light?

http://vbforums.com/showthread.php?t=418752

[Rassis]

Ajmac,

Despite the unfavourable report on EXCEL referred by RobDog888 , I attach an EXCEL file which perhaps will bring some light to your problem (I posted it in a previous thread but here it is one more time).

Note that the formula you obtained from WIKIPEDIA is the probability density function and a group of brackets is missing in the denominator of the EXCEL coded function.

It should be: =EXP(-((LN(A5)-$J$5)/$J$6)^2)/(A5*$J$5*SQRT(2*PI()))

Anyway, I don´t think it is of any use to you, if you are looking to estimate the LogNormal parameters.

Hope this helps.

[zaza]

I think I would add that the link shown on the NPL website does refer specifically to the SAMPLE standard deviation (which is not the same as the population sd) and then at larger values. It does not necessarily imply that all Excel stat analysis is bad. Indeed - if you check the built-in formulae against calculations, you'll by and large find no difference at all.

Don't let the rumour-mongers put you off, but be sensible and check that the numbers you're getting are what you think they ought to be. You should be doing this in any maths package you use anyway, Excel or otherwise. I do.

zaza

[Rassis]

I couldn´t agree more with Zaza´s words reagarding EXCEL. I regret having not stressed it before.

Rui