individual data points and group mean

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.

On Sun, 13 Jul 2008, in microsoft.public.excel.charting,
jaime <doyle.jaime@gmail.com> said:
>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).

--
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
which goes to a spam folder-- please send your email to del3 instead.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" <del@branta.demon.co.uk> wrote in message
news:NvZ+ONEkBoeIFwJh@branta.demon.co.uk...
> On Sun, 13 Jul 2008, in microsoft.public.excel.charting,
> jaime <doyle.jaime@gmail.com> said:
>>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).
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> del@branta.demon.co.uk,
> which goes to a spam folder-- please send your email to del3 instead.