Suppose we have a work schedule for employees, which indicates the number of working hours on each day of the week, or zero if the person did not work. The task is to determine how many days in a row each of the colleagues went to work:
In other words, determine the maximum number of consecutive values that satisfy a given condition. Sometimes such a problem is also called finding the “longest winning streak”, citing the example of an unbroken chain of consecutive victories in sports competitions. In English there is even a special term – “winning streak” for such a case.
To solve we need a function FREQUENCY (FREQUENCY)about which I have already written. In the normal case, this function is needed to count the number of numbers from the data range that fall into the back intervals (pockets):
It must be remembered that this function must be introduced in a special way:
- first, we select a range of empty cells for the results (green in the example above) – one more than the boundaries of the intervals (yellow)
- then we enter our function, specifying its two arguments – the range of data (A1:A20) and the range of intervals (E4:E6)
- and press combination Ctrl+Shift+Enterto enter the function as an array formula
How can this function help us solve the problem of finding the longest winning streak?
Let’s slightly change the wording of the previous example for clarity:
What changed:
- The source data range is horizontal, not vertical.
- Now these are not randomly shuffled numbers, but an increasing sequence of 1,2,3 … obtained using the function COLUMN (COLUMN)A that returns the column number for the current cell.
- Suppose, for example, that the numbers 4,9,16 are the boundaries of the ranges that interest us. I “pulled” them out of the original sequence into a separate row.
If we need to count the number of numbers from the original sequence 1..20 falling into each of the intervals (1-4, 4-9, 9-16, 16-20), then the function FREQUENCY can be entered as:
Doesn’t it remind you of anything? For example, the first line of our time sheet? Only instead of hours of work we have a sequence of numbers – column numbers, and instead of zeros, the boundaries of intervals. So now we can solve our problem gracefully and compactly:
In the English version it will be =MAX(FREQUENCY(IF(B3:H3>0;COLUMN(B3:H3));IF(B3:H3=0;COLUMN(B3:H3))))
Let’s take this formula piece by piece:
- IF(B3:H3>0;COLUMN(B3:H3)) – checks that the cell was > 0 and displays its column number. For the first employee, we will get an array {2; FALSE; 4; 5; 6; FALSE; 8} at the output, which we then use as the range of initial data for the function FREQUENCY.
- IF(B3:H3=0;COLUMN(B3:H3)) – the same, but set from – we display the column numbers for non-working days. At the output, we get an array {FALSE;3;FALSE;FALSE;FALSE;7;FALSE}, which we then substitute into the function FREQUENCY as a range of intervals.
- And since we need the longest sequence, the function FREQUENCY we put inside functions MAX.
And do not forget to press the combination after entering the formula Ctrl+Shift+Enterto enter it as an array formula, of course.
As it is easy to see, a similar approach can be applied to any other similar problems of finding the longest possible sequences – just adjust the conditions and anti-conditions in the functions IF.
That’s all. It’s not that difficult, is it? 😉
- What are array formulas, what are they and how to enter them
- What is the FREQUENCY function and what is it used for
- Finding and counting the most frequent values with the FASHION function and pivot tables with grouping