a probability function
I'm looking for an excel 2007 statistic function. I have a list of values and am looking for a function that will return the probability that another value will fall between a specified lower and upper limit. The values are not integers and there are some gaps in the distribution. Can you help?
Last edited by Xorin; 07-16-2010 at 08:41 AM.
Can you upload example?
"Relax. What is mind? No matter. What is matter? Never mind!"
Attached is some of the raw data. So far I've tried using a NORMDIST from integers 1 to 240 (ignoring the outlier) using the mean and std dev from the data (including outlier), and then graphed it and got the trendline formula for a 6th power polynomial for most accuracy. I was going to use this formula with some calculus to get what I wanted, but taking the integral from 0 to 240 only got me .71ish so something went wrong along the line, probably the function.
Looks more lognormal-ish to me.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
To put it into some sort of context these values are time latencies for a communication system that's being tested.
So any ideas anyone?
I tried the LOGNORMDIST but that doesn't look right at all. From the data you can see the probability should be favored on the lower side but not with the highest probability closer to 0.
Last edited by Xorin; 07-13-2010 at 08:44 AM.
A lognormal distribution need not have the highest density at zero. Consistent with an LN distribution, your data is asymmetric and has no values < 0.
Poisson is another possibility.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
I've tried both options. Logarithmic doesn't vary vertically enough, staying between 0.40 and 0.42 the entire time. Poisson reaches 1.0 much too soon, saying that it is 100% probable the time will be less than a minute, which isn't true. Normal looks the best so far... it's just that since these distributions are based off the mean and/or std dev, instead of the data set itself, the inaccuracy is high.
Is there an altogether different way of doing this besides distribution curves?
What you you know about the underlying phenomenon?Is there an altogether different way of doing this besides distribution curves?
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
The data basically represents the time delay between two stations using a comm system thats under review. The smaller values are regular delays, while the larger ones include switching from one medium to another under failure of the most efficient. There's a certain threshhold of acceptance for the latencies, and I've used percentile and percent rank previously. Now I'm dabbing in the probability for even more data.
Then it's the sum of two distributions, right?
I think you need to parse them into two groups and model each separately.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Well, it's an evaluation of the system as a whole. The media switch will occur at approximately the rate shown by the data, as the more efficient system relies on LOS (line of sight). I can't separate the data because they're not independent of each other and an overall assessment must be made.
Separating the data doesn't limit your ability to analyze it as a whole. On the contrary, I expect you can't model the system without doing so.
The primary system has some distribution arising from its nature, and so does the backup.
Simplistically, the PDF of the system is (1-P)*PDF1 + P*PDF2, where P is the probability of failure of the primary system.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
I'm sorry, I don't know what PDF means besides the file extension haha. Could you explain some more please?
Probability Density Function.
If you use the Frequency function to plot the number of samples in a given range, you have a Probability Mass Function, the discrete analog of the PDF.
Last edited by shg; 07-15-2010 at 10:51 AM.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks