individual data points and group mean

[jaime]

Hi - Need help creating a bar graph that shows group mean and
individual data points. I work in psychology - I have data from two
groups (a control group & patient group). I want to show results on a
bar graph - one bar for control mean and one bar for patient mean -
but then within the bar for patients - I want individual data points
for each patient.

 

[Del Cotter]

Hi - Need help creating a bar graph that shows group mean and
individual data points. I work in psychology - I have data from two
groups (a control group & patient group). I want to show results on a
bar graph - one bar for control mean and one bar for patient mean -
but then within the bar for patients - I want individual data points
for each patient.

Good for you for wanting to show the distribution as well as the
aggregate measures. However, Excel doesn't make it easy. To your bar
series showing the means, add a scatter series, using as X values the
number of the bars from left to right: "1" for points intended to place
with the first bar, "2" for points on the second bar, etc.

Having Paste Special-ed the data in and changed the Chart Type of the
series from Column to XY (Scatter), the data will be on Secondary X and
Y axes. Format the secondary Y axis so it matches the first, and the
Secondary X axis so it runs from a minimum of 0.5 to the number of bars
plus 0.5. e.g, if you have two bars, the scatter X axis goes from
0.5-2.5; if six bars, the scatter x axis goes from 0.5-6.5; and so on.

Now you should have a scatter of points (your patients) overlaid on bars
(the average of all patients).

 

[Jon Peltier]

It's easier than that.

Make a column chart with your two categories and two average values.

Put your actual values in one column, and in the column to the left of this
put either 1 or 2, depending which category it corresponds to. Copy this
data, select the chart, use Paste Special to add the data as a new series.

Select this added series (another column series with too many columns),
right click on it, choose Chart Type, and change it to an XY type. Then
assign this series to the primary axis, same as the initial column series.
Excel puts the XY data onto the primary axes without requiring you to
maintain two sets of axes.

- Jon