100 % bars with a constandt average

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

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