excel - how to count increasing profit streak?

Question

I want to count how many years a company has increased the profit from now and in a continuing streak.

Example here

In the picture it has to count until 2014 because the profits have not been increased relative to 2013. For steady profits, it has to count until 2012 because in 2011 the profits were higher than after that. So these are the two calculations that should be done. Import is that it shouldn't count rows of years where the profits were 0 - 0 - 0 - 0 (=steady, but 0 is no profit at all).

So it's like quality criteria to see how well a company is doing at its business. So if there is one interruption then everything before is irrelevant.

And the formulas should change dynamically, so if there will be data for 2018, 2019 ... then it should automatically count from the newest data. (there will be space between the formula column and the column for the latest year for more years)

Explanation for this new question: I needed a more flexible and complex solution for my problem and wanted to differentiate that in this question. So this may help others with similar problems.

Answer 1

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.