Showing text in the axis/Plotting non numerical data

[TOBY]

I have some credit ratings for a company and they are
changing over time. Credit ratings are in the form AAA,
AA, A, BBB, BB, B, CCC, CC, C, with AAA being the highest
and C the lowest. I want to plot how the company's rating
has changed over time, but the data is non numeric. I want
the Y axis to show the rating, the X axis to show monthly
time over 5 years say, and a line graph showing the
movement of the rating through time. Does anyone know how
to do this?

 

[Bernard Liengme]

Hi Toby,
Let us say your dates are in A2:A100, ratings in B2:B100.
In C2 enter =HLOOKUP(A2,{"AA","A","BB","B";4,3,2,1},2,FALSE) and copy down
the column
Expand this fomula to get more ratings - watch for the semicolon between the
letters and the numbers.
Plot A2:A100 against C2:C100 (selecte the A range, hold CTRL and selet the C
range)
OK, now we have a plot but the y-axis is labelled 1,2,3,4
With the chart seelcted, type AA and you get a text box with AA in it. Drag
this over the 4 on the y-axis, etc.

Best wishes
Bernard

 

[Jon Peltier]

Toby -

I have an example on my web site you could try. This is very similar to
Bernard's suggestion, except my method uses a dummy series to put the
axis labels right where you want them.

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

- Jon