Count number of cells upward, where price is lower than current cell

[loudnoiseman]

Hi Everyone,

Sorry if this seems confusing.

But instead of looking downward, I want the formula to look upward and return a count of the number of cells for the first instance where the price drops below current cell.

In this example, if current cell is 40, then the answer would be 5, as 34 is 5 cells above current cell price 40.

Thanks!!

50
49
48
47
46
45
34
44
43
42
41
40

 

[Cubist]

Try:

Book1
	A	B
1
2	50
3	49
4	48
5	47
6	46
7	45
8	34
9	44
10	43
11	42
12	41
13	40	5
Sheet4

Cell Formulas
Range	Formula
B13	B13	=ROWS(A2:A13)-XMATCH(TRUE,A2:A13<A13,,-1)

 

[shaowu459]

工作簿1
	A	B	C	D
1	50
2	49
3	48
4	47
5	46
6	45
7	34
8	44
9	43
10	42
11	41
12	40		5
13
Sheet1

Cell Formulas
Range	Formula
C12	C12	=ROW(A12)-XMATCH(1=1,A1:A12<A12,,-1)