Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining length of trend before significant retrace
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Length that falls within a length interval? | Excel Worksheet Functions | |||
Determining length of trend before significant retrace | New Users to Excel | |||
Determining length of trend before significant retrace | Excel Worksheet Functions | |||
Determining length of trend before significant retrace | Setting up and Configuration of Excel | |||
Determining error associated with polynomial trend lines. | Charts and Charting in Excel |