Okay, I have a series of numbers like the table below:
Value 1 Value 2 Value 3 Value 4 Value 5 Value 6
Mean St
Dev ~% Prob
Series A 67.09 64.31 70.47 64.43 54.73 64.21 5.86
4.66%
Series B 68.60 59.70 62.27 74.55 70.14 67.05 6.02
6.17%
Series C 66.28 68.31 62.86 72.61 66.58 67.33 3.56
9.60%
Series D 61.16 67.95 71.56 68.33 75.88 68.98 5.41
7.36%
Series E 62.02 68.27 68.97 68.25 68.81 67.27 2.95
10.63%
Series F 66.44 71.39 67.27 69.69 63.56 67.67 3.02
11.40%
Series G 65.52 66.27 68.29 70.05 65.72 67.17 1.95
11.19%
Series H 65.99 67.85 66.98 70.59 69.76 68.24 1.91
17.81%
Series I 64.67 69.30 66.30 68.63 66.91 67.16 1.85
10.98%
Series J 62.41 69.51 66.91 67.38 68.88 67.02 2.79
10.20%
69.31296719 1.00000
What I want to determine is the probability (based on the mean and
standard deviation) for each series that the next value will be the
highest value in the empty column. For example what is the probability
that the next value in Series 1 will be the highest value in column
"Value 6". I think the answer is roughly 4.66%
I can do a rough approximation using normdist on some dummy variable
and the using solver have the sum of the normdist probabilities sum to
1. Thats how I generated the numbers in the last column. Alternatively
I can do Monte Carlo simulations, but thats time consuming. So I'm
looking for a more elegant and precise solution using the existing
functions in Excel!
I believe the general equation has the form below:
Prob{t(min\A) < x} = Prob{min{t(B), ..., t(J)} < x} = Prob{not all
{t(B), ..., t(J)} > x} = 1 - Prob{all {t(B), ..., t(J)} > x} =
1−∏k∈(S∖A)[1−Φk(x)]
where Φk is the Gaussian CDF for t(k).
Although the above equation is for the probabilities for the lowest
value, I'm guessing substituting "max" for "min" and "<" for ">" will
get the right equation. The question then is, can you solve the
equation in excel? And how difficult is it?
Thank you in advance for any assistance you can provide.
Michael Hayes
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
