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 daylow and a dayhigh. 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
DAYHIGH DAYLOW 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
Zig zag oscillator formula
 ergostelios
 Closed


Re: Zig zag oscillator formula
I looked up what was meant by a zigzag formula. It is not clear to me how you want to combine HIGHLOW 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: =(($D3G2)/G2)  This is just for confirmation. Not required in final result.
Zigzag
D2: =$B$2  First cell of Zigzag 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
Welcome. Was kind of interesting to work on. As I say, it's not very mathematically elegant. I may find a tidier way to achieve it. Will post here if I do.

Re: Zig zag oscillator formula
Quote from Rob Xaos;525121Well 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!