Is there a way to evaluate for 0's in between 1's in Excel?

Viewed 165

I have a table that looks like this:

2018 2019 2020 2021
0 1 1 1
0 1 0 1
0 0 0 0
1 1 1 1
1 0 1 1
0 0 1 0
0 1 1 0
1 1 1 0

Trying to evaluate concurrent 1's (where no interruption in sequence). So output should look like:

2018 2019 2020 2021 Concurrent
0 1 1 1 True
0 1 0 1 False
0 0 0 0 False
1 1 1 1 True
1 0 1 1 False
0 0 1 0 False
0 1 1 0 False
1 1 1 0 False

Is there a way to evaluate rows where if a 1 is followed by 0 then false else True?

5 Answers

You could also use a spill formula in Excel 365:

=LET(concat,A2:A9&B2:B9&C2:C9&D2:D9,
ISERROR(FIND("10",concat))*ISNUMBER(FIND("1",concat))=1)

(this is only practical for a small number of columns)

enter image description here


The non-spill, non-let version would be:

=AND(ISERROR(FIND("10",TEXTJOIN("",,A2:D2))),ISNUMBER(FIND("1",TEXTJOIN("",,A2:D2))))

and this could be expanded to several columns.

Hereby another alternative for Excel2019 or higher:

enter image description here

Formula in E2:

=ISERROR(FIND(0,CONCAT(A2:D2)*1))

Or:

=ISERROR(FIND(0,--CONCAT(A2:D2)))

And with Microsoft365, you could opt to spill an array directly through:

=ISERROR(FIND(0,--MID(CONCAT(A2:D9),SEQUENCE(8,,,4),4)))

Or, if available:

=BYROW(A2:D10,LAMBDA(x,ISERROR(FIND(0,--CONCAT(x)))))

For previous versions of Excel I'd go with the answer given here.

Use COUNTIFS with offset ranges.

=COUNTIFS(A2:C2,1,B2:D2,0)=0

enter image description here

If the row must contain at least one 1, then perhaps:

=AND(COUNTIFS(A2:D2,1),COUNTIFS(A2:C2,1,B2:D2,0)=0)

enter image description here

Not sure how you feel about using VBA, but here's a solution.

First, we check to see if the final cell is a 0. If it is, then we automatically assume false:

If .Cells(.Cells.Count) = 0 Then Exit Function

Next, we will loop through each cell in the range, checking the current cell against the next cell. But before we do this, we do some pre-checks to ensure that the numbers are 'legal' (as in, they must be a 0 or a 1. Since I have the function returning a Boolean value, you will actually receive a #VALUE! error instead of the one I used (xlErrNum), but it doesn't break anything.

The loop is designed to grab all the False scenarios, so therefore if it passes the loop, it will return a true value.

Add this code to a VBA module:

Public Function TestForBreakage(rng As Range) As Boolean

    Dim i As Long
    With rng
        If .Cells(.Cells.Count) = 0 Then Exit Function              'Can't end w/ a 0
        For i = 1 To .Cells.Count
            If .Cells(i).Value < 0 Or .Cells(i).Value > 1 Then      '# other than 0 or 1 used
                TestForBreakage = CVErr(xlErrNum)
                Exit Function
            ElseIf .Cells(i).Value <> Int(.Cells(i).Value) Then     'Decimal used
                TestForBreakage = CVErr(xlErrNum)
                Exit Function
            End If
            If i = .Cells.Count Then Exit For
            If .Cells(i).Value > .Cells(i + 1).Value Then Exit Function 'Returns False
        Next i
    End With
    TestForBreakage = True  'If made through the loop unscathed then must be True

End Function

Since this didn't originate as a VBA question, just in case you are unfamiliar with VBA I will provide the steps to add the above code.

  1. Save your workbook as a Macro-Enabled Workbook enter image description here
  2. Simultaneously press ALT and F11 inside your workbook. This brings up the VBA Editor.
  3. Head to Insert then Module
    enter image description here
  4. Paste the above code into the new module you just created
    enter image description here
  5. Close the editor
  6. Use the newly-created formula that we just made
    enter image description here

If the logic is that the ones are unbroken from 2021 backwards, then another approach would be:

=AND(D2,SUM( --(FREQUENCY( IF(A2:D2,COLUMN(A2:D2)), IF(A2:D2,0,COLUMN(A2:D2)))>0) )=1)

Where 2021 is in column D. It looks for streaks of 1's starting from column D. If there are none or there is more than 1, it is false.

enter image description here

Related