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

golfcarter · Apr 15, 2011

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!

T. Valko · Jun 3, 2011

spitfire said:
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.

Well, there's enough of a difference that the formulas that work in Excel don't work in OOC.

Is there an Open Office forum where you can get help?

Aladin Akyurek · Jun 4, 2011

spitfire said:
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): ...

What follows is directly from OpenOffice...

<TABLE border=0 rules=none cellSpacing=0 frame=void cols=6><COLGROUP><COL width=136><COL width=107><COL width=107><COL width=107><COL width=107><COL width=107></COLGROUP><TBODY><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle width=136 align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=right SDNUM="1033;" SDVAL="292">292</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=right SDNUM="1033;" SDVAL="292">292</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=right SDNUM="1033;" SDVAL="317">317</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=right SDNUM="1033;" SDVAL="269">269</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=center>Art</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=center>John</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=center>Bill</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=center>Jim</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=center>Henry</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=23 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40401">8/11/2010</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="55">55</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=23 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40408">8/18/2010</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="58">58</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="54">54</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="50">50</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=23 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40415">8/25/2010</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="61">61</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="56">56</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="63">63</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="49">49</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="53">53</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=23 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40422">9/1/2010</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="56">56</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="61">61</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="60">60</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="49">49</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=23 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40429">9/8/2010</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="59">59</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="57">57</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="56">56</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="52">52</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="49">49</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40436">9/15/2010</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="59">59</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="61">61</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="65">65</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="57">57</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40653">4/20/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="55">55</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="68">68</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="53">53</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40660">4/27/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40667">5/4/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="58">58</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="60">60</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="65">65</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="55">55</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40674">5/11/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="57">57</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="62">62</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="57">57</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40681">5/18/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="64">64</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="72">72</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="50">50</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40688">5/25/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="61">61</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="57">57</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="60">60</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40695">6/1/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="61">61</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="65">65</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="65">65</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=right SDNUM="1033;" SDVAL="54">54</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40702">6/8/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40709">6/15/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40716">6/22/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40723">6/29/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff height=22 vAlign=middle align=right SDNUM="1033;1033;M/D/YYYY" SDVAL="40730">7/6/2011</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" bgColor=#ffffff vAlign=middle align=left>

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

B1, control+shift+enter, not just enter:

Rich (BB code):

=IF(COUNT(B3:B20)>=6;  SUM(IF(ISNUMBER(MATCH(ROW(B3:B20);LARGE(IF(B3:B20>0;
   ROW(B3:B20));{1;2;3;4;5;6});0));B3:B20))-
    MAX(IF(ISNUMBER(MATCH(ROW(B3:B20);LARGE(IF(B3:B20>0;
     ROW(B3:B20));{1;2;3;4;5;6});0));B3:B20));"")

I couldn't figure out how to copy this formula across, so it's re-created for each other cell in C1:F1.

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

golfcarter

New Member

T. Valko

Well-known Member

Excel Facts

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

golfcarter

New Member

T. Valko

Well-known Member

Excel Facts

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock