I have a table that looks like this:

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

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

CodePudding user response：

Use COUNTIFS with offset ranges.

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

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

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

CodePudding user response：

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
2. Simultaneously press ALT and F11 inside your workbook. This brings up the VBA Editor.
3. Head to Insert then Module
   
4. Paste the above code into the new module you just created
   
5. Close the editor
6. Use the newly-created formula that we just made