x-y scatter with alphabet scale

[vbacoder]

Hi everyone,

Please could anyone suggest a way to generate an X-Y scatter plot that has an alphabetical scale on the y-axis? The letters map to a numerical scale, so I really want the chart to ignore the letters in the plot (focusing instead on the number scale), but display it on the y-axis.

One way could be to plot two time series, one invisible, setting the range of the plot. The other timeseries is the one of interest, and a secondary axis then displays letters. So, scaled by invisible numbers and chart, but displaying letters on y-axis. Achieving this is not straightforward, and I am not even sure if it is really possible.

I'd be grateful to hear from anyone who may have tried something similar.

1:K
2:L
3:M
4:N
5:O


Thanks,

vcoder

 

[VoG]

This may help http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

 

[vbacoder]

Hi VoG,

Thanks for your reply. I was hoping not to use 3rd party software - I really want a solution in XL, even if it means having to write a bit of VBA.

vcoder

 

[vbacoder]

There's a way to add text to Max and Min levels for the y-axis here: http://support.microsoft.com/default.aspx?scid=kb;en-us;137528

... however, I would really like to assign text for all 'levels' of the scale, as in the example at the base of my first post.

vcoder

 

[vbacoder]

Interesting, with the method shown in the previous link, you cannot enter more than two numbers for converstion into text..

    Range("Chart").Select
    ActiveSheet.ChartObjects("Chart_1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.NumberFormat = "[=20] ""K"";[=19] ""L"""

...works, but adding one additional letter to the last line throws an 1004 runtime error: "Unable to set NumberFormat property of the TickLabels class"

Selection.TickLabels.NumberFormat = "[=20] ""K"";[=19] ""L""; [=18] ""M"""

So, basically it's not letting me assign more than two numbers to text - is there a way around this?

Please could someone offer assistance with this if you've seen this before?

Many thanks,

vcoder

 

[vbacoder]

Please, if anyone knows a way around this it would be greatly appreciated.

Thank you

vcoder