Error-bars in pivot-chart

[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 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.

David Rijsman

 

[Tushar Mehta]

Also check the response in office.developer.vba

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions