Chart issue with Vertical Error bars past 21 plot points

**maacmaac** · 10-30-2010, 11:05 PM

I am using vertical error bars in a chart to seperate data points between calendar years (i.e. draws a line between December and January). The vertical bar works great up to 21 plot points. If I try and plot anything with 22 data points or higher, the vertical error bars get all messed up (the tick lines on secondary x-axis no longer line up with the tick lines of the primary x-axis, which I think is the reason for the problem).

I can't figure out why this is happening or how to solve. Any insight is appreciated. I have attached a sample spreadsheet that is more visual and should be clear after testing a different number of data points to plot. Thanks.

**teylyn** · 10-31-2010, 05:00 AM

Hello Maacmaac,

for some reason that I can't quite figure out, Excel adds one to the scale of the secondary X axis when you select a higher number.

As a workaround, you can use the following macro to ensure the secondary X axis scale has exactly the number of tick marks required.

Please Login or Register  to view this content.

(this goes into the sheet module)

Maybe Andy Pope will swing by and shed some light on this.

cheers,

**maacmaac** · 10-31-2010, 08:25 AM

The code you provided is going to work in the interim. Still not sure why it bugs out for 22 plot points or highter. I will leave thread open for now and hope someone can explain. Thanks again for workaround solution.

**Andy Pope** · 11-01-2010, 05:26 AM

The variation in the secondary x axis is just the result of the algorithm used to calculated maximum scale value based on the data and number of data points.

If you use a line chart range than an xy-scatter and increase the number of data points by 1 you can then set the Crosses between attribute and get the year markers to correctly appear on the chart.

Revise the named ranges used by the Year data series in the secondary axis.

SCROLL_LABEL_2: =OFFSET(XValues,0,-1,ROWS(XValues)+1,1)
SCROLL_DATA_2: =OFFSET(SCROLL_LABEL_2,0,3)

**teylyn** · 11-01-2010, 05:36 AM

Originally Posted by Andy Pope

The variation in the secondary x axis is just the result of the algorithm used to calculated maximum scale value based on the data and number of data points.

Andy, do you have any insights on how that algorithm works? Or why it would work differently for a Line chart than a Scatter plot?

Is it just one of those "Microsoft" things, or is there a logic behind it that the ordinary user (like me) can grasp?

cheers

**Andy Pope** · 11-01-2010, 05:45 AM

The difference between Line and xy-scatter is one is a category axis the other value. With category no algorithm is used to calculate min or max. Just the number of categories.

There is logic and it is based on the actual data.
I'm sure at one point I saw a MS document on the algorithm used but I can not locate it now.