I am tracking snow data for skiing. In column D I have the snow that fell in the last 24hrs. I want to count how many consecutive times the value over 10 occurs in the column.
I thought I had it with =MAX(FREQUENCY(IF(D2:D153,">10",ROW(D2:D153)),IF(D2:D153,">10",ROW(D2:D153)))))
A B C D E
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl23"]1 Month[/TD]
[TD="class: xl24, width: 64"]Day[/TD]
[TD="class: xl24, width: 64"]Vis[/TD]
[TD="class: xl24, width: 64"]Snow[/TD]
[TD="class: xl24, width: 64"]Temp[/TD]
[/TR]
[TR]
[TD="class: xl25"]2November[/TD]
[TD="class: xl22"]22[/TD]
[TD="class: xl26"]1[/TD]
[TD="class: xl26"]2[/TD]
[TD="class: xl26"]-2[/TD]
[/TR]
[TR]
[TD]3 February[/TD]
[TD="class: xl22"]20[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]13[/TD]
[TD="class: xl22"]-10[/TD]
[/TR]
[TR]
[TD]4 February[/TD]
[TD="class: xl22"]21[/TD]
[TD="class: xl22"]4[/TD]
[TD="class: xl22"]7[/TD]
[TD="class: xl22"]-11[/TD]
[/TR]
[TR]
[TD]5 February[/TD]
[TD="class: xl22"]22[/TD]
[TD="class: xl22"]3[/TD]
[TD="class: xl22"]9[/TD]
[TD="class: xl22"]-12[/TD]
[/TR]
[TR]
[TD]6 February[/TD]
[TD="class: xl22"]23[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]42[/TD]
[TD="class: xl22"]-11[/TD]
[/TR]
[TR]
[TD]7 February[/TD]
[TD="class: xl22"]24[/TD]
[TD="class: xl22"]4[/TD]
[TD="class: xl22"]14[/TD]
[TD="class: xl22"]-11[/TD]
[/TR]
[TR]
[TD]8 February[/TD]
[TD="class: xl22"]25[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]10[/TD]
[TD="class: xl22"]-14[/TD]
[/TR]
[TR]
[TD]9 February[/TD]
[TD="class: xl22"]26[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]-16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl22"][/TD]
[TD="class: xl22"][/TD]
[TD="class: xl22"][/TD]
[TD="class: xl22"][/TD]
[/TR]
</tbody>[/TABLE]
In the above the formula would count D6:D8 giving a value of 3. Thus in column D, 10 14, and 42 are counted because they have a value of 10 and higher consecutively.
Thanks in advance
Byron
I thought I had it with =MAX(FREQUENCY(IF(D2:D153,">10",ROW(D2:D153)),IF(D2:D153,">10",ROW(D2:D153)))))
A B C D E
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl23"]1 Month[/TD]
[TD="class: xl24, width: 64"]Day[/TD]
[TD="class: xl24, width: 64"]Vis[/TD]
[TD="class: xl24, width: 64"]Snow[/TD]
[TD="class: xl24, width: 64"]Temp[/TD]
[/TR]
[TR]
[TD="class: xl25"]2November[/TD]
[TD="class: xl22"]22[/TD]
[TD="class: xl26"]1[/TD]
[TD="class: xl26"]2[/TD]
[TD="class: xl26"]-2[/TD]
[/TR]
[TR]
[TD]3 February[/TD]
[TD="class: xl22"]20[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]13[/TD]
[TD="class: xl22"]-10[/TD]
[/TR]
[TR]
[TD]4 February[/TD]
[TD="class: xl22"]21[/TD]
[TD="class: xl22"]4[/TD]
[TD="class: xl22"]7[/TD]
[TD="class: xl22"]-11[/TD]
[/TR]
[TR]
[TD]5 February[/TD]
[TD="class: xl22"]22[/TD]
[TD="class: xl22"]3[/TD]
[TD="class: xl22"]9[/TD]
[TD="class: xl22"]-12[/TD]
[/TR]
[TR]
[TD]6 February[/TD]
[TD="class: xl22"]23[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]42[/TD]
[TD="class: xl22"]-11[/TD]
[/TR]
[TR]
[TD]7 February[/TD]
[TD="class: xl22"]24[/TD]
[TD="class: xl22"]4[/TD]
[TD="class: xl22"]14[/TD]
[TD="class: xl22"]-11[/TD]
[/TR]
[TR]
[TD]8 February[/TD]
[TD="class: xl22"]25[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]10[/TD]
[TD="class: xl22"]-14[/TD]
[/TR]
[TR]
[TD]9 February[/TD]
[TD="class: xl22"]26[/TD]
[TD="class: xl22"]5[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]-16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl22"][/TD]
[TD="class: xl22"][/TD]
[TD="class: xl22"][/TD]
[TD="class: xl22"][/TD]
[/TR]
</tbody>[/TABLE]
In the above the formula would count D6:D8 giving a value of 3. Thus in column D, 10 14, and 42 are counted because they have a value of 10 and higher consecutively.
Thanks in advance
Byron