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

1 Month	Day	Vis	Snow	Temp
2November	22	1	2	-2
3 February	20	5	13	-10
4 February	21	4	7	-11
5 February	22	3	9	-12
6 February	23	5	42	-11
7 February	24	4	14	-11
8 February	25	5	10	-14
9 February	26	5	0	-16

<tbody>
</tbody>

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