+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Nancy R Gross
    Guest

    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!

  2. #2
    Andy Pope
    Guest

    Re: How to add a vertical bar to a column chart to display AVERAGEval

    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

  3. #3
    Nancy R Gross
    Guest

    Re: How to add a vertical bar to a column chart to display AVERAGE

    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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1