Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
add individual data points to a bar chart of averages
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? Thanks, |
#2
|
|||
|
|||
Answer: add individual data points to a bar chart of averages
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
add individual data points to a bar chart of averages
Hi,
How are you plotting the data points - as columns or line? and also could you show us dummy data layed out so we can understand what you data looks like. If you plot a clustered column chart you could show the average for each cluster as a line within the cluster or as a single point. -- Thanks, Shane Devenshire "lenos" wrote: 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? Thanks, |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
add individual data points to a bar chart of averages
On Wed, 30 May 2007, in microsoft.public.excel.charting,
lenos 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 , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding individual points to a line graph | Charts and Charting in Excel | |||
excel should let you vary error bars for individual points | Charts and Charting in Excel | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
Change color of individual hiloline points in a series | Charts and Charting in Excel |