How to calculate streaks in excel

So in my last post, i showed you how to look at streaks for coin tosses in Excel

In this post, i'll show you how i calculate the streaks in Excel (or google docs).   You can use the same technique to calculate streaks in anything (i.e. roulette).

What is a streak?

For coin tosses would be the same coin be thrown X times in a row.  e.g. 7 heads in a row would be a streak.

What does this look like in Excel?

First thing we need to do is track the length of a streak.   This is how it looks for me in Excel.

1) In the left hand column is the result of the coin toss (heads or tails),  you can generate this randomly using the technique described in my blog post, generating random coin tosses in excel

2) The right hand column tells the length of the streak.   As you can see in the image above, every time the streak ends, the count gets reset to 1.  If the latest coin toss is the same as the previous it increases

How do I calculate this in Excel (or google docs)?

I've used the following formula to calculate this.  I put this in each cell in the B column

=IF(A2=A1,B1+1,1)

Column A is the column with the result of the coin toss

Column B is the length of the streak.

This formula basically says, if the current coin toss is the same as the previous coin toss then increment the streak count by 1.   If the coin toss is different from the previous, reset the streak count to 1.

Summarizing the Data

Now that we've got the streaks.   We want a summary of the data.  This is how it looks in Excel

And this how we calculate it

1) Left column is a just count of the streaks (just enter 1 to 15)

2) Right column is calculated by the following formula

=COUNTIF(Sheet17!B2:B20001,A2)-B3

This formula counts up all the entries in the range that meet the IF condition.  In our case, we look at our 20,000 coin tosses that we've held in Sheet 17 in cells B2 to B20001.   The condition it needs to meet is the value in the A column (i.e. the length of the sequence, in this case A2).   So for row 10, it will count up all the sequences of length 9.     Now obviously a sequence of 9 will also count all the sequences of 10's.   So we subtract that number that's next in the sequence.   We don't want to count all the 10's in the 9 sequence count (that's the -B3 bit).

And then we can generate a chart against the distribution, i used a nice bar chart

Anyways, i hope this is useful.  This technique is pretty useful for predicting monte-carlo predictions of betting patterns and proving how bad things like martingale are.   And if you develop some advantage play system in blackjack, roulette, sports betting then this technique will allow you to calculate streaks.

Hope it helps.