making a zig-zag line

  • Thread starter Thread starter Brad Reid
  • Start date Start date
B

Brad Reid

For a series of closing index prices like:

104, 104.28, 104.57, 104.9, 105.27, 105.5, 105.41, 104.26, 105.04, 104.86,
103.54, 103.35, 103.58, 103.63, 105.04
105.18, 105.4, 105.3, 105.99, 105.76, 105.84, 106.4, 105.66, 105.18, 105.15,
106.33...

arranged in a column, I would like to graph a zig-zag line connecting data
points where the second or next graphed data point (after 104 in this case)
always represents an increase or decrease of a minimum of some set
percentage, say 1%, so that all data points not qualifying are eliminated. A
zig up would mark the highest qualifying value encountered before any
subsequent zag down qualified for a marking based on a 1% decline in value
from the previous marked high and vice versa.

104, 105.50, 103.35 would represent the first 3 qualifying data points based
on this logic.

Can this be done using an Excel formula but no VBA? Thanks, Brad
 
Brad -

So you only want to chart a point if it is not within 1% of the last
plotted point? Here's a quick and dirty approach.

I left column A for your dates, and put your values in column B,
starting in B3. In cell C1 I put 1%, the threshold for including the
next point. I reserved row 2 for labels. In C3 I put the formula =B3,
and in C4 I put this formula, which returns the current value if it
varies by more than 1%, or the previous plotted value if it varies by less:

=IF(B4>(1+C$1)*C3,B4,IF(B4<(1-C$1)*C3,B4,C3))

Then I filled this down as far as column B had values. In cell D3 I put
this formula, which only shows a value if it's different from the prior
value; otherwise it shows #N/A, which Excel will ignore in the chart:

=IF(C4=C3,NA(),C4)

So the data looks like this (with my own dates and no labels in row 2):

1%

12/1/2003 104 104 104
12/2/2003 104.28 104 #N/A
12/3/2003 104.57 104 #N/A
12/4/2003 104.9 104 #N/A
12/5/2003 105.27 105.27 105.27
12/6/2003 105.5 105.27 #N/A
12/7/2003 105.41 105.27 #N/A
12/8/2003 104.26 105.27 #N/A
12/9/2003 105.04 105.27 #N/A
12/10/2003 104.86 105.27 #N/A
12/11/2003 103.54 103.54 103.54
12/12/2003 103.35 103.54 #N/A
12/13/2003 103.58 103.54 #N/A
12/14/2003 103.63 103.54 #N/A
12/15/2003 105.04 105.04 105.04
12/16/2003 105.18 105.04 #N/A
12/17/2003 105.4 105.04 #N/A
12/18/2003 105.3 105.04 #N/A
12/19/2003 105.99 105.04 #N/A
12/20/2003 105.76 105.04 #N/A
12/21/2003 105.84 105.04 #N/A
12/22/2003 106.4 106.4 106.4
12/23/2003 105.66 106.4 #N/A
12/24/2003 105.18 105.18 105.18
12/25/2003 105.15 105.18 #N/A
12/26/2003 106.33 106.33 106.33

Now select the dates, then hold down the Ctrl key while selecting the
data in column D (you must select the same size range in both columns)
and run the chart wizard.

- Jon
 
Jon, I'm plugging in your zig-zag approach as I write. Thanks!!! This is a
feature that used to be found in a stockmarket technical program I used to
operate, then when I switched to Excel and lost that ability. Thanks again,
Brad
 
Brad -

Glad it helps. I'm sure someone could come up with an approach which
doesn't need the intermediate column of formulas. But I like to keep
these in my own solutions, because it's easier to track where an error
may have crept into the chart.

- Jon
 
Back
Top