[Solved] how to count increasing profit streak?

Question

To calculate Years of Increases enter the following formula in Cell L2

=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,"")))),0)

and to calculate Years of Steady Profits enter below formula in Cell M2

=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,"")))),0)

Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference.

In case you want this formula to be dynamic i.e. after adding new column for another year you want formula to work correctly then consider the following.

In Column K enter some dummy character say x and then enter the following formula in Cell L2

=IFERROR(COLUMN(K2)-COLUMN(INDEX(B2:K2,,MATCH(9.99E+307,IF(B2:K2>A2:J2=FALSE,1,""))))-1,0)

and in Cell M2

=IFERROR(COLUMN(K2)-COLUMN(INDEX(B2:K2,,MATCH(9.99E+307,IF(B2:K2>=A2:J2=FALSE,1,""))))-1,0)

Both formulas are array formula. Drag/Copy down as required. See image for reference.

Now when you select Column K and insert new column, formulas will change accordingly.

Notice formula (formula bar) in the image below.

EDIT : Avoid counting streak of zeros 0-0-0-0

For Years of Steady Profits use following formula in Cell M2

=IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(((B2:J2>=A2:I2)*(B2:J2<>0))=0,1,"")))),0)

This is an array formula.

VBA Solution :

Option Explicit

Sub Demo()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim lastRow As Long, lastCol As Long, rIndex As Long, cIndex As Long
    Dim increaseCnt As Long, steadyCnt As Long
    Dim ws As Worksheet
    Dim isSteady As Boolean, isZero As Boolean

    Set ws = ThisWorkbook.Worksheets("Sheet2")  'change to your data sheet
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    'last row with data using Column A
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column 'last column with data using Row 2
        increaseCnt = 0
        steadyCnt = 0
        isSteady = False

        For rIndex = 2 To lastRow               'loop through row 2 to last row
            For cIndex = lastCol To 2 Step -1   'loop through last column to column 2
                If .Cells(rIndex, cIndex) <> "NA" Then  'check for NA
                    If .Cells(rIndex, cIndex) <> 0 Then 'cheeck for 0
                        If .Cells(rIndex, cIndex) = .Cells(rIndex, cIndex - 1) Then 'compare cells for steady count
                            steadyCnt = steadyCnt + 1       'increment steadyCnt
                            isSteady = True                 'set steady flag true
                        ElseIf .Cells(rIndex, cIndex) > .Cells(rIndex, cIndex - 1) Then 'compare cells for increase count
                            If Not isSteady Then
                                increaseCnt = increaseCnt + 1   'increment increaseCnt
                                steadyCnt = steadyCnt + 1       'increment steadyCnt
                            ElseIf .Cells(rIndex, cIndex) <> 0 Then 'check for cell is 0
                                steadyCnt = steadyCnt + 1       'increment steadyCnt
                            End If
                        Else
                            Exit For                'exit for loop
                        End If
                    Else
                        Exit For                    'exit for loop
                    End If
                End If
            Next cIndex
            .Cells(rIndex, lastCol + 2) = increaseCnt   'display increaseCnt
            .Cells(rIndex, lastCol + 3) = steadyCnt     'display steadyCnt
            increaseCnt = 0
            steadyCnt = 0
            isSteady = False
        Next rIndex
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

See image for reference.

Accepted Answer