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

[Kevin Vaughn]

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