100 % bars with a constandt average

[Brian Reilly, MVP]

Hi fellow MVP's,
I am automating (VBA) bar charts in XL and have most of it under
control but have one outstanding issue that It would appreciate
comments on. XL 2003

Let's say I have two data series that all are measured on 100%.

The series are easy to calculate since I get the first value from the
data and the second = 1-first series value.

E.g. Series 1 value = 60 so the seccond data point in that series is
40. Series 2 value 1 = 25 so the second data point in that is 75.

So far fine.

Now I want to show an average or Mean for this across all records for
each series. Let's say that the Mean for Series 1 is 40 and the
average for the second is 85.

The way this has been done traditionally is to draw a line in the bar
at 40 and another line at 65. Clearly all the values are in the data
to calculate the 40 and 65. Can handle that.

The thing I don't get is how to show that value of 40 or 65 on the bar
chart.

Wrote some code using the macro recorder which added a line and then
moved it and it showed me a .IncrementLeft value which works but am
not sure how to calculate the .IncrementLeft value. based on a value
(40 or 65 or whatever).

You guys have any ideas how to approach this?

Brian Reilly, PowerPoint MVP

 

[Ed Ferrero]

Hi Brian,

Not too clear on how you are building the chart.

I assume you have a stacked column chart with two series.

You are adding a horizontal line programmatically?
Too much work.

Let's say the average value you wish to show is 40.
You can add the value 40 in two cells and add this as
a series to the chart, then right-click on the series you
have just added and change the chart type to line. This
gives you a horizontal line at the value 40.

To show a value label next to the horizontal line, add
another line series, this time with the values [blank], 40.
Then format this last series to show value data labels,
and no marker.

Ed Ferrero