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 wins or losses. Or getting to know,How many consecutive times HEADS occur in the coin toss session. To get the formula to calculate the longest winning streak of a session.

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

  1. IF Function
  2. FREQUENCY function
  3. MAX function

IF function is logic_test excel function. IF function tests a statement and returns values based on the result.
Syntax:

=IF ( logic_test, value_if_true, value_if_false)

Frequency function returns the count of cells from the data_array where the number is less than or equals to the number provided in bins_array.
Syntax:

= FREQUENCY ( data_array, bins_array)

data_array: array of values to count
Bins_arrray: a value to check

MAX function returns the maximum value from the range.
Syntax:

= MAX ( number1, number2, ..)

MAX function only considers the numbers

Now we will make a formula out of these functions. Here we will given the Toss ID with their respective results and we needed to find the longest winning streak or Consecutive max occurance of HEADs in coin tossing session
Use the 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.
  • IF ( result = "H" , 0 , times ) returns array of 0s and count of losses ID.
  • Now FREQUENCY function returns the count of each array element in the second array.
  • MAX function returns the max occurance of the value.

Note: Curly braces in excel must not be given as manually instead use CTRL + SHIFT + ENTER and get the desired result.
Curly braces are used for the function which accepts only one value and returns only one value but to get a range of values or array from the same function we use Curly braces using the Ctrl + Shift + Enter

Let's test this formula via running it on an example
John has a coin and he tosses the coin n times and recorded their respective results in the adjacent cell. And we need to find what was the maximum times consecutive head occur.

MAX winning streak

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 count 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 occurance of the value.

MAX winning streak 2
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.

MAX winning streak 3

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:

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

Hope this article about how to Longest Toss 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 drop down list in excel with colour

Remove leading and trailing spaces from text in Excel

 

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Convert Inches To Feet and Inches in Excel 2016

Join first and last name in excel

Count cells which match either A or B

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube