billion combinations of probabilities

C

catatonia

New Member
Joined
Apr 29, 2011
Messages
1
I have the following problem:
In the shown table bwlow you will see 9 competitors. Every competitor can score 0,1,2...or 9 points with e respective probability.
Scoring between competitors is independant.
What I am looking for is the probability of the total score. The total score is the sum of the individual scores of all 9 competitors.

For example, the probability of total score 0 is equal to the product of every competitor's probability of scoring 0 points. Total score 1 is the sum of the products of one competitor scoring 1 point and the rest scoring 0 points; then for total scores 5,6,...81 it gets very complicated to me.

<TABLE style="WIDTH: 537pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=716 border=0><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 57pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=76 height=20></TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 480pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=640 colSpan=10>points prob</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>competitor</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>7</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>8</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">70%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">15%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>B</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">45%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">30%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>C</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">60%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">20%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>D</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">35%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">25%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">15%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">7%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>E</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">50%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">25%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>F</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">52%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">25%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>G</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">50%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">25%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>H</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">66%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">20%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>I</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">80%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">4%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1%</TD></TR></TBODY></TABLE>
My questions are:
1. Can anyone solve that only in excel without VBA coding? Because later I assign different probabilities and need fast calculations of the total score. If the answer is not, it leads to Question 2.
2. Can anyone solve that using VBA coding and how fast/slow would be to execute the macro every time I change a probability?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hi catatonia,

I´m not sure how is obtained the probability for every Total score. I understood for Total Score = 0, and I'm not
clear for Total Score=1, 2, 3...9.

Please show what would be the probability for every Score Column, depending of which students are involved.
If you show for every case which cells are multiplied to obtained respective probability would be better.

So far, for what I could understand regarding how calculations are done I get the following fomulas. Please try
and tell if the results are in the correct way.
Excel Workbook
ABCDEFGHIJK
1points prob
2competitor0123456789
3A70%15%5%2%2%2%1%1%1%1%
4B45%30%10%5%2%2%2%2%1%1%
5C60%20%5%5%2%2%2%2%1%1%
6D35%25%15%10%7%2%2%2%1%1%
7E50%25%10%5%2%2%2%2%1%1%
8F52%25%12%5%1%1%1%1%1%1%
9G50%25%10%5%2%2%2%2%1%1%
10H66%20%5%2%2%1%1%1%1%1%
11I80%8%4%2%1%1%1%1%1%1%
12Probability0.4541%0.3243%0.0917%
13DB
14H
15Total Score=0Total Score=1 Introduce one competitorTotal Score=2 Introduce 1 or 2 competitors
...
Cell Formulas
RangeFormula
B12=PRODUCT($B$3:$B$11)
C12=PRODUCT($B$3:$B$11)*LOOKUP($C$13,$A$3:$A$11,$C$3:$C$11)/(LOOKUP($C$13,$A$3:$A$11,$B$3:$B$11))
D12=IF(AND(D13<>"",D14<>""),PRODUCT($B$3:$B$11)*(LOOKUP($D$13,$A$3:$A$11,C$3:C$11)/(LOOKUP($D$13,$A$3:$A$11,$B$3:$B$11)))*(LOOKUP($D$14,$A$3:$A$11,C$3:C$11)/(LOOKUP($D$14,$A$3:$A$11,$B$3:$B$11))),PRODUCT($B$3:$B$11)*(LOOKUP($D$13,$A$3:$A$11,D$3:D$11)/(LOOKUP($D$13,$A$3:$A$11,$B$3:$B$11))))
PS. If the current results are correct, I think is a job for a VBA macro :biggrin:, because formulas for E12 to K12 would grow enormously:laugh:

Regards.
 
Upvote 0
Starting with a smaller data set, this is an approach that could be used if the problem was only 2 competitors who could independently score 0,1, or 2 points each.

First list each combination and the probability of each part of that combination.

Excel Workbook
ABCDEFG
1Table012
2A70.0%15.0%15.0%
3B45.0%30.0%25.0%
4C60.0%20.0%20.0%
5
6
7All CombinationsABSUMAB
800070.0%45.0%
901170.0%30.0%
1002270.0%25.0%
1110115.0%45.0%
1211215.0%30.0%
1312315.0%25.0%
1420215.0%45.0%
1521315.0%30.0%
1622415.0%25.0%
Small Data Set
Excel 2007
Cell Formulas
RangeFormula
F8=INDEX($C$2:$T$4,1,B8+1)
G8=INDEX($C$2:$T$4,2,C8+1)
D8=SUM(B8:C8)



Then sum the probabilities for each total:
Excel Workbook
IJKLMNO
701234
831.5%
921.0%
1017.5%
116.8%
124.5%
133.8%
146.8%
154.5%
163.8%
17
18
19Probabilities of01234ALL
20Each Total31.5%27.8%28.8%8.3%3.8%100.0%
Small Data Set
Excel 2007
Cell Formulas
RangeFormula
J8=IF($D8=J$7,$F8*$G8,"")

The problem with scaling up this model as the OP Topic points out is there are 1 Billion combinations instead of just 9. However it's feasible to build a model by breaking the problem down in parts.

Using the same approach as above, you can model the probabilities for each total (from 1 - 27 points) that 3 Competitors could get when each could score 0-9 points.

I used a worksheet to model Competitors ABC and another 2 sheets for DEF, and GHI. There are 1,000 rows for combinations on each sheet which isn't too bad.

The last step is to apply the same process on a summary worksheet with 3 columns showing all the combinations of each total (from 1-81 points) that the 3 sets of 3 competitors could get when each set could score 0-27 points. About 22,000 rows are needed for all combinations on this sheet. That's a lot of rows, but much less than a billion. :)

There is probably a way to do this more directly and if so I'd be interested to learn that if someone that knows probability theory could explain a better way.
 
Upvote 0
I thought this was a good fit for a simulation - (which also complements my lack of maths!).

The result is of course a probability distribution. Interestingly, the expected value is about 9 but this is not a likely value because the scores are discrete (this was an interesting surprise to me). The most common result (by a hair) is six, but when I'm running trials of 100,000 sometimes seven gets to the top, and 5, 6, and 7 are all very close.

so, the main thing is that you could just plug in your values and in a few seconds graph the probabilistic results. Note that in "real life" although a higher score such as 18 is "unlikely" it will happen sometimes ... about 1 time out of 50.

Here's the graph of 1,000,000 trials:

<img alt="graph" src="http://northernocean.net/etc/mrexcel/20110502_mc4.png" />

Here's my workbook sample (not polished but a start):
<a href="http://northernocean.net/etc/mrexcel/20110502_mcsim3.zip" />Workbook</a>

zip file md5sum is:
8c271f4a8477ae8c47b6a5e9e846f56a)
zip file sha256sum is:
7a08d23a10faffa4e2d1e15a07653ca49d436adf32cbf8c6d8ce1bcd3742e38d


When I run a million trials (running 50,000-100,000 is faster though - 5 seconds vs a minute or two):

Hits Score Score Hit%
04578 00 00 0.46%
17122 01 01 1.71%
35711 02 02 3.57%
53895 03 03 5.39%
66948 04 04 6.69%
74526 05 05 7.45%
76661 06 06 7.67%
74602 07 07 7.46%
72707 08 08 7.27%
69374 09 09 6.94%
66528 10 10 6.65%
62055 11 11 6.21%
56188 12 12 5.62%
49061 13 13 4.91%
42252 14 14 4.23%
35142 15 15 3.51%
29173 16 16 2.92%
24331 17 17 2.43%
19875 18 18 1.99%
16058 19 19 1.61%
12709 20 20 1.27%
10112 21 21 1.01%
07593 22 22 0.76%
05956 23 23 0.60%
04450 24 24 0.45%
03368 25 25 0.34%
02513 26 26 0.25%
01877 27 27 0.19%
01370 28 28 0.14%
00970 29 29 0.10%
00712 30 30 0.07%
00497 31 31 0.05%
00349 32 32 0.03%
00224 33 33 0.02%
00171 34 34 0.02%
00119 35 35 0.01%
00082 36 36 0.01%
00049 37 37 0.00%
00031 38 38 0.00%
00022 39 39 0.00%
00020 40 40 0.00%
00006 41 41 0.00%
00003 42 42 0.00%
00003 43 43 0.00%
00004 44 44 0.00%
00000 45 NA 0.00%
00001 46 46 0.00%
00000 47 NA 0.00%
00002 48 48 0.00%
00000 49 NA 0.00%
00000 50 NA 0.00%
00000 51 NA 0.00%
00000 52 NA 0.00%
00000 53 NA 0.00%
00000 54 NA 0.00%
00000 55 NA 0.00%
00000 56 NA 0.00%
00000 57 NA 0.00%
00000 58 NA 0.00%
00000 59 NA 0.00%
00000 60 NA 0.00%
00000 61 NA 0.00%
00000 62 NA 0.00%
00000 63 NA 0.00%
00000 64 NA 0.00%
00000 65 NA 0.00%
00000 66 NA 0.00%
00000 67 NA 0.00%
00000 68 NA 0.00%
00000 69 NA 0.00%
00000 70 NA 0.00%
00000 71 NA 0.00%
00000 72 NA 0.00%
00000 73 NA 0.00%
00000 74 NA 0.00%
00000 75 NA 0.00%
00000 76 NA 0.00%
00000 77 NA 0.00%
00000 78 NA 0.00%
00000 79 NA 0.00%
00000 80 NA 0.00%
00000 81 NA 0.00%
 
Last edited:
Upvote 0
xenou,

That is really slick. Thanks for sharing your methodology...I was shocked how compact the VBA portion is.

If you are interested in comparing...these are the calculated odds. Your simulation is extremely close.


<TABLE style="WIDTH: 287pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=379 border=0><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" span=2 width=58><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl78 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" width=58 height=20>Score</TD><TD class=xl78 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #b8cce4" width=58>Sim.</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=58>Calc.</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 23pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=31></TD><TD class=xl78 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #b8cce4" width=58>Score</TD><TD class=xl78 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #b8cce4" width=58>Sim.</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=58>Calc.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>00</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.46%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>0.4541%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">17</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">2.43%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>2.4373%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>01</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">1.71%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>1.7310%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">18</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">1.99%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>1.9974%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>02</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">3.57%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>3.5674%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">19</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">1.61%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>1.6150%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>03</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">5.39%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>5.3910%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">20</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">1.27%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>1.2842%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>04</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">6.69%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>6.7466%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">21</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">1.01%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>1.0044%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>05</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">7.45%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>7.4558%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">22</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">0.76%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>0.7748%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>06</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">7.67%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>7.6170%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">23</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.60%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>0.5915%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>07</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">7.46%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>7.4764%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">24</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">0.45%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>0.4481%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>08</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">7.27%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>7.2247%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">25</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.34%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>0.3369%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>09</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">6.94%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>6.9545%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">26</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">0.25%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>0.2511%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>10</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">6.65%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>6.6343%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">27</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.19%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>0.1851%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>11</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">6.21%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>6.1863%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">28</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">0.14%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>0.1348%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>12</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">5.62%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>5.5933%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">29</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.10%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>0.0970%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>13</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">4.91%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>4.9056%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">30</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">0.07%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>0.0691%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>14</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">4.23%</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>4.1981%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">31</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.05%</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" align=right>0.0488%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #b8cce4" height=20>15</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">3.51%</TD><TD class=xl71 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>3.5339%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl74 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4">32</TD><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #b8cce4">0.03%</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>0.0343%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>16</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">2.92%</TD><TD class=xl75 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbe5f1" align=right>2.9461%</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbe5f1">33</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">0.02%</TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbe5f1" align=right>0.0239%</TD></TR></TBODY></TABLE>
 
Upvote 0
Excellent. Neat to see the side by side. I was also surprised how little vba was needed ... and more so how fast Excel calcs the numbers.
 
Upvote 0
I tried to solve this problem the long way.

1. Generate all possible permutations based on the number of competitors (9), number of points (10)
and the set of points {0,1,2,3,4,5,6,7,8,9}
2. Calculate probability of each permutation by taking the product of the associated percentages
3. Calculate probability of each possible total score by adding the probabilitites of each associated
permutation

However the number of permutations was too large for this problem, i.e 1 billion (=10^9).

So I reduced the number of competitors to 5, resulting in 100,000 permutations, and managed to solve
it for that case. I did it using code generously shared by shg at:
http://www.mrexcel.com/forum/showthread.php?t=530891&page=2

It took approx. 8 mins. The workbook consists of a Data sheet that looks like this:
Data

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 76px"><COL style="WIDTH: 93px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 100px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>competitor</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>A</TD><TD style="TEXT-ALIGN: right">70%</TD><TD style="TEXT-ALIGN: right">15%</TD><TD style="TEXT-ALIGN: right">5%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>B</TD><TD style="TEXT-ALIGN: right">45%</TD><TD style="TEXT-ALIGN: right">30%</TD><TD style="TEXT-ALIGN: right">10%</TD><TD style="TEXT-ALIGN: right">5%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>C</TD><TD style="TEXT-ALIGN: right">60%</TD><TD style="TEXT-ALIGN: right">20%</TD><TD style="TEXT-ALIGN: right">5%</TD><TD style="TEXT-ALIGN: right">5%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>D</TD><TD style="TEXT-ALIGN: right">35%</TD><TD style="TEXT-ALIGN: right">25%</TD><TD style="TEXT-ALIGN: right">15%</TD><TD style="TEXT-ALIGN: right">10%</TD><TD style="TEXT-ALIGN: right">7%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>E</TD><TD style="TEXT-ALIGN: right">50%</TD><TD style="TEXT-ALIGN: right">25%</TD><TD style="TEXT-ALIGN: right">10%</TD><TD style="TEXT-ALIGN: right">5%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">1%</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Here is the macro that is pasted in ThisWorkbook and run from the above worksheet:
Code: 
Option Explicit
 
Sub ProbTotalScore()
    Dim avdScore As Variant
    Dim nScore As Long, nCmp As Long
    Dim ScoreRng As Range
 
    Dim aiInx() As Long, aiMin() As Long, aiMax() As Long
 
    Dim i As Long, iRow As Long, iCol As Long
    Dim ws As Worksheet, homews As Worksheet
    Dim mySheets As Long, mySheetCtr As Long, myColCounter As Long
    Dim myRowCounter As Long, myRowCtr As Long, maxScore As Long, maxRows As Long
 
    Const calc = "Calc"
 
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
 
    maxRows = ActiveSheet.Rows.Count
 
    Set homews = ActiveSheet
    homews.Name = "Data"
 
    For Each ws In ActiveWorkbook.Worksheets
        If Left(ws.Name, 4) = calc Or ws.Name = "Results" Then ws.Delete
    Next
 
    sheets.Add
    ActiveSheet.Name = calc & mySheets
 
    Application.DisplayAlerts = True
 
    homews.Range(homews.Cells(1, 2).Address & ":" & homews.Cells(1, homews.Range("IV1").End(xlToLeft).Column).Address).Copy
    ActiveSheet.Cells(1, homews.Range("IV1").End(xlToLeft).Column + 2).PasteSpecial Transpose:=True
    Set ScoreRng = ActiveSheet.UsedRange.Columns(1)
    ActiveWorkbook.Names.Add Name:="Score", RefersTo:=ScoreRng
    maxScore = Application.WorksheetFunction.Max(ScoreRng)
    avdScore = WorksheetFunction.Transpose(Range("Score"))
    nScore = UBound(avdScore)
    nCmp = Application.CountA(homews.Range("A:A")) - 1
    ReDim aiInx(1 To nCmp)
    ReDim aiMin(1 To nCmp)
    ReDim aiMax(1 To nCmp)
    For i = 1 To nCmp
        aiMin(i) = 1
        aiMax(i) = nScore
    Next i
    GrpPermute aiInx, aiMin, aiMax, True
    Do While GrpPermute(aiInx, aiMin, aiMax)
        If iRow = maxRows Then
            sheets.Add
            mySheets = mySheets + 1
            ActiveSheet.Name = calc & mySheets
            iRow = 0
        End If
        iRow = iRow + 1
        ActiveWindow.ScrollRow = iRow
        For iCol = 1 To nCmp
            ActiveSheet.Cells(iRow, iCol) = avdScore(aiInx(iCol))
        Next iCol
    Loop
    Application.Calculation = xlCalculationAutomatic
 
    For mySheetCtr = 0 To mySheets
        myRowCounter = 2
        For myColCounter = nCmp + 1 To nCmp * 2
            sheets(calc & mySheetCtr).Cells(1, myColCounter).FormulaR1C1 = "=HLOOKUP(RC[-" & nCmp & "],Data!R1C2:R100C50," & myRowCounter & ",FALSE)"
            myRowCounter = myRowCounter + 1
        Next
        sheets(calc & mySheetCtr).Cells(1, myColCounter).FormulaR1C1 = "=SUM(RC[-" & nCmp * 2 & "]:RC[-" & nCmp + 1 & "])"
        sheets(calc & mySheetCtr).Cells(1, myColCounter + 1).FormulaR1C1 = "=PRODUCT(RC[-" & nCmp + 1 & "]:RC[-2])"
        sheets(calc & mySheetCtr).Range(sheets(calc & mySheetCtr).Cells(1, nCmp + 1), sheets(calc & mySheetCtr).Cells(1, nCmp * 2 + 2)).AutoFill Destination:=sheets(calc & mySheetCtr).Range(sheets(calc & mySheetCtr).Cells(1, nCmp + 1), sheets(calc & mySheetCtr).Cells(sheets(calc & mySheetCtr).UsedRange.Rows.Count, nCmp * 2 + 2))
    Next
 
    sheets.Add
    ActiveSheet.Name = "Results"
 
    For myRowCounter = 1 To (nCmp * maxScore) + 1
        ActiveSheet.Cells(myRowCounter, 1) = myRowCounter - 1
        ActiveSheet.Cells(myRowCounter, 2).FormulaR1C1 = "=SUMIF(Calc0!C[" & nCmp * 2 - 1 & "],Results!RC[-1],Calc0!C[" & nCmp * 2 & "])"
        If mySheets > 0 Then
          For mySheetCtr = 1 To mySheets
            ActiveSheet.Cells(myRowCounter, 2).FormulaR1C1 = ActiveSheet.Cells(myRowCounter, 2).FormulaR1C1 & "+SUMIF(Calc" & mySheetCtr & "!C[" & nCmp * 2 - 1 & "],Results!RC[-1],Calc" & mySheetCtr & "!C[" & nCmp * 2 & "])"
          Next
        End If
    Next
 
    ActiveWorkbook.Names("Score").Delete
 
End Sub
 
Function GrpPermute(aiInx() As Long, aiMin() As Long, aiMax() As Long, _
                    Optional bInit As Boolean = False) As Boolean
    ' shg 2007
 
    ' Changes array aiInx to the next permutation, varying elements between
    ' the min and max values in aiMin and aiMax. The three arrays must all
    ' be 1-based and the same size.
 
    ' To initialize aiInx so that the *next* call returns the first combination,
    ' call with bInit True.
 
    ' Init returns              {aiMin(1), aiMin(2), ... aiMin(m) - 1}
    ' The first permutation is  {aiMin(1), aiMin(2), ... aiMin(m)}
    ' The last is               {aiMax(1), aiMax(2), ... aiMax(m)}
 
    ' Returns False when no more permutations exist
 
    Dim i       As Long
    Dim m       As Long
 
    m = UBound(aiInx)
 
    If bInit Then
        For i = 1 To m - 1
            aiInx(i) = aiMin(i)
        Next i
        aiInx(m) = aiMin(i) - 1
        GrpPermute = True
 
    Else
        For i = m To 1 Step -1
            If aiInx(i) < aiMax(i) Then
                aiInx(i) = aiInx(i) + 1
                Exit For
            End If
            aiInx(i) = aiMin(i)
        Next i
 
        GrpPermute = i > 0
    End If
End Function

The macro creates new sheets as needed and fills all available rows with the permutations of the points set. It then uses HLOOKUP to fill the probabilities next to the permutations. Finally it creates a Results sheet that shows the probability of Total Scores. Once the macro has been run, it won't have to be run again if the probabilities are changed in the Data sheet. However if the number of competitors and/or points set is increased/decreased through addition or deletion of rows and columns in the data sheet, then it will have to be run again as all permutations need to be regenerated. The problem of 9 competitors can be solved by adding rows for the remaining 4 competitors, however the macro would take several hours to generate the billion permutations.

I guess the permutations could also be generated manually through clever use of the Row() function perhaps, however that would become cumbersome if multiple sheets are used.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps

Forum statistics

Threads
1,224,585
Messages
6,179,704
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top