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 Whe=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(msoTextOrientationHo rizontal, 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

Bay chart error bars automation - constrained to averages?
 

Hi,

This for me sets the Error bars to use custom values. It demonstrates both
array and range references.

' xl2003

With ActiveChart.SeriesCollection(1)
.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, _
Type:=xlErrorBarTypeCustom, Amount:="={1,2,3,4}",
MinusValues:="={3,3,2,3}"

.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, _
Type:=xlErrorBarTypeCustom, Amount:=Range("E2:E5"),
MinusValues:=Range("D2:D5")

End With

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Jason" wrote in message
...
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 Whe=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(msoTextOrientationHo rizontal, 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

Bar chart error bars automation - constrained to averages?
 

Thanks Andy!

I haven't implemented it, but it looks like you identified the solution. I
really really appreciate it.

Jason

"Jason" wrote:

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 Whe=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(msoTextOrientationHo rizontal, 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