Forum Discussion

Copper Contributor

Nov 16, 2022

Solved

Identify position of last positive value in range from positive values in a row

Hi, I am looking for a way to identify every position of a value which is the last positive value in a row. As an example below, I want to identify which row the yellow cell. The output in C2:C4 ...

excel

Formulas and Functions

OliverScheurich
Nov 16, 2022
mlylyly
=LET(u,A2:A11,FILTER(ROW(u)-1,(u>0)*(OFFSET(u,1,0)<=0)))
You can try this LET formula.

mlylyly

Copper Contributor

Nov 16, 2022

I unfortunately don't have access to LAMBDA function yet due to bi-annual release - any way to solve it with e.g. LET()?

OliverScheurich

Gold Contributor

Nov 16, 2022

mlylyly

=LET(u,A2:A11,FILTER(ROW(u)-1,(u>0)*(OFFSET(u,1,0)<=0)))

You can try this LET formula.

mlylyly
Copper Contributor
Nov 17, 2022
OliverScheurich

Thanks! Is there a way to make it independent of the cell position? E.g., if I move the function one cell down then the results increase by one. Any thoughts?
- mlylyly
  Copper Contributor
  Nov 17, 2022
  mlylyly
  
  I think I found a solution below