Calculating win streak and maximum win streaks

Hello,

So i'm building a spreadsheet to help track my team's darts ladder and I ran into a small problem.

I'm recording the matches in a simple table and using that table to generate a host of others. The way I've built it depends upon the matches being laid out as they are inthe attached sheet.

However, I am struggling to write a formula to calculate:

1. the current win streak of each player - the number of wins in a row they currently have.

2. the longest win streak the player has ever achieved - teh highest number of wins in a row they have ever had.

Its totally beyond me! I've attached a sheet which shows the basic layout i'm after and wonder if anyone can do something suitably genius-like to it?

Many thanks for your time in reading this and many more if you have a solution!

Thanks,

Andrew.Win Streaks.xlsxWin Streaks.xlsx

Wow! That looks incredible. Its so good, I can't back-engineer it! In the real thing I'm returning the win streak into a table in a different sheet from the match results. I'm trying to insert the name of the sheet ( 'newsheet'! ) in front of your $c$6:$c$1000 references, but its just not working. I'm really sorry to bother you again, is there any chance you could give me some pointers?

Thank so much,

Andrew.

Thanks for the reply, Tony valko. I had checked it out but the peculiar lay out of my results made it difficult for me to see how i could use the same method. Probably me being a dummy : )

A.

Yes, exactly what i want, i just cant get it to work if the source data and output table are in different sheets.

A.

I've re-uploaded a new sheet to reflect what i'm trying to do. as you'll see, in this one I've separated the summary table onto a different sheet from the raw data. I thought i'd be able to do this easliy by adding 'Results'! in front of each of the references to $C$6 or $c$6:$c$1000 in xbor's solution, but no deal!

Anyone show me how i'm being stupid? Part of the problem is that normally i can work out how a formula actually works, but this one is beyond my (limited!) ability.

Any help very gratefully received!

A.

Win Streaks update.xlsx

If you left formulae in the table I could tell you what was wrong.
But now here is only solution.

You've been more than generous enough with your time anyway....sincere thanks. The problem I think is that I was typing in 'Results'! insetad of just Results! in front of the column reference.

You, sir, are a gentleman. Thanks again.

Andrew.

I don't write that manually. When need to get range from other sheet just click sheet and select range.
Try just typing = then select sheet, then select range and hit enter.