Chart does not render plotted points correctly, data comes from a

[Rogerph100]

We have an Excel 2003 front end workbook that draws data from a SQL 2005
"Cube" into a pivot table with an associated pivot chart.
The values on the line chart, as shown e.g. in data labels, are correct
against the pivot table, however in some cases the plotted points on the
chart are way out on the scale - a value of -168 is plotted somewhere between
-220 and -250 against the scale, and in other cases where negative values are
below the X axis, -3 is plotted below -5 although the scale is rising, i.e.
has 0 above -1.
We have tested lots of different scenarios and most of them are fine with
correct plotting but just a few of them are incorrect.
Can anyone help please?
Thanks

 

[Tushar Mehta]

One possible cause is that you are plotting a Line Chart rather than a
XY Scatter chart. Though the results of the two look similar, a Line
chart has a Category X axis, wherein each data point is equidistant
from the previous.

The other possibility is that to Excel some of the data are text
values rather than numeric. You can test this with the formula
=ISNUMBER(B2) where B2 contains the suspect data value.

A quick way to fix this is to copy an empty cell (a cell with nothing
in it, not even a formula that yields an empty looking cell), select
the problem cells, right-click, select Paste Special..., and in the
Operation section select Add.

On Tue, 7 Oct 2008 03:45:01 -0700, Rogerph100

We have an Excel 2003 front end workbook that draws data from a SQL
2005
"Cube" into a pivot table with an associated pivot chart.
The values on the line chart, as shown e.g. in data labels, are
correct
against the pivot table, however in some cases the plotted points on
the
chart are way out on the scale - a value of -168 is plotted somewhere
between
-220 and -250 against the scale, and in other cases where negative
values are
below the X axis, -3 is plotted below -5 although the scale is rising,
i.e.
has 0 above -1.
We have tested lots of different scenarios and most of them are fine
with
correct plotting but just a few of them are incorrect.
Can anyone help please?
Thanks

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products

 

[Rogerph100]

Tushar,
Thank you for that - I am not in the office for a few days but will check
out your suggestion.

Regards,
Roger

 

[Rogerph100]

Tushar,

Thank you for your post which sent us off on two significant trails - one
led to success. The Chart type was set to "stacked line" and as soon as we
changed that to LINE everything resolved correctly.

regards,

Roger Phillips