On Wed, 30 May 2007, in microsoft.public.excel.charting,
lenos <(E-Mail Removed)> said:
>I have groups of data points in categories. I can easily calculate the
>average in each category and show these as a bar chart, but is it possible to
>show the individual data pioints in each category as well?
Yes it is. I would recommend using a combination of the bar chart series
with a scatter chart series.
1) Give the data "Y" points an "X" value of 1 to n, depending on which
bar they are to be with.
2) Add the second set of data to the bar chart using "Copy.. Paste
Special", taking care to choose "Categories (X labels) in First
Column", but not "Replace Existing Categories". This creates a new
bar series.
3) Right click on the new bar series, and select "Chart Type.. XY
Scatter)". This changes the bars to scatter points.
4) Adjust the X and Y scales of the scatter series so that they fit
nicely over the bars, and properly match the values.
This method of presentation works best if you make the bars a nice pale
colour, or even no colour at all, so that they do not hide the data
points. I also like to use error bars to mark the data as thin
horizontal lines, then set the data markers to "none". Select your
colours as you wish, to adjust the boldness of the data and the
aggregate measure depending on what you want the reader to pay most
attention to: bright bars for the aggregate and pale grey markers for
the data, or bold coloured markers for the data, and light pastel for
the averages.
I am a strong believer in showing all the data if possible, now that we
don't have to pay a draftsman to draw all the lines and points.
Aggregate statistics like the average are no longer necessary for labour
saving in this day and age, only for comprehension.
Be careful with the Y scales: it's obvious if the X scales are
mismatched, but it's easy to accidentally publish a combination chart
with mismatched Y scales, so that the averages don't seem to match the
data, which can be embarrassing. You will probably have to set the
scales to fixed zero and maximum values instead of letting them be
automatic, although there are techniques involving invisible dummy bar
series if you really need to leave the two scales free to adjust
automatically.
You might consider abandoning bars altogether, and plotting all the data
points and their aggregate statistics as scatter series with appropriate
markers. This avoids the hassle of mixing chart types.
(admittedly, this leaves the question of how to place the labels, a
problem which is often solved by creating a dummy category series, which
takes you right back to mixing chart types again!)
--
Del Cotter
NB Personal replies to this post will send email to
(E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.
Similar Threads
