Long and Wrong Nested IF

Scott Krause

Board Regular
Joined
Mar 8, 2009
Messages
96
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
 
That's not causing the problem on mine.

For the row for Cummings, Trent, can you post as

A1 Cummings, Trent
B1 11
C1 WT
D1 Max
E1 Points

But with the cells as in your sheet, and the formula for the cell if there is one, hopefully I can pick out the problem from that.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
B8-Trent Cummings
Code:
='Strength Tests!'B8
C8-Class11
Code:
='Strength Tests!'C8
D8-HT (Not concerned with that)
E8-WT
Code:
='Strength Tests!'E8
F8-Max
Code:
='Strength Test!H8
G8-Points
PHP:
=IF(ISERROR(MATCH(F6,INDEX('Ironman Table'!$C$12:$G$16,,MATCH(C6,'Ironman Table'!$C$11:$G$11)))),IF(OR(F6<E6,F6=0),0,1),MATCH(F6,INDEX('Ironman Table'!$C$12:$G$16,,MATCH(C6,'Ironman Table'!$C$11:$G$11))))


Is this what you wanted?
 
Upvote 0
OK, just talked to our Head Coach. He said give every kid at least one point! We can stop banging our heads against the wall.

So, 0-134lbs= 1 pt.

Same concept for squats. For 8th graders and 9th graders, 0-210 = 1 pt.
 
Upvote 0
Sorry Scott, couldn't reply earlier, had power failure and only just got back.
That was the information I was looking for, weird thing is, I copied the data into the cells of a new sheet 'Strength tests', then copied your formulas into my existing table and my results stayed as my earlier reply. Maybe it's an excel 03 bug of some kind, which I'm unable to check.
 
Upvote 0
Our coach actually wants to have each kid get at least one point, unless they don't do the lift.

If their max is greater than 0 and less than 135, they get 1 pt. Thanks for the help Jason.
 
Upvote 0
Think this should do it now Scott,

If you revise your points tables as

<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=469 border=0 u1:str><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=3 width=76><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=81><COL style="WIDTH: 48pt" span=2 width=80><TBODY><TR style="HEIGHT: 26.25pt" height=44><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: #efefef" width=76 height=44>Bench Points</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>8</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>9</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>10</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>11</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>12</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>0</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>0</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>170</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>170</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>180</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>185</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>190</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>205</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>205</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>210</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>230</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>240</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>4</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>230</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>230</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>235</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>250</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>270</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>5</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>260</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>260</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>275</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>305</TD><TD class=xl71 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>315

</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=473 border=0><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=76><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=81><COL style="WIDTH: 48pt" span=3 width=80><TBODY><TR style="HEIGHT: 26.25pt" height=44><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: #efefef" width=76 height=44>Squat Points

</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>8</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>9</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>10</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>11</TD><TD class=xl73 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>12</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:bool="TRUE">0</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:bool="TRUE">0</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:bool="TRUE">0</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:bool="TRUE">0</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:bool="TRUE">0</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>210</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>210</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>225</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>245</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>265</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>255</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>255</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>270</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>295</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>315</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>4</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>300</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>300</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>315</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>345</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>365</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23 u1:num>5</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=76 u1:num>345</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=81 u1:num>345</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>375</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>390</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" align=right width=80 u1:num>400

</TD></TR></TBODY></TABLE>

Then this should give the results as

no lift = 0 points
lift between 0 and 2 point weight for class = 1 point
otherwise points are allocated based on max weight

Formula for Bench

PHP:
=IF(NOT(F5>0),0,MATCH(F5,INDEX('Ironman Table'!$C$12:$G$16,,MATCH(C5,'Ironman Table'!$C$11:$G$11))))

For Squat
PHP:
=IF(NOT(J5>0),0,MATCH(J5,INDEX('Ironman Table'!$D$18:$H$22,,MATCH(C5,'Ironman Table'!$D$17:$H$17))))
 
Upvote 0
<TABLE style="WIDTH: 264pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=351 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: 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: 15.75pt" height=21><TD class=xl24 id=td_post_1871604 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=131 height=21>Acker, Kyle</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 31pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=41 x:num>11</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35 x:num>0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 21pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=28 x:num>175</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=58 x:num>305</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 44pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" width=58 x:num>5

<!-- php buffer end -->

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Anderson, Darren</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>200</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>295</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>4



</TD></TR><!-- php buffer end -->



<TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Benish, Nathan</TD><TD class=xl27 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=xl27 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=xl27 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=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>315</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Cummings, Trent</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>210</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Fischer, Kyle</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>165</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>205</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Follen, Mathhew</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>154</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Goetsch, Ben</TD><TD class=xl27 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=xl27 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=xl27 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=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Greiber, Devon</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>225</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>250</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Hahn, Sam</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>182</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>250</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Harman, Michael</TD><TD class=xl27 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=xl27 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=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>262</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>295</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>4</TD></TR></TBODY></TABLE>

All of the 0's return #N/A, as shown above.
 
Upvote 0
This is starting to get confusing Scott, I can't see why your sheet should return different resuts to mine, I'm sure that should only happen as a result of the first problem where I was using functions that are only available in 07.

Just a quick check in case you misunderstood this part, with your tables,

<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=473 border=0><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=76><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=81><COL style="WIDTH: 48pt" span=3 width=80><TBODY><TR style="HEIGHT: 26.25pt" height=44><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: #efefef" width=76 height=44>Squat Points</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=76>8</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=81>9</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80>10</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80>11</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=80>12</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23>1</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=76>0</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=81>0</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>0</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>0</TD><TD class=xl71 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>0</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23>2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=76>210</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=81>210</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>225</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>245</TD><TD class=xl71 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>265</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=76>255</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=81>255</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>270</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>295</TD><TD class=xl71 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>315</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23>4</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=76>300</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=81>300</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>315</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>345</TD><TD class=xl71 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>365</TD></TR><TR style="HEIGHT: 13.5pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" align=right width=76 height=23>5</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #92d050" align=right width=76>345</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 49pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #92d050" align=right width=81>345</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>375</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>390</TD><TD class=xl73 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #92d050" align=right width=80>400</TD></TR></TBODY></TABLE>

The first Match in the formula should be referencing the green cells only.

Might be easier if you could send me a copy to look at, sent you a private message with email address if you can do that.
 
Upvote 0
To be honest Scott, I'm at a loss, without seeing the sheet I'm running out of ideas as to what could be causing the errors, mine is producing the results that I've posted previously, I just can't work out what we're missing.

I'll try it on excel 03 later to see if it gives different results, but 07 is telling me it should work.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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