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

edit: formula for the better explained requirements in comments (see previous versions in history):

=COLUMN(I2) - MAX(($C2:I2 < $B2:H2) * COLUMN($C2:I2), COLUMN($B2))

Answer 2

I couldn't see an easy way of doing this other than by working backwards through the year using an offset of 0, -1, -2 etc. from the last one and searching for the first instance where the profit for the later year is not greater than the profit for the earlier year. If there is no such instance, then all years show an increasing profit.

=IFERROR(MATCH(FALSE,N(OFFSET($I2,0,COLUMN($B2)-COLUMN($B2:$H2),1,1))>N(OFFSET($H2,0,COLUMN($B2)-COLUMN($B2:$H2),1,1)),0)-1,"All in profit")

This has to be entered as an array formula using CtrlShiftEnter

Answer 3

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

Answer 4

You can use the below array formula, if you are looking for number of time the profit is increased starting from current year. Enter the below formula and press Shift+Control+Enter. The last -1 is to align this with your answer. If you want 5 instead of four, you can remove that -1.

=COUNT(B2:H2)-MAX(IF(C2:I2<B2:H2,COLUMN(C2:I2)-COLUMN(B2)))

by using this, with only one formula you can achieve what you want.