Calculating Rolling Peak to Valley in a Column
 

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


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=509006

Calculating Rolling Peak to Valley in a Column
 

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

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

Results a
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.
--
Kevin Vaughn


"tx12345" wrote:


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


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=509006