Calculating Rolling Peak to Valley in a Column

[tx12345]

Hi

This is another brain twister (for me)

Let's say I have a column of numbers:

2
3
4 < new high
3
2 < recent low since new high
2
3
5 < new high
6 < new high
5
4
4 < recent low since new high
6
7
9 < new high

etc

As each new high is made, there is the inevitable fall off to a lowest
point since the last high. Is there an efficient way to identify the
peaks, the recent lows off the peak, and then calculate the difference
as the column rolls on down?

Thx

Tx

 

[Guest]

This formula entered in B2 and copied down seems to work:

=IF(AND((A3>=A4),(A3>A2)),"new high",IF(AND((A3<=A4),(A3<A2)),"recent low
since new high",""))

Results are:
2
3
4 new high
3
2 recent low since new high
2
3
5
6 new high
5
4 recent low since new high
4
6
7
9 new high

Slightly different than yours, but yours had some inconsistencies.