Re: Re : Excel Range of Values Amidst Characteristic Transitions

TKT-Tang wrote...
....
1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
2. The values are interspersed with Blank Rows and the number of Blank
Rows (amidst transitions or without transitions thereof) varies
unevenly (as well).

Please devise a formula that will return an array consisting of the row
numbers corresponding to the transition of values within the given
range namely, {01, 07, 12, 17, 23, 27}.

Name your range LST and define these additional names:

LST.next
=INDEX(LST,2,1):INDEX(LST***!$1:$65536,MAX(ROW(LST))+1,COLUMN(LST))

seq
=ROW(LST)-MIN(ROW(LST))+1

Then you could generate a list of transitions with these formulas
beginning in D1.

D1 [array formula]:
=MIN(ROW(LST))

D2 [array formula]:
=SMALL(IF(ISNUMBER(LST.next)*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),
MMULT(--(seq>=TRANSPOSE(seq)),--ISNUMBER(LST)))=TRANSPOSE(seq)),
IF(ISNUMBER(LST),LST,0))<>LST.next),seq+1),ROWS(D$2:D2))

Fill D2 down as far as needed. This is more flexible because the column
D list can grow or shrink as needed. If you really want a single array
formula, with all the hassle and inflexibility it entails, you could
use the array formula

=SMALL(IF(ISNUMBER(LST.next)*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),
MMULT(--(seq>=TRANSPOSE(seq)),--ISNUMBER(LST)))=TRANSPOSE(seq)),
IF(ISNUMBER(LST),LST,0))<>LST.next)+(seq=MAX(seq)),MOD(seq,MAX(seq))+1),
ROW($A$1:INDEX($A:$A,1+SUM(ISNUMBER(LST.next)
*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),MMULT(--(seq>=TRANSPOSE(seq)),
--ISNUMBER(LST)))=TRANSPOSE(seq)),IF(ISNUMBER(LST),LST,0))<>LST.next)))))

.

Loading