Longest Toss winning streak

In this article, we will learn how to get the Longest Toss winning streak in Excel.

For instance, you have a large data of a team of a particular season's wins or losses. Or getting to know how many consecutive times HEADS occur in the coin toss session. Get the formula to calculate the longest winning streak of the session.

For this article we will be needing the use of the following functions:

Generic formula for getting the longest winning streak:

= {MAX ( FREQUENCY ( IF ( result = "H" , times ) , IF ( result = "H" , 0 , times ) ) )}

Explanation:

IF ( result = "H" , times ) returns an array of the count of wins ID and FALSE values.
IF ( result = "H" , 0 , times ) returns array of 0s and count of losses ID.
Now FREQUENCY function returns the frequency distribution of each array element in the second array.
Finally, MAX function will return the max occurrence of the value.

Note: Curly braces in excel must not be given manually instead use CTRL + SHIFT + ENTER.

Let's jump into an example to make things clear:

Example: Get Longest Streak of Heads in a Toss Using Frequency Distribution

John has a coin and he tosses the coin at times and records their respective results in the adjacent cell. We need to find out what the maximum times consecutive heads occur.

Now we will use the below formula to get the MAX count for the data

Formula:

{ = MAX ( FREQUENCY ( IF ( result = "H" , times ) , IF ( result = "H" , 0, times ) ) ) }

Explanation:

IF ( result = "H" , times ) returns an array of the count of wins ID and FALSE values. {1;FALSE;3;4;5;6;FALSE;8;9;FALSE;FALSE;FALSE;13;FALSE;15}
IF ( result = "H" , 0 , times ) returns array of 0s and count of losses ID. {0;2;0;0;0;0;7;0;0;10;11;12;0;14;0}
Now FREQUENCY function returns the frequency of each array element in the second array. =MAX({0;1;0;0;0;0;4;0;0;2;0;0;0;1;0;1})
MAX function returns the max occurrence of the value.

Here result is named range used for the array B2:B16 & times is the named range used for the array A2:A16. Press Ctrl + Shift + Enter to get the result.

The function returns #VALUE error if the formula doesn't conclude with Ctrl + Shift + Enter shortcut.

As you can see using the above formula, you can get the max consecutive occurrence. Below are some of the observational results.

Notes:

The function considers non- numeric values as 0s.
The function considers logic value TRUE as 1 and FALSE as 0.
The argument array must be of the same length else the function returns error.
The function returns 0 if the Ctrl + Shift + Enter for curly braces is not used.

Hope this article about how to Longest Toss a winning streak in Excel is explanatory. Find more articles on FREQUENCY functions here. Please share your query below in the comment box. We will assist you.

Related Articles

How to use the FREQUENCY function in Excel

How to use the MAX function in Excel

How to use the IF function in excel

How to Remove Text in Excel Starting From a Position

How to use the ROWS function in Excel

Validation of text entries

Create a drop down list in excel with colour

Remove lead and trailing spaces from text in Excel

Popular Articles

50 Excel Shortcuts to Increase Your Productivity

Edit a dropdown list