lognormal graph

  • Hi,

    I've been struggling with the lognormal function in excel for the past week now.

    There's a particle analysis that I've been trying to replicate in excel, using formulas, instead of probability graph paper, and so far it has been challenging.

    The data stated the following :

    Dvs (mean volume-surface diameter) = 231 um

    Dvs transformed to D50 using Hatch-Choate transformation

    log d50 = log dvs + 1.151 log2(S)

    D50 (mean drop size diameter ) = 264um

    Standard deviation (S) = 1.7

    The d50 diameter was then transformed to d16 diameter by using d16 = d50/S

    d16 = 155 mm

    This represents the drop size diameter equivalent to 0.1587 wt fraction of the dispersion.

    Based on the above definition I believe S = geometric standard deviation, and d50 = geometric mean

    For the excel lognorm.dist formula - the inputs are (x , mean, SD), where the mean = standard deviation of ln (x), and SD = standard deviation of ln (x)

    This is the part where I'm confused as I'm not too sure what inputs should I put in to replicate the above data. Originally, d50 and d16 were plotted on the probability graph paper, and the entire range of droplet size could be predicted.

    I did some experimenting and found that if I substitute mean = ln (264) and SD = ln (1.7), the plot of the data (droplet size vs cumulative weight %) matches what the graph paper shows. But I'm not sure if I am even doing this correctly or it was just luck. substituting mean = ln(264) makes sense to me, but I'm unsure of ln (1.7).

    Appreciate if someone can enlighten me.

    Thank you!

  • Hi
    Assuming you want to calculate the cumulative lognormal distribution, you can use the following.


    The LOGNORM.DIST function uses the following arguments:

    • X (required argument) It is the value at which we wish to evaluate the function.
    • Mean (required argument) It is the mean of In(x).
    • Standard_dev (required argument) It is the standard deviation of In(x).
    • Cumulative (optional argument) It specifies the type of distribution to be used. It can be either TRUE (implies the cumulative distribution function) or FALSE (implies the normal probability density function).

    Sample data (x, mean, SD) shown below.

    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tLND.jpg Views:\t1 Size:\t23.4 KB ID:\t1214996","data-align":"none","data-attachmentid":"1214996","data-size":"full","title":"LND.jpg"}[/ATTACH]

    a few links to follow up with


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!