Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

lognormal distribution function

Ok so i can get it to work with a normal distribution but im after a lognormal one

please help, i know ive done a small mistake somewhere but not sure where..

many thanks

version 2007


Post your answer or comment

comments powered by Disqus
Hi.I am trying to recreate a lognormal distribution from a paper I'm reading:http://www.cdc.gov/ncidod/eid/vol10no4/03-0509.htm

The paper says "Lognormal distributions were fitted to these data by maximizing the log-likelihood function. In subsequent modeling, the duration of individual latent periods and infectious periods could then be taken from the fitted lognormal distributions in Figure 1 with means (SD) of 4.3(1.8) and 2.5(1.2) days.Figure 1"

I've been trying to use the lognormdist function in excel to recreate the distribution in the paper but have been unsuccessful, can someone help me figure out how to input the mean and SD into excel so that my curves looks like the ones in figure 1 of the paper?

Thanks!

I am trying to recreate a lognormal distribution from a paper I'm reading:http://www.cdc.gov/ncidod/eid/vol10no4/03-0509.htm

The paper says "Lognormal distributions were fitted to these data by maximizing the log-likelihood function. In subsequent modeling, the duration of individual latent periods and infectious periods could then be taken from the fitted lognormal distributions in Figure 1 with means (SD) of 4.3(1.8) and 2.5(1.2) days.Figure 1"

I've been trying to use the lognormdist function in excel to recreate the distribution in the paper but have been unsuccessful, can someone help me figure out how to input the mean and SD into excel so that my curves looks like the ones in figure 1 of the paper?

Thanks!

Does Excel allow me to plot the empirical cumulative distribution function? And if so, how can I do it? Thanks.

Hi,

I'm trying to do a Monte Carlo simulation which involves randomly selecting a value from a lognormal distribution (with mean-reverting jumps) and performing a set of calculations from this value, then moving on to the next randomly selected value.

My current idea, albeit a rough one, is to first generate a normal distribution, then taking the log of the values to obtain the lognormal distribution. The mean-reverting jumps are then added randomly by replacing some of the generated values of the lognormal distribution. Next, using a loop, I'll take each value of the new distribution to make the necessary calculations.

However, I find the mentioned approach to be inefficient as I'll have at least four loops... first to generate the normal distribution, then to convert into lognormal, third is to add the jumps and lastly, to make the necessary calculations. Are there any better approach, where I can create the same outcome faster? Also, any ideas on creating mean-reverting jumps using VBA would be greatly appreciated.

Thanks.

I don't know anything about the distribution functions, but I have a range of numbers and I need to know each number's rank within the range. I believe that's related to distribution, so if someone could tell me how to go about calculating that (with or without those particular functions), I'd really appreciate it.

Hi,

Wanting to create a list of random lognormal numbers from two min and max numbers, e.g.

Min number: 0.07
Max number: 0.43

I'm able to create a list of random numbers using the following formula:

=Min+RAND()*(Max-Min)

Works great

Tried the following formula for lognormal distribution (courtesy vertex42):

=LOGINV(RAND(),meanlog,sdlog)

My question is how do I go about finding the meanlog and sdlog values using Excel formulas and the above two min and max examples?

Also, for "RAND()" in the LOGINV equation do I use the same formula as in the first example (assuming yes)? i.e.

=LOGINV(Min+RAND()*(Max-Min),meanlog,sdlog)

Apprecaite any help as I'm about to start banging my head against the wall....

Thanks,

Gareth

Hello,

Does anyone know the steps to create a non-cumulative lognormal distribution
in Excel? I just need the shape, i.e. the values are irrelevant.

Please help and thanks in advance

Adrian T

I want to calculate the cumulative distribution function of a return series.
Appreciate anyone's good ideas.

I have been given a price of a stock $142, and a volatility of 14%.

I need to figure out what percentage (by points) of volatility will increase or decrease if the volatility increases by 5%, 25%, 50%, 100%, and 200%. Since this is dealing with stocks, I believe a lognormal distribution needs to be used but I am not for sure. Here is an example of what I need to figure out, and I know excel can do it for me.

Example.

stock at $142 volatility 14%

current volatility is increased by 5% = ? (How many percentage points do I need to add to the current vol to indicate a increase of 5%)

hi all,

help please.

i'm having a difficult time trying to generate a pseudo random lognormal distribution in excel.

to make things worse, i need to generate a distribution that meets the folowing criteria: mean is 50 and 68th percentile is 250.

i'm told it's possible to calculate sigma out of percentile, but wouln't know where to start.

thank you all in advance for support and guidance.

zurich

Hi all,

I recently upgraded to Office 2003. I have a rather complex Excel file that
performs a Monte-Carlo analysis. Each trial invokes several statistical
functions to generate random variables based on exponential, normal, and
lognormal distributions.

I discovered after the upgrade that this workbook runs much slower than it
used to, probably 5x slower or more. Which is posing a problem for me. Also
I am a little concerned that the results might be slightly different with
the new version.

So, is there a list available of all of the statistical functions that have
changed in Office 2003? Are there any known performance issues? This would
help me target where I have to optimize the code.

Thanks,

-- Rev

Hello Everyone,

I have a quick question. I am using Excel to do some statistical modelling and I am wondering if anyone knows if the PERCENTILE function in Excel has the ability to determine the most suitable probability distribution model that best describes the data set before giving an answer. No reference is made to the probability distribution model in the Excel help file on PERCENTILE function. Does anyone here definitively know how the PERCENTILE function works? Does it base the answer on the probabilty distribution function or does it simply order the data set and give the answer based on the rank? Any clarification is welcome.

Hello all. First post here and I'm looking for a little bit of help .

I've started on a project for a Prob & Stat class I have and I'm stuck trying to figure out how to display a lognormal probability distribution on a Histogram.

In the attached .xlsx on sheet "C1 HIST" you can see the random variable seeds, the loginv to get the full numbers (column C), the BIN I used for the histogram, and my attempt at the "lognormaldist" values. On sheet 3 you an see the actual commands I used to generate the numbers. I don't think I'm using the "lognormaldist" properly because the values in Column E of "C1 HIST" keep going up instead of spiking up quickly and then coming back down.

Toward the bottom of this sheet you can see what an add-in called "EasyFitXL" shows the lognormal distribution to be, but I'd like to know how to do it without an add-in.

I also attached the project statement (I'm trying to do the probability distribution part of step 2 of "Progress Report 1". Right now I'm just trying to figure out everything for Random Variable C1, and once that's done, the rest will be easy. You can also see the distribution type, CV, and Mean on the table in the .pdf (seen at the upper right of sheet "C1 HIST").

If you guys have any help/input it's be greatly, greatly appreciated.

Thanks so much!

Is anyone familiar with the Kaplan-Meier estimator, and if there is a way I can get excel to draw a traditional survivior curve? The charts on this page are the style that I would like to produce. However, these appear to have been produced using an add-on called XLstat-life.

I am attempting to produce a spreadsheet to analyse various statistical models which are all reliant on producing a survivor curve in this style. So far I have the code to get most of the distribution functions. Any help anyone could offer me would be gratefully received as I have spent days trying and can not find a way of doing this.

Hi,

i am dealing with random number generator and the link below gives information to return random numbers from Normal Distribution but i would like to know how to return random numbers from LogNormal Distribution, Weibull Distribution and Gamma Distribution. Is anyone familiar with this or would be able to give me some advise?

http://www.anthony-vba.kefra.com/vba/vba4.htm

Thank you.

How do I use the Student, x^2 and Gaus (normal) distributions?

For example in student distribution for n samples and error probability of 5% I'm going to Student's table I'm going in n-1 row and t=95 and find the corresponding number. But in Excel I don't know how it works.

I'm a registered user of Excel 2007 (part of Office 2007).

I want to point out an error in an Excel function (the statistical Poisson distribution function), and request it be corrected with some update.

How do I do that?
When I try to contact technical support I'm asked what payment method I want to use.
Microsoft is making it very difficult to be a good Samaritan.

Thanks. Neal Carron

I am trying to use the excel function BINOMDIST to calculate what the probabilities would be for each correct score in a best of 14 darts match where the match stops if a player reaches the required 8 legs.

To work out these probabilities I am using the probabilities for each player to win a single leg when they are throwing first and then multiplying this by the number of possible combinations of this particular score occuring. This part I believe I have worked out correctly. Bear in mind, for Player A to win 8-1 then player B can only win one of the first 8 legs or else Player A would have already won the match 8-0. For any novices to the game of darts each player throws first in alternate legs so we also need to take in to account the player who is throwing first, I intend to identify this by marking either cell A2 or A3 with a 1.

On the attached example I believe I have correctly formulated for Player A to win 8-0 but am struggling with the remaining scores.

Could someone have a look at the example and see if they can point me in the right direction on how to formulate these remaining probabilities.

Thanks,
Adam.

Hi,

I am trying to create a triangular distribution function in Excel VBA with three parameters : a, b, and c. The parameters a and c are the minimum and maximum possible values, and b is the most likely value. The method is :
Calculate d = (b-a)/(c-a)
Generate a random number N between 0 and 1
If N <= d, then a + (c-a)*sqr(dN) is the required random number.
If N > d, then a + (c-a) [1 - sqr((1-d)(1-N)) is the required random number.

Any help will be appreciated

Moroformat

Hi everyone,

I have posted this question on another forum but hoped that I may be able to get a different perspective from other people.

I have been working on a problem that requires being able to calculate the chances of any winnng a series of coin tosses with a weighted coin.

The coin has for arguments sake a 65% / 35% chance of landing heads or tails and to win the game you need to hit your side 9 times.

Can you use excel to calculate things like: If heads takes the lead 1-0, what does that mean the its overall percentage chance of winning? If the score is 7-3 to tails, what are the chances of either side gaining victory? etc, etc.

I thought that there would be a way of setting up a spreadsheet that listed the goal (first to 9, 12, 15 or whatever) and below this the percentage chance of heads and the percentage chance of tails. Then somewhere would be a box to enter the current score (0-0, 3-5, 1-1 or whatever) and beside this the current chance of heads or tails gaining victory.

I've tried setting up something like this but am a bit of a dufus when it comes to knowing how, where and when to use the different functions of excel.

Would anyone be able to give me a hand in how to set up something like this?

Thanks in advance for any help.

Here is the link to the same question on another board http://www.mrexcel.com/forum/showthread.php?t=340808

Can someone help me, give me a link to learn, how to plot the gaussian
normal distribution function in Excel!

Thanks and sorry for my english!

Francesco.

--
Per rispondermi in privato togliere NOSPAM

Is there a function that will find the recurring values within a range and
then count the number of recurrences for those values? For example, if my
range 1,2,1,3,2,1, then 1 = 3 times, 2 = 2 times, and 3 = 1 time. I would
like the results returned so that they can be charted.

I have collected a large amount of data (approx 400 data points) and I need
to represent the statistical date graphically, namely the Normal
Distribution. I understand the concept mathematically but I would like some
direction on how to accomplish this within Excel. (I have to do this in Excel
due to different groups, engineering and non-engineering, need to view the
data and graphs.

Hi

The "skew" function in excel returns the: "Skewness of a distribution of the data set" (this is a quote from the help on skew function)
I, on the other hand, do not have a data set. I have the actual distribution function that I would like to calculate the skewness for.
Is there any way to do this automatically in excel?

Attached is an example that I hope will clarify my question.

Thanks
skewness calculation Q.xlsx


No luck finding an answer? You could always try Google.