Bar chart error bars automation - constrained to averages?

Similar Threads:

1.Bay chart error bars automation - constrained to averages?

Hi,

Over the past month I developed an Excel add-in (which I can't share in its 
entirely for legal reasons, unfortunately). The most relevant code is pasted 
below my signature.

What the relevant part of what it does:

- Sorts the primary (input) worksheet by a column which represents the ID of 
survey panelists (there are multiple, varying numbers of observations/rows 
per respondent)

- Uses the subtotal function to average values for that panelist on a number 
of "attributes", which are found in adjancent cells on each panelist's row(s)

- It then creates a bar chart on a separate worksheet for each panelist 
(~100) with the averaged/subtotaled score for each of the specified cells on 
the panelist's subtotaled row

- Finally it creates error bars for each panelist's series of data

***The problem:

VBA/Excel seems to limit my error bar choices to those which you could 
create by right clicking any bar chart series - standard error, stanard 
deviation, etc. 

The huge problem here is that it averages those bars across the series - so 
the error bar is the same for each bar in the bar chart. It is absolutely 
critical for me that the error bars are calculated on a "per bar" (aka "per 
column") basis.

I don't care what measure is used - range, std dev, std error, etc - but I 
have to find a way to make them reflect the variance in the data for each 
panelist's columns/attributes/bars.

Any of you guys with the huge brains have any thoughts? I will greatly 
appreciate any help.

Happy Thanksgiving (if you celebrate it)!

Jason Miller

Sub errorbars()
'
' errorbars Macro

    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, 
Include:=xlBoth, _
        Type:=xlStError
End Sub

*****
Related module-

Sub FeedbackReport()

Dim rCell As Range
Set rCell = ActiveCell.Offset(0, -2)

    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData 
Source:=Sheets("Sheet1").Range("Currentselection"), PlotBy:= _
        xlRows
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C4:R1C21"
    ActiveChart.SeriesCollection(1).Name = rCell
    ActiveChart.SeriesCollection(2).Values = "=Sheet2!R1C2:R1C11"
    ActiveChart.SeriesCollection(2).Name = "Constant Values"
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Blah blah blah Title Goes Here"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Attributes"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Intensity"
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlTop
    ActiveChart.HasDataTable = False
    ActiveChart.ChartTitle.Select
    ActiveChart.Legend.Select
    Selection.Left = 242
    Selection.Top = 53
    ActiveChart.ChartArea.Select
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 210.84, 
30.89, _
        273.48, 14.12).Select
    Selection.Characters.Text = "Censored title blah blah blah"
    Selection.AutoScaleFont = False
    With Selection.Characters(Start:=1, Length:=43).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.ShapeRange.IncrementLeft 15#
    Selection.ShapeRange.IncrementTop -0.03
    ActiveChart.Shapes("Text Box 1").Select
    Selection.Characters.Text = "Censored title blah blah blah"
    Selection.AutoScaleFont = False
    With Selection.Characters(Start:=1, Length:=43).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 0
        .MaximumScale = 150
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 15
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Interior.ColorIndex = xlNone
    ActiveChart.ChartArea.Select
End Sub

2.Customise error bars on individual bars in a bar chart 2007

I posted this question a couple of days ago but it there seems to be an issue 
with the MS notification system and I can't access the answer that someone 
posted for me.

I want to allocate individual values for error bars in a bar chart, I'm 
using Excel 2007. There is a customise option but the values you select apply 
to all bars on the chart. As the bars in my charts are a mean value in 
themselves, I want to show the error applicable to that bar.

3.error bars on bar chart in Excel 2007

I would like to assign a separate error bar value to each of the data sets 
within my bar chart. When I use the customise error bars feature it allocates 
the same error bar to all of the bars in the graph.

Each chart in the bar is a mean in itself, hence the requirement for 
individual error bars.

I see from previous posts that this appeared to be possible in Excel 2003 
but I don't have access to this version.
  

4.adding multiple error bars to bar chart

i need some help!! i need to add error bars to a bar chart, and each value in 
the series has a different standard error which has to be added. any ideas 
how i can do this?? each time i try and change the error bar they all change 
in the series!
Help me please!

5.Bar Chart with bar data and another bar showing a range of data

I have to create a chart that has three columns that I would like to show as 
two bars,the columns are one with a fixed amount and the other two with a 
negative to positive range. Do you know how this could be done?

Ive included a sample of the data, the first numeric column would be the 
first set of bars (2.5, 8.9, etc.) and the second two columns would be the 
range set of bars (-5.3 to 10.3, etc.)
	
Sample 1	  2.5	-5.3	10.3
Sample 2	  8.9	-2.5	20.3
Sample 3	  3.8	-7.4	14.9
Sample 4	  0.0	-7.1	7.1

Any help would be greatly appreciated! Thanks!!!!

6. Bar Chart with bar data and another bar showing a range of dat

7. BAR CHARTS-DISPLAYING A WHOLE BAR AND A STACKED BAR ON THE SAME GRAPH

8. Can I combine a cluster bar chart and a stacked bar chart in one