SAT scores predict grades

[gw9880]

Hi everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Great forum! I'm so new to Excel, I'm not sure how to phrase my problem, but I will try!!! <o></o>
<o> </o>
Basically, I have an Excel model that predicts the chance of a student receiving a certain grade on an exam. I can change independent variables such as the student's SAT score to predict the chance that the student will receive an "A" in a class. The statistical model will give me an output like this:<o></o>
<o></o>
<o></o>
<o><TABLE style="WIDTH: 387pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=516 border=0 x:str><COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=4 width=96><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 99pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=132 height=17>Probability of F grade</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of D</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of C</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="2.2119782399276757E-3">0.2%</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="4.9855813608230461E-3">0.5%</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.34877317411824288">34.9%</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.38429842092666883">38.4%</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.25973084535433749" x:fmla="=1-D2-C2-B2-A2">26.0%</TD></TR></TBODY></TABLE> </o>
<o></o>
As you can see, the student is likely to receive a "B" grade. However, if I increase the student's SAT score, then it becomes most likely that he will receive an "A" grade. The probabilities change whenever I change the independent variable, such as SAT score.<o></o>
<o> </o>
My question: how can I get Excel to dynamically find the category with the highest probability (the probable grade) and also calculate the sum of all of the probabilities to the left and to the right of the most likely category. I know that the MAX function can give me the highest probability, and I've also been successful at using HLOOKUP to identify that category by name, but I have real problems with the summation.<o></o>
<o> </o>
As an example, here is one distribution of probabilities:<o></o>
<o></o>
<o><TABLE style="WIDTH: 387pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=516 border=0 x:str><COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=4 width=96><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 99pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=132 height=17>Probability of F grade</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of D</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of C</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=96>Probability of A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="2.2119782399276757E-3">0.2%</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="4.9855813608230461E-3">0.5%</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.34877317411824288">34.9%</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.38429842092666883">38.4%</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.25973084535433749" x:fmla="=1-D2-C2-B2-A2">26.0%</TD></TR></TBODY></TABLE> </o>
I need Excel to give me an output that sums up the probabilities of receiving a F, D, and C grade (35.6%) and the probability of an A grade (26%). Remember that these probabilities change, so the formula must:<o></o>
--know which cell is the largest<o></o>
--add all of the cells to the left of the largest cell<o></o>
--add all of the cells to the right of the largest cell<o></o>
<o> </o>
Any help you could give would be greatly appreciated--<o></o>
<o> </o>
Thank you,<o></o>
<o> </o>
Sandy<o></o>

 

[wigi]

Hi

A simple SUMIF function will be enough. I will create the formula for you.

 

[wigi]

See below please:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">F</td><td style=";">D</td><td style=";">C</td><td style=";">B</td><td style=";">A</td><td style=";">MAX %</td><td style=";">MAX grade</td><td style=";">Sum to the right</td><td style=";">Sum to the left</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0,2%</td><td style="text-align: right;;">0,5%</td><td style="text-align: right;;">34,9%</td><td style="text-align: right;;">38,4%</td><td style="text-align: right;;">26,0%</td><td style="text-align: right;;">38%</td><td style=";">B</td><td style="text-align: right;;">35,6%</td><td style="text-align: right;;">26,0%</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=MAX(<font color="Blue">A2:E2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=INDEX(<font color="Blue">A$1:E$1,MATCH(<font color="Red">F2,A2:E2,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=SUMIF(<font color="Blue">$A1:$E1,">"&$G2,$A2:$E2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=SUMIF(<font color="Blue">$A1:$E1,"<"&$G2,$A2:$E2</font>)</td></tr></tbody></table></td></tr></table><br />