Winning Streak Formula Solution..Please !!!

[TC-Fireman]

Hi Everyone

I have looked all over for a solution to my problem and while some have been close I still cannot get this fixed.

I have a pool (Eight Ball) program. I currently have winning streaks and losing streaks in it but there is one problem. It is not calculating when somebody misses a game but still stays on a winning or losing streak.

I will try and give a good example of what I mean.

A1 B1 C1 D1 E1 F1 G1
3-0 2-1 2-1 3-0 2-1

Here a player wins his best out of three games for four consecutive weeks, (A1 -> D1). He then misses two weeks (E1 and F1) so there isn't even a 0 in these cells, they are just blank. He then comes back and wins his next night's games 2-1 (in G1). My formula currently only counts up until D1 and then starts recounting from G1 again. I would like to include all the consecutive cells in the count provided he has either won 3-0 or 2-1 for the night. I don't want to count the blank cells, but I do want the formula to carry on the winning streak because the player has not lost yet.

If the cell should read 1-2 or 0-3, then the player has obviously lost that night. That should then break the streak.

I have tried so many different ways to get this right but to no avail. We are coming up to our annual prizegiving and I need to re-calculate who the winning and losing streak winners are because it currently stops at all the players who couldn't play on one night and creates a blank which the formula doesn't count.

To give an idea of how big our league is and to see how I post our weekly results, here is a link to my results.

I cannot post my spreadsheet unfortunately because it is just way too big.

http://www.eightballleague.com/Core/index.php?page=21&sid=6be1a4cbc40e263d472a527498d34eab

I am only posting this link to show how serious I am in requiring help regarding this.

Any help would be greatly appreciated.

Best Regards

TC

 

[TC-Fireman]

Wow, ok, thanks Andrew.

I am not too familiar with VBA or VBE but I am more than willing to give it a try.

I will fiddle around with it and post a response to let you know if it worked or not.

I seriously appreciate the effort you went to here, so thanks.

I willl advise how it went.

Thanks again Andrew

 

[TC-Fireman]

Hi Andrew

Simply put, you are a genius.

It works likes the proverbial bomb. Seriously, a very big thank you for the time and effort I can see you put into this. This has made my program so much more easier to maintain with minimal effort.

It seems stable as well which was my concern, it first said it couldn't find the library but for some reason it did afterwards.

Thanks for the help

Have a fabulous weekend

Regards

TC

 

[Andrew Fergus]

You're welcome. Sorry for the slow reply to the thread - too much else going on!!

If you have any problems with the custom function just reply to this thread again.

Cheers
Andrew