Forum Discussion

mlylyly's avatar
mlylyly
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's avatar
    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.

OliverScheurich's avatar
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's avatar
    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's avatar
      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.

      • mlylyly's avatar
        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()?

Resources