Lognormal Distribution being applied to IQR

bump~

I do not recall using the LOGNORMDIST function but this is how I would start to go about this problem.

The InterQuartile Range should be a measure of statistical dispersion in a normal distribution and involves finding the middle fifty (%).

It appears that it can still be calculated for the lognormal, but what does it mean, what is the point, and what are you getting at?

Here is an example:
Copy These Doown The Column
Column D can be plotted on the X axis vs the PDF in Column B on the Y axis.
What is Column D plotted against Column C the cumulative?
This will approximate the integral which can give the IQR.
Set bins to a larger number, 100 for instance.
Then find the closest bin numbers between 0.25 and 0.75 and take difference for the middle fifty.

Your 239 would not be part of the middle fifty.
How can you get a middle fifty unless you have bin numbers associated with your data?

JackBean

To answer your first set of questions:
It appears that it can still be calculated for the lognormal, but what does it mean, what is the point, and what are you getting at?

the main reason I am asking is, the lognormal distribution is to help remove any outliers - as you solved at the bottom of your response.

I am not familar how the Lognormal Distribution works.
So I am trying to figure out how it gets applied when removing outliers.
I am also trying to follow your details. So the Lognormal Distribution only gets applied to the IQR?
If so, whats the point? Wouldn't that only change the IQR by a little?

I have a few more questions:
What are: STDin are AVGin
And I am not familar with bin numbers. can you explain?

You have been a huge help thus far - thanks

The STDin is the standard deviation.
The AVGin is the mean or average.

I have noticed in the Excel Help for LOGNORMDIST that it is actually the cumulative function.
So what was previously posted is INCORRECT and this will have to be redone.

From wiki:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
In probability theory, a log-normal distribution is a probability distribution of a random variable whose logarithm is normally distributed. If Y is a random variable with a normal distribution, then X = exp(Y) has a log-normal distribution; likewise, if X is log-normally distributed, then Y = log(X) is normally distributed.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

How are you calculating these numbers?
Do you have a range of x over which you are generating the lognormal distribution?
What is your AVG and STD of the normal distribution?

The 10 values that I have are coming from a report.
From what I understand, those are the only numbers that are being used for calculation purposes.

I want to know how the report is working to remove outliers.
Based on what I was provided, they are using 3IQR and if a number is negative, they are applying a lognormal distribution.

I did request from the company how the calculation is working and being applied, but I don't have a response just yet.

I am trying to figure out if applying a Lognormal Distribution even makes sense.

So apparently the negative numbers are not wanted and so the data is transformed into a lognormal distribution.

If you have 10 values you may have trouble selecting a middle fifty. That could leave 2 on the low end, and 3 on the high end thrown out, or vice versa.

The problem with deciding on where the breakpoint for 25 and 75 is, when basing this on sample data, is that the sample of data is so small. These breakpoints can shift with each new set of sample data. If the sample is large, then the data set will more closely approximate the Probability Distribution Function (PDF).

If this data is generated by a process, then how do you know what the PDF is?

Excel has a LOGNORMDIST (this is cumulative), and LOGINV "Returns the inverse of the lognormal cumulative distribution function".

I would prefer to use the PDF (see wikipedia "log-normal distribution"), in Column A below starting in Cell A5.

Keep in mind that the entire PDF is not being modeled, but the # of rows can be extended to model as much of the PDF as is desired.

This creates a log-normal distribution as a histogram.
The PDF in Column A is normalized in Column B.
The Cumulative in Column C means, for bins, that for Row 5, any results between 0 and 0.06355 would go in that bin.
This also generates a random selection of results for the log-normal distribution in Column E from the count of the results in Column F.

X axis is Column D below.
Y axis is Column B (PDF normalized):


Y axis is Column E (randomly generated data):


This shows that the random data more closely matchs the PDF for a large number of samples (5000)
Y axis is Column E (randomly generated data):


A better estimation of where the 0.25 and 0.75 break points should go can be found by setting, in example below, # of Bins to 5000.

0.25 is bracketed here:
0.75 is bracketed here:
The middle fifty is bracketed between about 2.23 to 4.34.

I am not familiar with the term "3IQR".
It seems possible to apply the IQR to most any PDF.
It is difficult to say whether or how the IQR should be applied to your situation.
There is not enough information given.

This is similar to generating random samples based on the normal distribution:
Random Number based on Normal Distribution
http://www.mrexcel.com/forum/showthread.php?t=465705
Also, this has info on:
Named Formulas for chart ranges
Conditional Formatting Formulas

Jackbean - Thank you for your help on this.
I really doubt that company that I am pulling this report from is applying this complex, multistep calculation, just to remove outliers.

Also, thank you for your response for not being familar with 3IQR. I have looked for information about 3IQR and have found very little.