Formula to find index of first value that meets a condition, starting from bottom of a range.

[TomCon]

I want to determine how many rows back (from bottom) of a range that you have to go, to find the first value that meets a condition (condition might be >, <, or =). The values in the range are not sorted.

So, if A1:A8 contains:
1, 10, 100, 45, 75, 200, 5, 88

These are the answers i want.
Condition: ">100". Answer wanted: 3 (3rd value from end (value=200) is the first value that is > 100)
Condition: "<100". Answer wanted: 1 (1st value from end (value=88) is the first value that is <100)
Condition: "=45". Answer wanted: 5 (5th value from end (value=45) is first value that is =45)
Condition: "<4". Answer wanted: 8 (8th value from end (value=1) is first value that is <4)

Is there an Excel formula that will return those answers?

Thank you.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

for >100
=MATCH(TRUE,N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) > 100,0)

for < 100
=MATCH(TRUE,N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) < 100,0)

for = 45
=MATCH(TRUE,N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) = 45,0)

for < 4
=MATCH(TRUE,N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) < 4,0)

 

[Ron Coderre]

With your sample data in A1:A8
and...

C1: >100
C2: <100
C3: 45
C4: <4

This regular formula, copied down, compares each cell in the range to the condition and returns the transition position

D1: =9-MATCH(0,COUNTIF(OFFSET($A$1:$A$8,{1,2,3,4,5,6,7,8},0),C2),0)

Is that something you can work with?

 

[TomCon]

Thanks for the help; really appreciate it. Both solutions work, but the one from Aladin is "more general" so will serve my purpose. I made the problem smaller for illustration, but as i use this formula i'll have alot more rows, so it would be hard to put a hard-coded array within the formula.

I would like to ask this, in way of trying to understand this formula and how it works. Do not need this for getting the solution to work (thats great!); it is just a way to attempt to increase my understanding!

I changed the input data slightly to make sure it worked for negative numbers, and if a number was repeated in the range. So, now the data in A1:A8 is: 1, -10, 100, -45, 5, 200, 5, 87.

I put it in Excel's stepwise "Evaluate formula" feature, and here is the stepwise output and my comments. Where i did not understand, i made the comment in red. If anybody could respond to these questions, or in some other way describe "how this formula works", would appreciate it very much!

1=MATCH(TRUE,N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) < 6,0) 'initial
2=MATCH(TRUE,N(OFFSET($A$1:$A$8,8-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) < 6,0) '8 rows
3=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))) < 6,0) '8-1=7
4=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-({1;2;3;4;5;6;7;8}-ROW($A$1)+1))) < 6,0) 'rows expanded as array
5=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-({1;2;3;4;5;6;7;8}-{1}+1))) < 6,0) 'row $A$1 is 1, but why is it returned as an array instead of a scalar value?
6=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-({0;1;2;3;4;5;6;7}+1))) < 6,0) 'performed array addition of -1, adds -1 to each element
7=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-{0;1;2;3;4;5;6;7}+1)) < 6,0) 'evaluated within ()
8=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-{1;2;3;4;5;6;7;8})) < 6,0) 'added scalar +1 to each element
9=MATCH(TRUE,N(OFFSET($A$1:$A$8,7,0,-({#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE }))) < 6,0) 'Why did #VALUE result?
10=MATCH(TRUE,N({#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE ;#VALUE }) < 6,0) 'Converted the OFFSET range to an array, all values in array remain as #VALUE
11=MATCH(TRUE,N((87;5;200;5;-45;100;-10;1} < 6,0) 'The range described by OFFSET is returned as the data in the range on the sheet, reverse order.
'Very hard to see how array of #VALUE values got back to worksheet and could pick up correct values
12=MATCH(TRUE,{87;5;200;5;-45;100;-10;1} < 6,0) 'Evaluated N() function
13=MATCH(TRUE,{FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,},0) 'Expression "<6" evaluated against each value in array
14=2 'MATCH finds position of first TRUE VALUE

Thanks much!

 

[Aladin Akyurek]

Thanks for the help; really appreciate it. Both solutions work, but the one from Aladin is "more general" so will serve my purpose. I made the problem smaller for illustration, but as i use this formula i'll have alot more rows, so it would be hard to put a hard-coded array within the formula.

I would like to ask this, in way of trying to understand this formula and how it works. Do not need this for getting the solution to work (thats great!); it is just a way to attempt to increase my understanding!

[…]

Book1
	A
1	1
2	-10
3	100
4	-45
5	75
6	200
7	5
8	87
Sheet1

1. OFFSET in the bit

OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))

uses the cell above the last cell, i.e. A7, as the reference cell and the height specs

-(ROW($A$1:$A$8)-ROW($A$1)+1)

in order to construct A1:A8 in the reverse order.


This ROW bit evaluates to (select it and hit F9 to see the result)...

{-1;-2;-3;-4;-5;-6;-7;-8}


A7 >> -1 means A8; A7 >> -2 means A8, A7; A7 >> -3 means A8, A7, A6, etc.


Note that ROW(reference) yields by design the array of the rows reference consists of.

Thus, ROW(A1) >> {1}; ROW(E7) >> {7}; ROW(E2:E4) >> {2;3;4}, etc. The COLUMNS() function behaves likewise.


If we select


OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1))

and apply valuator F9, we would possibly get:

{87;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

It looks here as if OFFSET cannot display the expected contents, but by wrapping the OFFSET expression into a function like N, it can be made to do so. The action amounts to a second round of evaluation:

N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1)))

Applying the F9 evaluator to this bit we get:

{87;5;200;75;-45;100;-10;1}

which is the desired reverse order.

Once the foregoing is obtained, a full formula like

=MATCH(TRUE,N(OFFSET($A$1:$A$8,ROWS($A$1:$A$8)-1,0,-(ROW($A$1:$A$8)-ROW($A$1)+1)))>100,0)

is easy to understand, using the F9 evaluator:

>>
=MATCH(TRUE,{87;5;200;75;-45;100;-10;1}>100,0)
>>
=MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},0)
>>
3

Hope this helps.