re: counting consecutive numbers

Hello all!. I need to count consecutive numbers, but with a condition.

i suppose this would be done in one or two steps, the example spreadsheet is attached.

I have two columns.
one for temperature M26:M43826, the other is relative humidity N26:N43826.

Part1

What i want to do is,
for every value that is >=5 and <40 for M26:M43826 AND >=80 in column N26:N43826
should be designated a value 1 in cells Q26:Q43826

if either of those two criteria are not met, then a value of 0 should be assigned in Q26:Q43826

Part2

ask excel to count the number of maximum consecutive cells in Q26:Q43826 and insert into P:15.

I'll use a small section of the dataset to use as an example
lets say in the column Q27-Q34 we get the following values:. In P:15, the value should be 3
0
1
1
1
0
0
1
0

thanks for the help
-Bismo

O26 =--AND(M26>=5,M26<40,N26>=80) fill down ,now if you dont want your workbook to grind to a halt
copy column 0 paste back over itself special values to remove formulas!
then in p15
=MAX( FREQUENCY( IF( O26:O43826=1, ROW(O26:O43826) ), IF(O26:O43826<>1, ROW(O26:O43826) ) ) ) array entered
this gives 3 for the example given
ie rows
3543
3544
3545

works fine for me did you array enter it? with ctrl+shift+enter attached is a shorter sample

Originally Posted by martindwilson

works fine for me did you array enter it? with ctrl+shift+enter attached is a shorter sample

yes i did the array. (Ctrl D) i arrayed all 44000 or so cells.
this part works. it is part two that is the issue

when i opened your document. it seems to work okay using only 10 cells. when i altered the cell numbers to include the whole range (copied your forumula and pasted into my orginal document) and pasted into mine.

error. "the value in this formula is of the wrong datatype"
=MAX( FREQUENCY( IF( Q26:Q43826=1, ROW(Q26:Q43826) ), IF(Q26:Q43826<>1, ROW(Q26:Q43826) ) ) )


I would give you my original document, but the filesize is larger than 1mb allowance.
I am at a loss

here is the full version
you only array enter
=MAX( FREQUENCY( IF( Q26:Q43826=1, ROW(Q26:Q43826) ), IF(Q26:Q43826<>1, ROW(Q26:Q43826) ) ) ) with ctrl+shift+enter
so it looks like this in the formula bar

{=MAX( FREQUENCY( IF( Q26:Q43826=1, ROW(Q26:Q43826) ), IF(Q26:Q43826<>1, ROW(Q26:Q43826) ) ) )}
notice the curly brackets that appear when array entered