count consecutive increases from last value

[fprzekop]

I'm trying to come up with an array formula to count the consecutive declines or increases from the last value in a column, going from bottom to top. The real world application is a list of daily stock prices down a column, and counting from the most recent day, how many consecutive days the price declined or increased.

I got some hints in the following post but can't get it evaluate the resulting logical array in reverse order (which is needed since column addresses are evaluated top to bottom, not bottom to top).
http://www.mrexcel.com/forum/showthread.php?t=93203&highlight=count+consecutive+increases

Example for consecutive increases leading to last value
49
50
51
52
53
49
55
56
I need a formula to return 2, the number of increases from 49 to 56

Thanks all in advance

 

[shg]

If your data is in A2:A9, then use this formula, which MUST be confirmed with Ctrl+Shift+Enter:


=LOOKUP(9E+307, FREQUENCY(IF(A3:A9>A2:A8, ROW(A3:A9)), IF(A3:A9 <= A2:A8, ROW(A3:A9))))

 

[fprzekop]

SHG... perfect and elegant solution. I haven't quite digested the logic yet but it works perfectly. Can you describe the basic logic employed with your use of the frequency function? regardless, thanks much

 

[shg]

It's derivative to the formula you use to count runs of a number, say, 1:

=FREQUENCY(IF(A1:A10=1, ROW(A1:A10)), IF(A1:A10<>1, ROW(A1:A10)))

That generates a frequency array where the row numbers where 1 appears are the data, and the row numbers where something other than 1 appears are the bins; so each bin captures the length of the preceeding run of 1's.

In this

=LOOKUP(9E+307, FREQUENCY(IF(A3:A9>A2:A8, ROW(A3:A9)), IF(A3:A9 <= A2:A8, ROW(A3:A9))))

... the row numbers where the value is greater than the preceeding row is the data, and the row numbers where they aren't are the bins. The LOOKUP function captures the last value in the resulting array.

 

[fprzekop]

Love it. Thanks so much for the clear explanation.

 

[shg]

You're welcome, good luck.