ExcelBanter - Determining length of trend before significant retrace

Hi Douglas,

I'd love to see some sample data with the answers you would expect,
but here's a first try without getting any further info from you:

Function DirMvmnt(srcData As Range, Retrace As Long) As Long
Dim tmpVal As Long, MoveDir As Integer
Dim cell As Range

Set srcData = srcData.Columns(1)
tmpVal = srcData.Cells(2) - srcData.Cells(1)
DirMvmnt = tmpVal
MoveDir = Sgn(tmpVal)

For Each cell In srcData.Offset(2, 0).Resize(srcData.Cells.Count -
2, 1).Cells
tmpVal = cell - cell.Offset(-1, 0)
If Sgn(tmpVal) = MoveDir Then
If tmpVal DirMvmnt Then DirMvmnt = tmpVal
Else
If Abs(tmpVal) Abs(Retrace) Then Exit For
End If
Next
End Function

This is a UDF obviously that you stick in a module. The usage would
be: =DirMvmnt(A1:A100,25) if for example your values were listed in
column A and the retrace set at 25. I have made quite a lot of
assumptions in this. Firstly, I am assuming that the difference
between cell A1 and A2 is what sets the direction of movement. I.e. if
A1 = 2 and A2 = 10 then the initial movement is 8 in a positive
direction.
It will then compare the difference between A2 and A3. If that is in
the same direction as A1 - A2, then it looks to see if it is bigger
and if it is will then save that as the new Max Movement.
If it is in a different direction to A1 - A2, it will check to see if
the absolute value of the movement is bigger than the absolute value
of Retrace. If it is the function is finished and you have your
answer, if it isn't it then moves on to the next pair of cells.

I hope this explains my thinking. Let me know if I have it horribly
wrong.

Cheers,
Ivan.

On Apr 1, 12:15*pm, DouglasinHawaii
wrote:
In range of data I need to find jthe largest numerical movement in one
direction without (x) retrace. * (x in this case being a specified whole
number) *
Please help with a formula or direct me to where I could find this
information.
Using Excel 2007
For example: in range of numerical data, the largest directional integer
movement was 300 before the data moved 25 in reverse.
Example 2: *The largest directional integer movement was 224 before
the data moved 30 in reverse.