Long and Wrong Nested IF

[Scott Krause]

Using Excel 2003,

Here's my original table....
C E F G
<TABLE style="WIDTH: 270pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=361 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=131 height=18>Name</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 31pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=41>Class</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 26pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=35>HT</TD><TD class=xl35 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 21pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=28>WT</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=63>Max</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=63>Points</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>9</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>175</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>305</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>6</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>95</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">85</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>7</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>226</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>305</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>8</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>110</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">110</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>


I'm trying to get this nested if to work with the above table,

=IF(C5=9,{F5<E5,0;F5<135,0;F5<170,1;F5<205,2;F5<230,3;F5<260,4,5}IF(C5=10,{F5<E5,0;F5<135,0;F5<180,1;F5<210,2;F5<235,3;F5<275,4,5}IF(C5=11,{F5<E5,0;F5<135,0;F5<185,1;F5<230,2;F5<250,3;F5<305,4,5}IF(C5=12,{F5<E5,0;F5<135,0;F5<190,1;F5<240,2;F5<270,3;F5<315,4,5}))))

The table below is where I got the numbers from....

<TABLE style="WIDTH: 541pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=721 border=0 x:str><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" span=3 width=144><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 109pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=145 height=17></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 432pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=576 colSpan=4> Class:</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 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: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bench Points</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>9</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="BW<MAX<135 ">BW(E5)<MAX(F5)<135 </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="BW<MAX<135 ">BW(E5)<MAX(F5)<135 </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="BW<MAX<135 ">BW(E5)<MAX(F5)<135 </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="BW<MAX<135 ">BW(E5)<MAX(F5)<135 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>135</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>135</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>135</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>135</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>170</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>180</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>185</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>190</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>205</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>210</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>230</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>240</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>230</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>235</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>250</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>270</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>5</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>260</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>275</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>305</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>315</TD></TR></TBODY></TABLE>

Here are some examples of what should be entered into Column G.
The student from row 7 should have a 5 entered because he's in class 11 and his max is 305.

The student in row 6 should have a 0 entered because he weighs less than 135 AND maxed less than his body weight.

The student in row 8 should have a 1 entered because he weighs less than 135 BUT maxed at least his body weight.

I hope this makes more sense than my explanation in the thread, Entering an entire table in one cell....

Thanks again.

Scott

 

[Scott Krause]

Jason, I took a closer look at the squat formula. After changing a few cells it worked but it still gave 1 point for a 0.

=IF(ISERROR(MATCH(J5,INDEX('Ironman Table'!$C$19:$G$23,,MATCH(C5,'Ironman Table'!$C$18:$G$18)))),IF(J5<E5,0,1),MATCH(J5,INDEX('Ironman Table'!$C$19:$G$23,,MATCH(C5,'Ironman Table'!$C$18:$G$18))))

<TABLE style="WIDTH: 88pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=116 border=0 x:str><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" width=58><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=58 height=18>Max</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=58>Points</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>400</TD><TD class=xl29 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; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>360</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>4</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>0</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>260</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>260</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num>375</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>360</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>400</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num>395</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>395</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>315</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>215</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR></TBODY></TABLE>

 

[jasonb75]

Try

=IF(J5>0,MATCH(J5,INDEX('Ironman Table'!$D$19:$H$23,,MATCH(C5,'Ironman Table'!$C$18:$G$18))),0)

With the revised point system where you're giving 1 point for anything above no lift, you no longer need the error part so the formula can be reduced, the one above works for me as it should, if it still gives errors then it could just be a case of tweaking ranges.

Ranges used in the formula above.

In the table where you enter the formula C5 = class number, J5 = max,
$D$19:$H$23 = green part of ironman table from my reply in post #58,
$C$18:$G$18 = class numbers in ironman table.

 

[Scott Krause]

when I hit tab or enter, it takes me to a window that says update values. If I select the file and hit ok it takes me to another window that asks me to select a sheet. Which sheet should I select?

Thanks again,

Scott

 

[jasonb75]

That sounds like it's looking for a sheet it can't find, check the formula to make sure the 'Ironman Table'! ranges are correct.

I'm assuming all of the sheets are in the same file, so the file and sheet selection you described shouldn't be happening.

 

[Scott Krause]

I think I figured out which sheet was causing the windows to pop up. Yesterday, I deleted a wrong worksheet in the workbook so I had to copy the info from another saved copy. To solve the window pop up problem I made a copy of the worksheet, then deleted the original.

The shortened formula

=IF(J8>0,MATCH(J8,INDEX('Ironman Table'!$C$19:$G$23,,MATCH(C8,'Ironman Table'!$C$18:$G$18))),0)

is giving 0 maxes #N/A, like the Bench max formula is.

 

[jasonb75]

Scott, I just thought of something,in addition to my last reply if you haven't found the problem.

If you do what you did before so that the update values window appears, the titlebar will say "Update Values: Ironman Table", this will be the sheetname causing the error, it's possible it could be a spelling error in the formula, but this will identify it.

If you then do Ctrl + f and enter the wrong name shown into the search box it will find it for you.

 

[jasonb75]

Click the dropdown arrow that appears to the left of the N/A cell when you select it, then choose show calculation steps, and post the results.

You can't copy / paste it so it might be easier to use screen print and post the image, but you'll have to paste the screenprint to paint and save it as jpeg to copy it to the forum.

 

[jasonb75]

Sorry, ignore last one, show calculation steps doesn't show for N/A, or at least not on the one I just forced.

Think the only way I can solve this easily is if you can send me a copy when you get into school tomorrow.

 

[Scott Krause]

Something interesting just happened. When I manually entered a 0 in the Max cell the point formula worked. I then went back and linked the Max cell to the Strength Tests worksheet and the #N/A returned. Should we be using the Strength Test cell in the formula instead of the Max cell?

 

[jasonb75]

If it works then maybe its a good way to do it, but I'm confused as to why it's doing that, if the formula in the max cell was showing a figure then it should have worked.

Unless excel is not recognising it as a number, are the weights entered into the strength test sheet directly?

Check the cell format, make sure it's set as general, although again it shouldn't make a difference, but you said earlier that you had to delete and copy a sheet so strange things have been known to result from dulpicated sheets.