Forum Discussion

durendal's avatar
durendal
Brass Contributor
Nov 16, 2022
Solved

Counting consecutive cells equal to 0 at the end of the range

Hell everybody,

 

I am looking for a formula to count the consecutives values of 0 at the end of a line.

Let me show you an example with the expected result. Let's say the only two values in this table are 0 or 1:

 

I havn't been able to find a formula, especially with the fact that I don't want the maximum consecutive values of zero in the line, but only the consecutive values from the end of the line.

 

Thank you all

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Nov 16, 2022

    durendal 

    =BYROW(C3:N5,LAMBDA(row,12-LARGE(IF(row>0,COLUMN(C3:N3)-2),1)))

    With Office365 or Excel 2021 or Excel online you can try this formula.

    =12-LARGE(IF(C5:N5>0,COLUMN(C3:N3)-2),1)

    With older versions you Excel you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

     

3 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 16, 2022

    durendal 

    This is just a variation that uses XMATCH and names the Lambda function

    WorksheetFormula
= BYROW(array, FinalZerosλ);

FinalZerosλ 
= LAMBDA(row,
    COLUMNS(row) - XMATCH(TRUE, row>0, ,-1)
  );

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 16, 2022

    durendal 

    =BYROW(C3:N5,LAMBDA(row,12-LARGE(IF(row>0,COLUMN(C3:N3)-2),1)))

    With Office365 or Excel 2021 or Excel online you can try this formula.

    =12-LARGE(IF(C5:N5>0,COLUMN(C3:N3)-2),1)

    With older versions you Excel you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

     

Resources