Analysing multiple values in an array

Hi all,

I have 2 arrays, one column is date and time stamps by the minute, the other column is an array of temperature values. I'm trying to calculate the time at which a temperature value is achieved. I've managed to do this using the index and match functions and an array formula.

However, I'd really like to only match the time stamp providing the temperature is steady for 10 continuous rows.

Is it possible to check the value of multiple cells in an array before returning a value?

For the record, the formula I'm using at the moment which checks a single cell is below. Time is Column A and Temp is in Column B

{=INDEX(A:A,MATCH(1,IF(B:B>=150,IF(B:B<=160,1)),0))}

Many thanks in advance.

Assuming by "steady" you mean within upper/lower threshold (i.e. no signal change) then perhaps something like below would work for you?

Formula:

Please Login or Register  to view this content.

modify ranges to suit but avoid entire column references as computationally expensive, and Volatile.
consider use of a non-volatile dynamic range, or Table construct

That looks like the sort of formula I need.
I’ll try it out later.

Many thanks.