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 should be as a spill function.

Thanks a lot!

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.

9 Replies

Patrick2788

Silver Contributor

Nov 16, 2022

mlylyly

Try this one:

=LET(values,A2:A11,f,FILTER(ROW(values),values>0),SORT(LARGE(f,SEQUENCE(4))))

OliverScheurich
Gold Contributor
Nov 16, 2022
mlylyly

=IF(AND(IF(A3>0,ROW()-1,"")="",IF(A2>0,ROW()-1,"")<>""),IF(A2>0,ROW()-1,""),"")

You can try this formula which returns the expected result in my sheet.
EDIT: Simplified formula:
=IF(AND(A3<=0,A2>0),ROW()-1,"")
- mlylyly
  Copper Contributor
  Nov 16, 2022
  Thanks a lot! I need the output as a spill array though, any ideas on how to convert it to spill?
  - OliverScheurich
    Gold Contributor
    Nov 16, 2022
    mlylyly
    =BYROW(A2:A11,LAMBDA(x,IF(AND(OFFSET(x,1,0)<=0,x>0),ROW(x)-1,"")))
    If you have Office 365 or Excel 2021 or Excel online you can apply this formula.