Counting consecutive numbers greater than a set value

[Bcapcook]

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

 

[lrobbo314]

Try this.

=MAX(FREQUENCY(IF(D2:D9>=10,ROW(D2:D9)),IF(D2:D9<10,ROW(D2:D9))))

This is an array formula so hit Ctl+Shift+Enter when entering the formula.

 

[Bcapcook]

Try this.

=MAX(FREQUENCY(IF(D2:D9>=10,ROW(D2:D9)),IF(D2:D9<10,ROW(D2:D9))))

This is an array formula so hit Ctl+Shift+Enter when entering the formula.

That was done, just didn't copy and paste with the brackets. Regardless it didn't work

 

[lrobbo314]

It worked on the data in your original post. The formula I posted doesn't have 10 in quotations, whereas your original post did. Did you try it without the quotes?

 

[MARK858]

Regardless it didn't work

Are you sure that you entered the formula with Ctl+Shift+Enter (and the curly brackets appeared) as the formula lrobbo314 posted works fine for me.
See formula/result in F2

Excel 2010
	A	B	C	D	E	F
1	1 Month	Day	Vis	Snow	Temp
2	02-Nov	22	1	2	-2	3
3	03-Feb	20	5	13	-10
4	04-Feb	21	4	7	-11
5	05-Feb	22	3	9	-12
6	06-Feb	23	5	42	-11
7	07-Feb	24	4	14	-11
8	08-Feb	25	5	10	-14
9	09-Feb	26	5	0	-16
Sheet1

Cell Formulas
Range	Formula
F2	{=MAX(FREQUENCY(IF(D2:D9>=10,ROW(D2:D9)),IF(D2:D9<10,ROW(D2:D9))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Bcapcook]

It worked on the data in your original post. The formula I posted doesn't have 10 in quotations, whereas your original post did. Did you try it without the quotes?

Being new to this I over looked the changes to the formula thought you were just suggesting the array.

Your formula works wonderfully .

Thank You