Scatter Plot with Continuous Y and Categorical X Variables

[karlruff]

I need a scatter chart where the y axis is a continuous variable and the x axis is a categorical variable with, say 5 different categories.
Example:
Y Axis: Horsepower
X Axis: "Really Fast", "Fast", "Medium", "Pretty Slow" and "Slow"
Datapoints would be car models: "Porsche 911", "VW bug", "Ford Taurus", etc.

Many thanks for your help- I'm at a loss.

B

 

[Damon Ostrander]

Hi karlruff,

Scatter charts don't support category-type axes. You have to switch to a Line chart. Don't worry about the implied idea that Scatter-type charts are for scatter plots (points only, no lines) while Line charts are for lines not points. Either type chart can handle points only, points and lines, and lines only by setting the chart series properties appropriately.

Damon

 

[karlruff]

Thanks Damon.

So is it possible to make a scatter chart (albeit within a line chart) that would plot this data? It seems like the problem with a line chart is that it wouldn't know to put the "Porsche 911" datapoint in the "Really Fast" zone of the x-axis on the chart.

Many thanks for your expertise
b

 

[Damon Ostrander]

Hi again karlruff,

Yes, Line Charts can't do a scatter chart in the sense of taking data in random order and figuring out where the data should plot on the abscissa (x) axis based on the x data values. It only sees the x-axis data as text and doesn't know that "Really Fast" is faster than "Fast". As a result it can only plot the x-axis data value in the order that the data are presented, one category per point.

So if you want your data to plot in a different order than presented you must first sort the data into the order you would like it to plot. In your case, perhaps you have a maximum speed column, and you can sort the rows into ascending max speed order. Then the points will plot on the x-axis in the order of their max speeds, and this would create "Fast" and "Really Fast" regions of the x axis, etc.

Keep Excelling.

Damon

 

[Jon Peltier]

Make a scale in a separate column, where 1=slow and 5=wicked fast. Use this as your X value. Plot the data. Use this technique to add your own custom axis labels:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

 

[karlruff]

Thx

Jon,
I'm sure you hear this all the time, but you are a genius. Thank you.
Ben

 

[seika7]

Jon,

I haven't been able to find your Arbitrary Axis article. Is it still available?

Thanks,

Bryan

Make a scale in a separate column, where 1=slow and 5=wicked fast. Use this as your X value. Plot the data. Use this technique to add your own custom axis labels:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html