vba - Count years of increasing profits

Question

I want to count how many years a company has increased the profit from now. So if the previous year had lower profits than this year it counts as 1 and goes one year back in past to count until there is one years profit higher than the year after that. So it must be a ongoing trend from this year (2017) so that it counts the years.

(Years of increases should be 5 not 4, sorry!)

In the picture it has to count until 2011 because there was a year(2012) without increasing profits relative to last year(2011).

So maybe its even possible with a Excel Function. Otherwise VBA is probably the way to do this.

EDIT: Is it possible to count years of steady profits, too? Like if 2015 = 5 and 2016 = 5 then this should not stop streak.

Answer 1

If you are looking for the streak of "increased/same profit" starting from current year, try the below formulas in the 2^nd row:
- in cell K2: =IF(B2>=A2,1,0)
- in cell S2: =IF(K2=1,A4+K2,K2)
- in cell J2: =Z2
then copy the cell K2 to the range L2:R2, cell S2 to the range T2:Z2, and then copy the range J2:Z2 to all the applicable rows as per below pic.

Note: instead of filling-up with formulas in rows 3 & 4, sure it can be done directly in cell J2 by way of Excel array, but for the sake of simplicity this method is preferable.

Edit: the above post was updated as per subsequent discussion with @technotoast to make the following adjustments:
- there are many companies in the list - not just "Apple"
- the streak will continue for same profit levels also - not just increasing profit