Incorrect x axis in ScatterGraph

[unclesnoop]

Hi All,

I've got a small problem with the axis labels not showing correctly on the scatter graphs.
For example i'll have the following values
(1,3), (2,4),(3,4) etc
and when i plot them the x axis is complete wrong, but when i hover over the point with my cursor the correct value is displayed.

Does anyone know a solution to this problem?

Regards,
Ben

[teylyn]

Hello,

can you please post a data sample in an Excel spreadsheet, including the chart that does not display as expected? It's hard to troubleshoot charting issues without seeing your actual setup.

In what way is the X axis "completely wrong"?

Check your data source definitions in the data source dialog box.

cheers,

[unclesnoop]

Hi teylyn,

Thanks for replying. I've attached a sample chart displaying my error.
The x axis labelling is incorrect to the source data. In the source data it shows the x values as 10/1, 10/2, 10/3 etc but when graphed they appear as 100,200,300!
I've also figured out that when you change the chart type to "Line graphs" the x axis labels are correct.

Thanks,
Ben

[unclesnoop]

Forgot to attach, lolol

[teylyn]

Hello,

your upper XY chart has two data series. One series has its X axis values in column H, the other one has the X axis values in column G. None of these columns are dates.

If you change the series source to point to column A (which carries dates), these dates will show in the X axis labels.

The lower chart, the line chart, draws its X axis categories from the column J, which has text values. These do resemble numbers, or dates in mm/dd format, but on closer inspection, I don't really know what to make of some values like 10/52 (October 52?)

So, long story short, for XY charts you need numeric data for both axes. Dates are numbers, so they will be accepted. Text strings like 10/52 are not numbers and will not make it into the native X axis labels in a XY chart.

There are workarounds, but I feel you need to straighten out what you want to chart before we get to that.

cheers,

[unclesnoop]

Hi teylyn,

Sorry i forgot to explain myself first. The 10/15 refers to 2010/week15, 11/20 refers to 2011/week20 etc.
It's a method that i've used to overcome a previous problem i had, as when i use the xvalues as pure week values week 52 of 2010 is before week 1 of 2011, but because 52>1 the week 52 value is graphed after week1 and hence distorts the graph. If that makes sense? haha? :D

If it's not too much of a hassle, would you know a solution to the above problem ? That would pretty much solve the problem i'm having now. I orignially thought that as long as the values of the x axis were in order it would not matter if it's text or number. But i realised im wrong!

[teylyn]

Hello,

change the data source definition for both XY chart series to point to

=Sheet1!$a$4:$a$326

instead of columns G and H.

Now they are spaced correctly on the X axis, according to their time line.

The next step is to create a column with the desired labels for the X axis. You already have something in place in column J, but it would be better to use a label only when the value changes.

So, change column J to a formula that starts in J4

=IF(TEXT(A4,"yy")&"/"&B4=TEXT(A3,"yy")&"/"&B3,"",TEXT(A4,"yy")&"/"&B4)

copy down.

Now column J only has a value when there is a change in the data. this can be used as the label for the XY chart.

Enter a 0 into cell K4 and copy all the way down your data table.

Now, add a new data series to the chart.

Select chart > Design > Select Data > Add

Series name = Xlabels
X values =Sheet1!$A$4:$A$326
Y values =Sheet1!$K$4:$K$326

This will add a data series to the chart. We will use this data series to create labels that sit where X axis labels normally are, but we will use this new series. The values of the new series sit on the 0 line of the Y axis, and we will use data labels for the data points, then hide the X axis labels.

Download and install the free (and immensely useful) XY Chart Labeler tool from here: http://www.appspro.com/Utilities/ChartLabeler.htm

Select the new data series and use the XY Chart Labeler tool to add J4:J326 as labels BELOW the data points.

It will look messy.

Format the chart X axis to not show any labels or tick marks.

It will still look messy, because the labels for the new chart series are overlapping. Either expand the chart width to make enough room for the labels or delete entries in column J, so not every data label shows.

After that, format the new data series to have no line and no markers to hide it.

See attached for all this in action.

cheers

[unclesnoop]

Hey teylyn,

I just followed your guide and i fixed the error!!!
Thank you very much! It was very easy to follow, and it looks great now! I will definitely send some rep your way
Once again, thanks for helping me out!

Ben

[lmausolf]

I have attached a copy of my file, there is a data tab and the chart tab. can you see my file?

Hello,

can you please post a data sample in an Excel spreadsheet, including the chart that does not display as expected? It's hard to troubleshoot charting issues without seeing your actual setup.

In what way is the X axis "completely wrong"?

Check your data source definitions in the data source dialog box.

cheers,[/QUOTE]

[arlu1201]

Lmausolf,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.