Calculating Rolling Peak to Valley in a Column

  • Thread starter Thread starter tx12345
  • Start date Start date
T

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
 
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.
 
Back
Top