Zig zag oscillator formula

    Hi all,

    i am trying to find a zigzag formula in Excel that takes the minimum and highest prices in a series of data of a day-low and a day-high. i want it to generate only one column of prices that each number represents the period high and the period low after the price has changed by a given rate (i.e. +/-4%). I have put below sample of the data that i am currently working on. I hope that someone can help me out. Thank you in advance.

    Stelios

    DAY-HIGH DAY-LOW 11,880 11,575 11,606 11,160 11,067 10,825 11,138 10,883 11,382 11,121 11,269 10,920 10,959 10,823 11,170 10,828 11,206 10,975 11,394 11,194 11,355 11,034 11,134 10,932 11,258 10,970 11,072 10,907 11,142 10,911 10,899 10,810 11,041 10,924 11,292 11,085 11,308 10,990 10,960 10,772 10,988 10,841 11,216 10,927 11,563 11,209

    Re: Zig zag oscillator formula


    I looked up what was meant by a zig-zag formula. It is not clear to me how you want to combine HIGH-LOW pairs into a single value though. The attached workbook contains the following formulas:

    Base
    G1: {=INDEX($E$1:$E$24,MAX(NOT(ISNA($E$2:$E2))*ROW($E$2:$E2)))} - array formula committed with <ctrl><shift><enter>

    % Change
    H2: =(($D3-G2)/G2) - This is just for confirmation. Not required in final result.

    Zig-zag
    D2: =$B$2 - First cell of Zig-zag has to be started with a known value. So take the first from a series.
    D3: =IF(AND(-$I$2<=(($B3-$F2)/$F2),(($B3-$F2)/$F2)<=$I$2),NA(),$B3) - Compare Base to current series value and see if it is between +/- of %. If it is return series value otherwise return #N/A

    I have created a chart to plot these values. 4% produced an uninteresting result so currently the Worksheet is showing 2.25%. You can change this by changing the value of I2.

    Re: Zig zag oscillator formula


    Hi Rob,

    Thank you for your reply and for pulling it into an excel. This is really excellent and quite close to what i am looking for. Perhaps, i was not very clear in my previous post. I am trying to connect the higher high with the lower lows. Please find attachemnt with the file you created in which i have put the zig zag values i need in column E, which are also illustrated in a chart.

    Thanks,
    Stelios

    Re: Zig zag oscillator formula


    Well after a bit more formula wrangling...
    =IF($D28<$D27,IF($D29<$D28,NA(),IF(ABS($K28)>$M$27,$D28,NA())),IF($D29>$D28,NA(),IF(ABS($K28)>$M$27,$D28,NA())))

    Seems to do the trick. Although to exactly match your example I had to use 4.3%. Its not a very mathematically elegant solution so I may have a muddle over it again at some point if it grabs my fancy. However it works.

    Re: Zig zag oscillator formula


    Hi Rob,

    This is exactly what i was looking for and it seems to work very well in bigger amounts of data too.
    Great work. I really appreciate you help.

    Stelios

    Re: Zig zag oscillator formula


    Quote from Rob Xaos;525121

    Well after a bit more formula wrangling...
    =IF($D28<$D27,IF($D29<$D28,NA(),IF(ABS($K28)>$M$27,$D28,NA())),IF($D29>$D28,NA(),IF(ABS($K28)>$M$27,$D28,NA())))

    Seems to do the trick. Although to exactly match your example I had to use 4.3%. Its not a very mathematically elegant solution so I may have a muddle over it again at some point if it grabs my fancy. However it works.


    this is quite accurate! however i haven't been able to drag this along for a larger data set as this is an array. any suggestions?

    Re: Zig zag oscillator formula


    Please don't post questions in threads started by other members.


    Start your own thread, give it a clear and concise title and explain your issue fully. If you think this thread can help explain your issue, you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login