Bar chart error bars automation - constrained to averages?



  • 1. Pie Chart with other charts representing each slice
    Hello, I wasn't sure where to start researching this, so I'm starting here. I have a user who would like to create a pie chart representing totals. She would then like to have "offshoot" charts indicating the values used to arrive at the total for each slice. These separate charts should be positioned next to each slice it represents. Can Excel do this automatically, or will she have to create charts for each slice separately? We are using Excel 2007. Thanks for any suggestions - please let me know if more detail is needed.
  • 2. How can I post values in excel chart without displaying the colum.
    I have 3 variables, I would like to display 2 of them as column in a column chart, and would like the third variable's values to appear on top of the other columns, without presenting the third variable as a column.
  • 3. St4em and Leaf Display or Plot
    I had an assignment at class and couldn't do it because I didn't get the stem and leaf chart in Excel. Can anyone assist me in how to create a stem and leaf plot? Thanks.
  • 4. Stacked columns width too thin - how can I alter?
    Hi! I've created a bar chart with stacked columns, but the bars have come through no wider than a 1pt line - I've managed to increase them to the width of a 2pt line by reducing the 'gap' to zero in 'Format data series-> options', but this is still not wide enough to see the bar colour and not consistent with the legend. How can I get the bars to be a standard width so I can see the data? I've also tried transposing the data to see if this made a difference, but no luck. The data is as follows: Column 1 = date (1/1/08; 8/1/08, etc) Column 2-4 = time values for different training type times, eg run, cycle, swim (format = 00:29:34) Columns 5 = number value between 120 - 200 (for average heart rate) Column 1 = chart x axis. Total of columns 2-4 = primary y axis plotted as stacked column Column 5 = secondary y axis plotted as line. Any suggestions?! Thanks

RE: Bar chart error bars automation - constrained to averages?

Postby SmFzb24 » Thu, 27 Nov 2008 23:53:02 GMT

hanks Andy!

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


"Jason" wrote:

Similar Threads:

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


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).ErrorBar Direction:=xlY, 
Include:=xlBoth, _
End Sub

Related module-

Sub FeedbackReport()

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

    ActiveChart.ChartType = xlColumnClustered
Source:=Sheets("Sheet1").Range("Currentselection"), PlotBy:= _
    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
    Selection.Position = xlTop
    ActiveChart.HasDataTable = False
    Selection.Left = 242
    Selection.Top = 53
    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
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 0
        .MaximumScale = 150
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    With Selection.Border
        .ColorIndex = 15
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Interior.ColorIndex = xlNone
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


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

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 57 guest