XY Scatter Plot - Change Labels From Numeric To Text Programmatica

[Guest]

Hello,
Let's say I have data like this:

A 1
A 2
A 3
B 4
B 2
C 1
C 5
C 0

I want to create an XY scatter plot where the values in the first column are
the X-Axis and the values in the second column are values for the Y-Axis.

Right now, I can only get numeric labels for the X-Axis. I want the labels
to be text, namely A, B and C

How can I do this *programmatically* in VB.NET or C#? I know there are Excel
tool add-ins that allow this to be done manually, but I am doing some
automation.

My only 2, non-optimal, solutions thus far are:
1. To hide the numeric labels and manually try to place the text labels via
text boxes using the size of the chart and other funky things to create an
algorithm. Very ugly
2. To use a line chart, not show the lines and treat each row as a series.
This works well until I get over 255 data points then I run into the 255
series limit in Excel 2003 (and not fixed in 2007). Then I get into the
situation where I have to create multiple worksheets and charts. Again,
better than (1), but still ugly.

Any help would be appreciated.

Thanks.

 

[Bernard Liengme]

Assuming your data is JUST as you show it, there is no way to make an XY
chart of this,
an XY chart needs pairs of numeric data (like the graphs you made in Grade 9
algebra - if you are North American) You have what is called "category data"
for you x-values.
I think you need a Line chart
best wishes

 

[Guest]

You can create a XY Scatter with that data; it just treats the text values as
a series and assumes a 1, 2, 3, 4, 5... type of X-Axis.

What I want is to have the text actually be the labels of the X-Axis.

Say I had

1 1
1 2
1 3
2 4
2 1
3 0
3 5

You can easily create an XY Scatter out of that. Now pretend 1 = A, 2 = B, 3
= C....that's what I want.

 

[Bernard Liengme]

Then use a LINE chart.

 

[Guest]

Right. That is exactly how I do it now. See my solution (2) in my original
post.
But I run into the Series limit problem because in order to get a scatter
look from a line chart, you have to treat each row as a series. That's
non-optimal. I know there has to be a way to do it programmatically with a
scatter plot -- I mean add-ins have been made to allow you to do it manually.

 

[Kelly O'Day]

You can use easily use a custom axes (aka dummy axis series) that will
display the A, B, C labels that you are trying to place on the X Axis.

Here's a link to an overview of custom axes for XY charts.

http://processtrends.com/pg_charts_custom_axis.htm

I have several examples: day of year, flexible log, dotplot (Y axis with
alpha labels).

Jon Peltier, Tushar Mehta and Andy Pope also have pages on dummy axis
series.

Kelly

http://processtrends.com

 

[Jon Peltier]

Insert a column which provides numerical equivalents (e.g., 1, 2, 3 for A,
B, C). Start with a column chart with this data:

A 0
B 0
C 0

This gives you the labels A, B, C across the X axis. Now add your data

1 1
1 2
1 3
2 4
2 1
3 0
3 5

as a new series, and change it to an XY chart. Then force it back onto the
primary axis. 1 will align with A, 2 with B, etc.

- Jon

 

[Guest]

Jon,
Thanks for the reply.
Could you go into a little more detail? I am trying to do this manually in
Excel. I easily created the column chart and got the A, B, C on the X-Axis,
but when I tried to add a new series (Series 2), and select the "real" data
to be part of that, I got an error that said "The reference is not valid.
References for titles, values, or sizes must be single cell, row or column".

What am I missing?

Thanks a lot.

 

[Jon Peltier]

Start with the column chart. Copy the data for the XY series data (both
columns), select the chart, go to Edit menu > Paste Special, and add the
data as a new series, categories in first column. This will temporarily mess
up the chart, but don't worry. Select the added series, go to Chart menu >
Chart Type, and select an XY chart type. Excel puts the XY series onto the
secondary axis; double click the XY series, and on the Axis tab, select
Primary.

- Jon