Adding error-bars in a pivot-chart

D

David

I want to add error-bars to my pivot chart in the following context:

I have a pivot-table with the following fields:

- instance
- strategy
- parameter
- average
- std


I create a pivot report and pivot-chart with the following fields:

- instance as page
- strategy as column
- parameter as row
- average as data

The pivot chart will show me a data-serie per strategy with the x-axis
the paramater and y-axis the average and I can select the instance in
a drop down menu.

My goal is to add error-bars to the data series with the plus side
'average + std' and the minus side 'average - std'. It doesn't work to
do this in the formatting of the individual data series as they (the
formatting) are reset when I change/refresh data.

So I thought let me write a macro in VBA something like (assume I want
only to do this for SeriesCollection(1) ):

Charts("results chart")..SeriesCollection(1).HasErrorBars = True
Charts("results chart").SeriesCollection(1).ErrorBars.EndStyle =
xlNoCap
Charts("results chart").SeriesCollection(1).ErrorBar _
Direction:=xlY, _
Include:=xlErrorBarIncludeBoth, _
Type:=xlErrorBarTypeCustom, _
Amount:= ???????????, _
MinusValues:= ?????????, _


What do I put in for Amount and MinusValues (plus and minus error). Or
re-formulated, how do I access the field std in my pivot-tabel which
is not in my pivot-table-report in the correct format so ErrorBar can
handle it,

thanks for any reply,

David Rijsman
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top