Add the last 2 or 3 non-zero numbers in a row

[golfcarter]

I have a series of golf scores in rows. For the purpose of this league, we use your last three scores to determine your handicap. If you have played 1 or 2 rounds, we use your handicap for the one round or the two rounds.

A typical row might look like this

85 0 84 88 0 81 86 0 85

The 0's are because the golfer didn't play in a particular week.

For the example above, in week 4, this golfer would have the following row

85 0 84

So I'd need to add 85+84 and then calculate the handicap based on 2 scores. I'll need to know how many scores are in use to properly finish the handicap math.

In week 5, the golfer would have had this row:

85 0 84 88

So now I'd need to add all three non-zero scores to calculate

in week 9, we'd have this row

85 0 84 88 0 81 86 0 85

so now I would need to add 81+86+85 and then calculate the handicap knowing I had three scores.

There is a max handicap, and the handicap can be a negative number.

Any suggestions? I'm pretty good at Excel, but this one has me stumped and is beyond my normal excel use.

Thanks to all, and here's to hunting birdies!

 

[jaybee3]

Hi there, out of interest what's the calculation to find the handicap?

 

[golfcarter]

(((Adjusted Score - Course Rating) * 113) / Course Slope) * .96.

In the real world, you take the lowest 10 of the last 20 rounds to come up with your handicap. The league I help out with is over 65 years old, and is primarily played by guys over 70. They made things easier (but not necessarily fairer) by just doing this little averaging system.

BTW - Only a bunch of drunk Scotsman would come up with that formula and say it makes sense.

Carts

 

[spitfire]

GolfCarter -

Did you find an answer yet? I have a similar problem. My father-in-law is in charge of finding the handicaps and tracking scores for his league. I told him I might be able to automate the process for him, but it turns out I'm a bit over my head on this one.

They take the last 6 games played (which means for some of the guys in their late 80's, they go back quite a few weeks at times). From there, they drop the highest score of the six and add the remaining scores. There is then a chart they reference that number to that will tell the handicap. I can do the look-up portion. Its the part where I find the last six games played and then drop the highest and add the remaining where I'm stumped. I've learned a bit about dynamic ranges in my research, but I don't know how to do anything from there and everything I've tried gives me an error message (Err 508, Err 512, etc.). I also need it so that my FIL can add scores week by week, so the range needs to be dynamic.

Sounds like what I'm trying to do is very similar.
Any help would be greatly appreciated. I would like to have this for him at least by Father's Day.
Sample data below (names are changed to protect the innocent):

<table frame="VOID" rules="NONE" border="0" cellspacing="0" cols="6"> <colgroup><col width="94"><col width="70"><col width="59"><col width="53"><col width="53"><col width="53"></colgroup> <tbody> <tr> <td width="94" align="LEFT" height="34"></td> <td width="70" align="center">Art</td> <td width="59" align="center">John</td> <td width="53" align="center">Bill</td> <td width="53" align="center">Jim</td> <td width="53" align="center">Henry</td> </tr> <tr> <td align="RIGHT" height="17">08/11/10</td> <td align="RIGHT">55</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="RIGHT" height="17">08/18/10</td> <td align="RIGHT">58</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="RIGHT">54</td> <td align="RIGHT">50</td> </tr> <tr> <td align="RIGHT" height="17">08/25/10</td> <td align="RIGHT">61</td> <td align="RIGHT">56</td> <td align="RIGHT">63</td> <td align="RIGHT">49</td> <td align="RIGHT">53</td> </tr> <tr> <td align="RIGHT" height="17">09/01/10</td> <td align="LEFT">
</td> <td align="RIGHT">56</td> <td align="RIGHT">61</td> <td align="RIGHT">60</td> <td align="RIGHT">49</td> </tr> <tr> <td align="RIGHT" height="17">09/08/10</td> <td align="RIGHT">59</td> <td align="RIGHT">57</td> <td align="RIGHT">56</td> <td align="RIGHT">52</td> <td align="RIGHT">49</td> </tr> <tr> <td align="RIGHT" height="17">09/15/10</td> <td align="RIGHT">59</td> <td align="RIGHT">61</td> <td align="LEFT">
</td> <td align="RIGHT">65</td> <td align="RIGHT">57</td> </tr> <tr> <td align="RIGHT" height="17">04/20/11</td> <td align="LEFT">
</td> <td align="RIGHT">55</td> <td align="LEFT">
</td> <td align="RIGHT">68</td> <td align="RIGHT">53</td> </tr> <tr> <td align="RIGHT" height="17">04/27/11</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="RIGHT" height="17">05/04/11</td> <td align="LEFT">
</td> <td align="RIGHT">58</td> <td align="RIGHT">60</td> <td align="RIGHT">65</td> <td align="RIGHT">55</td> </tr> <tr> <td align="RIGHT" height="17">05/11/11</td> <td align="LEFT">
</td> <td align="RIGHT">57</td> <td align="LEFT">
</td> <td align="RIGHT">62</td> <td align="RIGHT">57</td> </tr> <tr> <td align="RIGHT" height="17">05/18/11</td> <td align="LEFT">
</td> <td align="RIGHT">64</td> <td align="LEFT">
</td> <td align="RIGHT">72</td> <td align="RIGHT">50</td> </tr> <tr> <td align="RIGHT" height="17">05/25/11</td> <td align="RIGHT">61</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="RIGHT">57</td> <td align="RIGHT">60</td> </tr> <tr> <td align="RIGHT" height="17">06/01/11</td> <td align="LEFT">
</td> <td align="RIGHT">61</td> <td align="RIGHT">65</td> <td align="RIGHT">65</td> <td align="RIGHT">54</td> </tr> <tr> <td align="RIGHT" height="17">06/08/11</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="RIGHT" height="17">06/15/11</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="RIGHT" height="17">06/22/11</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="RIGHT" height="17">06/29/11</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="RIGHT" height="17">07/06/11</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> </tbody> </table>

 

[c_m_s_jr]

Well to answer the original post (along with some help from Aladin Akyurek in my post:
http://www.mrexcel.com/forum/showthread.php?t=554791&goto=newpost)

I think you can use the following two formulas. One for the score and 1 for how many scores are used:

<TABLE style="WIDTH: 636pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=847><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=60>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=52>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=60>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=52>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=60>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=52>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=60>H</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=52>I</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=60>J</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=138>K</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>L</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Golfer</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 1

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Round 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Last 3 Rounds <> 0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63># of Scores</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>85</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>84</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>88</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>81</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>86</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>85</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>252

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2740086 class=xl63 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>77</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>77</TD>

<TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>88</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>80</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>168

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2740086 class=xl63 align=right>2</TD></TR></TBODY></TABLE>

In K2:

={SUM(IF(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(IF(B2:J2>0,COLUMN(B2:J2)),{1,2,3}),0)),B2:J2))}

-- Confirm with ctrl+shift+enter to get the {}

In L2:
={COUNT(IF(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(IF(B2:J2>0,COLUMN(B2:J2)),{1,2,3}),0)),B2:J2))}

-- Confirm with ctrl+shift+enter to get the {}

For the last 6 games you would just change the {1,2,3} to be {1,2,3,4,5,6}

 

[spitfire]

I tried what you suggested, first got a Err533 : Nested arrays are not supported.

I set {1,2,3,4,5,6} as a definition named six_list, but now I'm getting a Err512 : Formula overflow.

Not sure what I'm doing wrong.

={SUM(IF(ISNUMBER(MATCH(COLUMN(C2:J2),LARGE(IF(C2:J2>0,COLUMN(C2:J2)),six_list)))))}

 

[c_m_s_jr]

I tried what you suggested, first got a Err533 : Nested arrays are not supported.

I set {1,2,3,4,5,6} as a definition named six_list, but now I'm getting a Err512 : Formula overflow.

Not sure what I'm doing wrong.

={SUM(IF(ISNUMBER(MATCH(COLUMN(C2:J2),LARGE(IF(C2:J2>0,COLUMN(C2:J2)),six_list)))))}

Did you try just putting 1,2,3,4,5,6 into the formula?

={SUM(IF(ISNUMBER(MATCH(COLUMN(C2:J2),LARGE(IF(C2:J2>0,COLUMN(C2:J2)),{1,2,3,4,5,6})))))}

Also don't put the {} at the beginning or end of the formula youself. After entering the formula in the cell hit ctrl+shift+enter to confirm. That will put the {} around the formula.

 

[spitfire]

Tried that and I'm still getting Err512.

 

[T. Valko]

Tried that and I'm still getting Err512.

Excel doesn't have an Err512.

Are you by any chance using Open Office Calc?

 

[spitfire]

Yes. I'm in open office. Is there a big difference? I can't afford Microsoft's software. Maybe that's my whole problem.

My FIL has Excel, so maybe I will just need to do all this on his machine instead of on my own time at home.