Frequency Function: what exactly is it doing?

[DRSteele]

I wonder if someone can please help me understand how the function FREQUENCY operates here.

This algorithm counts the current streak of ONs and OFFs, counts the longest streaks of ONs and counts the longest streaks of OFFs. I just don't know how nor why it works.

Specifically, what goes on here? {=FREQUENCY(IF(B$5:B$14=1,ROW(B$5:B$14)),IF(B$5:B$14=0,ROW(B$5:B$14)))} reports this: {0;2;0;5}

Excel 2012

	A	B	C
Day	on/off

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Current Streak[/TD]
[TD="bgcolor: #FFF2CC"]On Streak[/TD]
[TD="bgcolor: #FFF2CC"]Off Streak[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #E2EFDA"]on 5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=LOOKUP(INDEX(B$5:B$14,COUNT(B$5:B$14)),{0,1},{"off","on"})&" "&
IF(COUNTIF(B$5:B$14,B$5)=COUNT(B$5:B$14),COUNT(B$5:B$14),MAX((B$5:B$14<>"")*ROW(B$5:B$14))-MAX((B$5:B$14<>INDEX(B$5:B$14,COUNT(B$5:B$14)))*(B$5:B$14<>"")*ROW(B$5:B$14)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(B$5:B$14=1,ROW(B$5:B$14)),IF(B$5:B$14=0,ROW(B$5:B$14))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(B$5:B$14=0,ROW(B$5:B$14)),IF(B$5:B$14=1,ROW(B$5:B$14))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Specifically, what goes on here? {=FREQUENCY(IF(B$5:B$14=1,ROW(B$5:B$14)),IF(B$5:B$14=0,ROW(B$5:B$14)))} reports this: {0;2;0;5}

Frequency function syntax
FREQUENCY(data_array, bins_array)

The data_array is generated by IF(B$5:B$14=1,ROW(B$5:B$14)) resulting in
{FALSE;6;7;FALSE;FALSE;10;11;12;13;14}

the bins_array is generated by IF(B$5:B$14=0,ROW(B$5:B$14)) resulting in
{5;FALSE;FALSE;8;9;FALSE;FALSE;FALSE;FALSE;FALSE}

So, ignoring the FALSE results, Frequency function results in
{0;2;0;5}
which means
0 numbers in data_array less than or equal 5 (first bin)
2 numbers in data_array (6 and 7) greater than 5 (first bin) and less or equal 8 (second bin)
0 numbers in data_array greater than 8 (second bin) and less or equal 9 (third bin)
5 numbers greater than 9 (last bin)

That's it!

M.

 

[V_Malkoti]

FREQUENCY function accepts two arguments:

Data - the range in which values must be counted.
Buckets - the list over which values must be grouped.

Now lets consider formula : {=FREQUENCY(IF(B$5:B$14=1,ROW(B$5:B$14)),IF(B$5:B$14=0,ROW(B$5:B$14)))}

Replacing output of IFs, this expands into
FREQUENCY( { ,6,7, , ,10,11,12,13,14}, {5, ,8,9, , , , , } )

So FREQUENCY counts:
number of values in Data <=5 : 0
number of values in Data >5 and <=8 : 2 (6 and 7)
number of values in Data >8 and <=9 : 0
number of values in Data >9 : 5 (10,11,12,13 and 14)

and thus the output array of {0;2;0;5}, counting number of consecutive 1s between each 0 in the range.

 

[DRSteele]

Marcelo Branco and V_Malkoti --- thank you both very much for your replies, which were clear and concise. I now understand!