Detecting where the sequence changes

Hi everyone,

Hopefully a simple problem!

I'm looking to find the first time a number changes in a sequence - this change could be +ve or -ve (hence my issue)

r7oumjbhQna3aUxg0YI_pw.png

As the screenie suggests, the answer I'm looking for is 215

Any questions pop em below

Ty

Mdn

Originally Posted by Richard Buttrey

Is a helper row 4 permitted or is this a very simple example and there's data in many rows.

Only a simple example

392u8Sd2Tz6E3PFdPdBdsw.png

Originally Posted by 63falcondude

Try this:

=INDEX(B2:KO2,MATCH(TRUE,A3:KN3<>B3:KO3,0)) Ctrl Shift Enter

or the non-CSE alternative:

=INDEX(B2:KO2,INDEX(MATCH(TRUE,A3:KN3<>B3:KO3,0),0))

Originally Posted by Richard Buttrey

...how about n array formula

Formula:

=INDEX(B2:KO2,1,MATCH(TRUE,A3:KO3<>B3:KP3,FALSE))

Damn, Falcondude beat me to it!

Originally Posted by Pete_UK

You have a sequence of 85 threes starting with cell A3, and no other cells after that which contain 3, so in that case you can use this formula in cell KQ3:

=INDEX(2:2,COUNTIF(A3:KO3,A3)+1)

If you do have any other cells with 3 in them after the first sequence, then this formula will produce erroneous results.

Hope this helps.

Pete

Top stuff all many many thanks

You have a sequence of 85 threes starting with cell A3, and no other cells after that which contain 3, so in that case you can use this formula in cell KQ3:

=INDEX(2:2,COUNTIF(A3:KO3,A3)+1)

If you do have any other cells with 3 in them after the first sequence, then this formula will produce erroneous results.

Hope this helps.

Pete

Glad we could help.

Thanks for the rep!