Showing text in the axis/Plotting non numerical data

  • Thread starter Thread starter TOBY
  • Start date Start date
T

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?
 
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
 
Back
Top