0

I have a pivot-chart that uses averages to plot each of my grouped points. I'd like to add errorbars, but Excel doesn't seem to support automatic errorbar generation (or does it?).

Should I just generate a standard graph / pivot-chart all in VBA to include this capability? I'd be losing most of the nice slicer features (if the standard chart is the solution) and it makes me sad...

EDIT: Let's look at an example!

Here is a picture of a pivot-chart I made using data from a table in another sheet.

Example

I'd like to add errorbars that are automatically calculated form the standard deviation of the mean. But since a new mean is calculted each time I filter my pivot for a specific combination of filtered fields, then I can't just add errorbars manually each time I change the filtering of my data!

Vincent-Daniel Girard
  • 13
  • 4
  • Well, there are many ways to put in error bars... some chart styles have them by default, in other cases you can add error bars via VBA. We would need more info about *how* you generate to aid in finding an **objective** solution to your issue, rather than the current question which may have a multitude of answers. – Cyril May 21 '19 at 20:28
  • Perfect, I'll edit my question to put a concise example – Vincent-Daniel Girard May 22 '19 at 12:47

1 Answers1

0

I think I found a way to do what you would like to achieve. I create two Pivot tables. One containing the average Values ("PivotTable1" on "Sheet1") and a second one containing the StdDev ("PivotTable2" on "Sheet2"). To apply a filter on both tables simultaniously the two Pivot Tables need to be connected using slicers ("PivotTable Analyse"->"Add Slicer" then select the slicer and go to "Slicer"->"Report Connections"). After these preparations the following code works for me in Office365 on Windows:

Sub StdDevErrorBarsToPT()
    Dim cht As chart
    Dim ser As series
    Dim ptAve As PivotTable
    Dim ptDev As PivotTable
    Dim dataRange As Range
    Set ptAve = Worksheets("Sheet1").PivotTables("PivotTable1")
    Set ptDev = Worksheets("Sheet2").PivotTables("PivotTable2")
    
    Set cht = Worksheets("Sheet1").Shapes.AddChart2( _
        251, xlColumnClustered).chart
        
    ' Set the data source for the chart
    cht.SetSourceData Source:=ptAve.TableRange1
    
    For i = 1 To cht.SeriesCollection.Count
    ' Set series object
        Set ser = cht.SeriesCollection(i)
        Set dataRange = ptDev.DataBodyRange.Columns(i)
        cht.SeriesCollection(i).HasErrorBars = True
        ser.ErrorBar _
        Direction:=xlY, _
        Include:=xlErrorBarIncludeBoth, _
        Type:=xlErrorBarTypeCustom, _
        Amount:=dataRange, _
        MinusValues:=dataRange
    Next i

End Sub
brauer-t
  • 45
  • 7