Show # of data points w/average on bar chart

[ckam888]

Hello. This seems basic, but I can't figure this out:

I have a horizontal bar chart with several series. The data for each series is an 'average' from columns of data elsewhere in the workbook. Some of the averages reflect only a couple of data points, while other averages reflect dozens. For this reason, the chart can be misleading. I want to be able to show the number of data points that each 'series average is comprised of on the chart. All I can seem to do is have the 'average' value display next to each of the series bars. Any ideas?

Thanks!

Craig

[teylyn]

Hello Craig,

You will need to construct another column in your data table, using count() to count how many values went into each individual average. With your chart in Sheet 1 and your source data in Sheet 2, and the source data comprising columns B to Z, you could use

=count('Sheet 2'!B2:Z2)

to arrive at the number of values that went into the average for the data in row 2. Copy down as appropriate.

With this count of values, you can then use these results in your chart. You can assign them to data labels, using the XY chart labeler tool (which works on all kinds of charts, not just XY charts) and show the numbers that went into the average right on the horizontal bars.

Download the XY Chart Labeler here: http://www.appspro.com/Utilities/ChartLabeler.htm

If the above was a little too much for comfort, please post a sample file with some dummy data that represents your data structure and chart.

I'm sure you'll get a working solution from this forum.

cheers,