How to add a vertical bar to a column chart to display AVERAGE val
 

I have a chart that summarizes survey responses:
- Y axis is the percent of total respondents
- X axis is the Likert scale responses, "Strongly Disagree (1)", thru
"Strongly Agree (7)".

I want to add a vertical bar that shows the average response (which the
chart would take from the spreadsheet).

For example, if the average was "5.5", I want the bar to be vertical, and
for it to cross the X axis where 5.5 would fall, between the "Somewhat Agree
(5)" and "Agree (6)" bars, as if the X axis were truly a numerical plotting
(as opposed to the text responses).

I suspect it is a secondary X axis without displaying the scale, but I
cannot seem to make it work. Help! Thanks VERY much!

Hi,

You correct a additional data series plotted as xy scatter on the
secondary axis will allow you to plot the average point. Using the Y
Error bar at 100% will give you a line.

Add a new data series to the chart and then change its chart type to
xy-scatter. Format the point to be on the secondary axis. You will need
to fix the secondary Y axis scale to range from 0 to 1.
You the chart options, right click chart to pick from menu, to enable
the secondary x axis. Again format this to scale from 0 to 1. The values
of the new data series should be Y fixed at 1 and the x value linked to
a cell that calculates the average percentage.
You can then add the error bar and set its format. The secondary scales
will also require formating to remove the axis labels.

Cheers
Andy

Nancy R Gross wrote:
I have a chart that summarizes survey responses:
- Y axis is the percent of total respondents
- X axis is the Likert scale responses, "Strongly Disagree (1)", thru
"Strongly Agree (7)".

I want to add a vertical bar that shows the average response (which the
chart would take from the spreadsheet).

For example, if the average was "5.5", I want the bar to be vertical, and
for it to cross the X axis where 5.5 would fall, between the "Somewhat Agree
(5)" and "Agree (6)" bars, as if the X axis were truly a numerical plotting
(as opposed to the text responses).

I suspect it is a secondary X axis without displaying the scale, but I
cannot seem to make it work. Help! Thanks VERY much!

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thank you SO MUCH! Now I just have to go back and fix about 200 charts.
I'll make this new chart into a set of user-defined custom charts so I can
easily slide through them. I'm delighted to have this help - thanks!

"Andy Pope" wrote:

Hi,

You correct a additional data series plotted as xy scatter on the
secondary axis will allow you to plot the average point. Using the Y
Error bar at 100% will give you a line.

Add a new data series to the chart and then change its chart type to
xy-scatter. Format the point to be on the secondary axis. You will need
to fix the secondary Y axis scale to range from 0 to 1.
You the chart options, right click chart to pick from menu, to enable
the secondary x axis. Again format this to scale from 0 to 1. The values
of the new data series should be Y fixed at 1 and the x value linked to
a cell that calculates the average percentage.
You can then add the error bar and set its format. The secondary scales
will also require formating to remove the axis labels.

Cheers
Andy

Nancy R Gross wrote:
I have a chart that summarizes survey responses:
- Y axis is the percent of total respondents
- X axis is the Likert scale responses, "Strongly Disagree (1)", thru
"Strongly Agree (7)".

I want to add a vertical bar that shows the average response (which the
chart would take from the spreadsheet).

For example, if the average was "5.5", I want the bar to be vertical, and
for it to cross the X axis where 5.5 would fall, between the "Somewhat Agree
(5)" and "Agree (6)" bars, as if the X axis were truly a numerical plotting
(as opposed to the text responses).

I suspect it is a secondary X axis without displaying the scale, but I
cannot seem to make it work. Help! Thanks VERY much!

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info