Words on Scaled Chart Axis
Posted by Andrew Watson on January 07, 2002 11:11 PM
I have just been asked to chart a satisfaction survey in Excel.
The items surveyed were:
Tangibles
Assurance
Reliability
Responsiveness
Empathy
Overall Satisfaction
Each of these could be rated as:
1 Very Dissatisfied
2 Dissatisfied
3 Neutral
4 Satisfied
5 Very Satisfied
I have calculated the average rating for each item for all projects. Some items could have non-integer values.
I want a bar chart with the items on the horizontal axis and the ratings on the left hand axis. But instead of showing 1, 2, 3, 4, 5 I want to show the rating descriptions.
Does anyone have any suggestions how I might replace the numbers with words?
I thought I might have a look at custom number formats but at this stage I don't know how to do it.
I know I don't want to just write the labels manually. I want the system to calculate the correct position.
Andrew Watson
Posted by Mark W. on January 08, 2002 7:11 AM
Andrew, the Y-axis values can't be replaced with
descriptions, and a number format can't be
constructed for 5 ratings. I'd recommend that
you format your chart's data series to include
Data Labels showing their value. Once displayed
you can link these labels to a worksheet cell
corresponding to each your six categories. For
example, suppose cells A1:B4 contained...
{"Item","Rating"
;"Tangibles",4.5
;"Assurance",4.3
;"Empathy",3.2}
You could enter the formula...
=CHOOSE(ROUND(B2,0),"Very Dissatisfied","Dissatisfied","Neutral","Satisfied","Very Satisfied")
...into cell C2 and copy down. Now just select
the Data Label for "Tangibles", type an equal
sign (=), and click on cell C1. Repeat this
process for each of the 6 categories.
