Free Microsoft Excel 2013 Quick Reference

2d-Column (Clustered Column) chart with Standard Deviation Bars

I have a 2-D clustered column chart that shows the mean value of 4 different
samples using chunky blue coloured bars.

What I would also like to do is, on the same graph, show the standard
deviation of each sample on the relevant blue bar, such that a vertical line
is inserted in the middle of each of the blue bars that shows 1 standard
deviation up the way and one standard deviation down the way (i.e. so that it
looks like 2 letter "T"'s, symmetrically above and below each other).

I've not explained that very well but I hope someone knows roughly what I'm
trying to do - its really a typical statistical chart that shows both mean
and standard deviation on the same graph.

I can post an image if this site allows and if it would be useful.

Regards


Post your answer or comment

comments powered by Disqus
Hi, I'm trying to add a column clustered chart and I'm not sure how to define the names, values and x category labels for my different series. I posted what code I do have but I am just trying to follow the Peltier website. I like how the ranges are easy to define in his method. I will post the link to that below. I think that the Peltier code is intended for a scatter line plot. I obviously need to create a column clustered chart instead. Any help you can give to put me on the right track would be greatly appreciated.

Thanks

http://peltiertech.com/Excel/ChartsH...html#VBAcharts

Private Sub CommandButton2_Click()
    Set myChtObj = Worksheets("Individual Jobs").ChartObjects.Add _
        (Left:=12, Width:=370, Top:=2042, Height:=239)
    myChtObj.Chart.ChartType = xlColumnClustered
    With ActiveChart.SeriesCollection.NewSeries
        .Name = Worksheets("Graph Reference").Range("AA4")
        .Values = Worksheets("Graph Reference").Range("some range")
        .XValues = Worksheets("Graph Reference").Range("some range")
    End With


I've just discovered how extraordinarily usefull pivot-tables can be. But I am still having problems with its pivot-charts, especially in VBA.

My problem: In particular, I have a pivot-table with all "amount of data-sets", "average" and "Standard Deviation" of my data, which I'd like to visualise in a pivot-chart. I would like to have the pivot-chart show a xlColumnClustered Chart with the "average" and the "Standard Deviation" (as y-errors of the average!!!) - and if possible the "amount of data-sets" in/on/next to the correspondent column. I don't think that is something new or unusual, but I simply haven't found out, how to do so at all (in VBA).

Reached so far: What I have been able to encode is the establishment of the pivot-table and the chart (see code below). But I cannot get the chart to show the "Standard Deviation" as a y-error of the "average"-values! (Also, but this is less important, I neither get to show the "amount of data" ON the correspondent column.) For a usual chart, I do know how to add the y-error ... but for the pivot-chart, I don't know how to do so, cause relying on defined data-ranges for the chart, would not be a good idea in case somebody simply rearranges the pivot-chart, I fear.

Below you find the code, I have been able to make up so far, I appreciate any help,
Andy

[EDIT] I know, there is the button to add a "y-error" where you can choose from. But it does not contain the standard deviation (sample), whereas this is exactly what I can calculate in the table ... but I dunno how to make the standard deviation (sample) appear as the y-error in my chart.


	VB:
	
, strNFormat) 
     '
     ' This macro creates a pivot-table and a pivot chart based on the data in rPivot.
     ' wks: worksheet of rPivot
     ' rPivot: range of data for pivot-table
     ' strTblNme: future name of the pivot-table
     ' arrData: array field contains four infos about every field
     ' arrRow: array field, contains info about the row field
     ' arrColumn: array field, contains info about the column field
     ' strNFormat: (mostly = "0.00")
     '-------------------------------------------------
    Dim wkbNow As Workbook 
    Dim rStart, rUsed As Range 
    Dim myPiv As PivotTable 
    Dim pTable As Worksheet 
    Dim pChart As Chart 
    Dim b As PivotField 
    Dim i As Integer 
    Dim e, f, g As Variant 
     '-------------------------------------------------
     
     '---------------------------------------------------------
     ' Clean up and delete old pivotTable and pivotChart
     '---------------------------------------------------------
    Set wkbNow = ActiveWorkbook 
    Application.DisplayAlerts = False 
    If wsExists(wksPivotTable, wkbNow) Then wkbNow.Sheets(wksPivotTable).Delete 
    On Error Resume Next 
    wkbNow.Charts(wksPivotChart).Delete 
    On Error Goto 0 
    Application.DisplayAlerts = True 
     '---------------------------------------------------------
     ' Add Pivottable
     '---------------------------------------------------------
    wkbNow.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=wksIndexName & "!" &
rPivot.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable TableDestination:="", TableName:=strTblNme,
DefaultVersion:=xlPivotTableVersion10 
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    Set pTable = ActiveSheet 
    pTable.name = wksPivotTable 
    Set rStart = pTable.Cells(3, 1) 
    Set myPiv = pTable.PivotTables(strTblNme) 
     '---------------------------------------------------------
     ' Fill Pivottable
     '---------------------------------------------------------
    With myPiv 
         '-------------------------------------------------
         'Add RowField Variables
         '-------------------------------------------------
        For Each g In arrRow 
            With .PivotFields(CStr(g)) 
                .Orientation = xlRowField 
                .Position = 1 
            End With 
        Next g 
         '-------------------------------------------------
         'Add ColumnField Variables
         '-------------------------------------------------
        For Each f In arrColumn 
            With .PivotFields(CStr(f)) 
                .Orientation = xlColumnField 
                .Position = 1 
            End With 
        Next f 
         '-------------------------------------------------
         'Add DataFieldContent
         '-------------------------------------------------
        For Each e In arrData 
            If e(1) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "Data-sets of " & CStr(e(0)),
xlCount 
            If e(2) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "Mean of " & CStr(e(0)),
xlAverage 
            If e(3) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "StDev " & CStr(e(0)), xlStDev 
        Next e 
         '-------------------------------------------------
         'Set Number format for every data-field
         '-------------------------------------------------
        pTable.UsedRange.NumberFormat = strNFormat 
    End With 
     '---------------------------------------------------------------
     ' Add Pivot Chart
     '---------------------------------------------------------------
    Set pChart = Charts.Add 
    With pChart 
        .SetSourceData Source:=pTable.Range("B4") 
        .Location Where:=xlLocationAsNewSheet 
        .ChartType = xlColumnClustered 'chart type
        .name = wksPivotChart 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


3D bar chart:
Column 1 has labels
Column 2 has data
Column 3 has calculated Standard Deviation
I need to show one cell from column three as a standard deviation on one of
the data items in Column 2. Can this be done?

Hi everyone. I need to create a bar chart with variable width columns.

Let's say you have 5 tasks to do, and each is rated on how important it is.

I want the Y axis to have the importance value, and the X axis to be date. So the bars would be horizontal, their height would be determined by their importance value and their width would go to the date the task is due to be completed.

For example:

Task 1 - Importance 12 - Completion Date 3rd January '08
Task 2 - Importance 5 - Completion Date 2nd November '07
Task 3 - Importance 70 - Completion Date 19th February '08
Task 4 - Importance 20 - Completion Date 4th December '07
Task 5 - Importance 2 - Completion Date 4th April '08

And the chart would look something like this:
http://img3.freeimagehosting.net/uploads/bb57e41650.jpg

OK so it's drawn on paint but you get the idea!!

I am trying to make a combination chart with stacked colums and an overlayed
line (or data point), just like it is possible to make a combination chart
with "regular" bars and a line.

Hey,
I am trying to create a list with standard deviations.

I am using the following formula:
=IF(COUNT(B4:E4); STDEV(B4:E4);"")

4 columns are the source for the standard deviation. The formula works great, when 0 or 2 or 3 columns have values. However, when only one source value is available, then I get an error message (which is understandable).

My question is now how I can extend the formula so that Excel gives me an empty value (like it does when 0 source values are available) when only one source value is available.

Thanks
Benjamin

Hi,

Would someone be able to help me with standard deviation within excel? I have used the formula =STDEV(number one, number two...etc). I think the problem is that my data is not a normal distribution, thus the incorrect standard deviation value. But I need to some how calculate standard deviation? Is there a way that I can with excel? I have over 20 000 data elements, thus making it harder to work out. Could someone please suggest some ideas that I could possibly implement to help correctly calculate standard deviation?

I need help creating a bell chart for the standard deviation in the data below. Can someone help me pretty please?

1 2 3 4 5 6 Rating Average Response Count Median Mode St. Dev.
5 15 10 10 9 4 3.28 53 3.5 2 1.47
7 16 8 16 4 2 3.00 53 3.5 2, 4 1.34
4 17 13 12 6 1 3.04 53 3.5 2 1.22
10 12 14 11 5 1 2.85 53 3.5 3 1.32
11 14 14 3 9 2 2.83 53 3.5 2, 3 1.48
16 11 15 7 2 2 2.51 53 3.5 1 1.35
2 4 15 11 16 5 3.94 53 3.5 5 1.28
3 10 12 19 7 2 3.43 53 3.5 4 1.22
4 8 12 19 8 2 3.47 53 3.5 4 1.25
9 12 13 9 9 1 3.00 53 3.5 3 1.40
10 23 10 7 2 1 2.45 53 3.5 2 1.17
0 10 12 13 14 3 3.77 52 4 5 1.21
4 10 11 13 12 3 3.53 53 3.5 4 1.38
5 4 14 14 12 3 3.63 52 3.5 3, 4 1.34
4 7 9 12 18 3 3.79 53 3.5 5 1.39
5 8 16 10 12 2 3.42 53 3.5 3 1.35
7 15 13 9 9 0 2.96 53 3 2 1.30
8 10 7 16 9 3 3.32 53 3.5 4 1.49
5 10 6 15 10 6 3.63 52 3.5 4 1.52
3 13 13 15 6 2 3.27 52 3.5 4 1.24

I am trying to make a combination chart with stacked colums and an overlayed
line (or data point), just like it is possible to make a combination chart
with "regular" bars and a line.

Hey Jon Peltier... perhaps you can help me out with this (or someone else)

I'm trying to make a complicated chart in Excel. Here's the deal:

I want to put in the same graph three data: investment, rating and # of insertions. I need to use three axis then... and I know you might be thinking now that I could check out the steps you have for adding a third axis to a graph. But a problem arises: I want to use a stacked column chart for both investment and # of insertions and a line chart for the audience measure.

This is the data for insertions:

Insertions Brand
Channel Stayfree Always Nosotras Category
TELEANTILLAS 93 117 63 273
TELEMICRO 58 0 21 79
CORAL-39 0 53 0 53
TELESISTEMA 50 0 0 50
ANTENA LATINA 38 0 0 38
AME 0 0 8 8
COLOR VISION 0 0 4 4

I have a similar table for the investment data.

Luckily, for the audience data, I have only crossed it with the channels, and not the channels and the brand.

I hope you can understand what I want.

It's "simply" adding a tertiary axis for a combined stacked column-line chart.

Thanks in advanced and sorry for my poor English skills, I hope I have expressed myself correctly.

Marcos

I'm trying to create a column that shows the standard deviation of a subset. I'm going to use this to search for outlying values for each particular customer.

I.e. so I can set a flag if a particular invoice is a given amount away from the "normal" for that customer.

I'm nearly there, but my formula is including the zeros in the array when calculating the standard deviation, which produces the wrong answer.

Anyone know a work around?

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC2D2C3D3C4D4C5D5C6D6C7D7C8D8C9D9C10D10C11D11C12D12C13D13C14D14C15D15C16D16=
ABCD1Customer*numberAmtStdev*of*subsetWhat*I*want*it*to*show21109.1025898981031209.1025898981041309.10258989810525017.694497410.40833624517.694497410.40833723017.694497410.4083383106.3635868895.03322295793206.3635868895.033222957103146.3635868895.03322295711410089.8941176190.4249228212430089.8941176190.4249228213410089.8941176190.424922821448089.8941176190.4249228215420089.8941176190.424922821647089.8941176190.42492282Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I'm trying to make a combination chart with 3 sets of data. I want to stack 2
of them and leave the 3rd unstacked. I want the table data to show each line
of data individually. How can I?
--
yvonne

I have a set of the means and the standard deviation. How can I plot line graph in such a way, that for each mean, I have 2 dots, above and below of that number, representing the -std and +std. So that I have a line representing the mean, and whole bunch of dots that locate above and below the line representing +std, -std. Thanks in advance

I'm trying to create a cluster chart with two series' of data but with a
secondary y-axis. Upon clicking on secondary axis (Format Data Series / Axis
/ Plot on secondary axis) it stacks each cluster, meaning each data pair. I
want them to stay side by side, just as they appear when there is only one
axis (ie. no overlap). Once the secondary axis is clicked off, manipulating
the overlap has no effect. I can't seem to separate the pairs.

Is it possible to have a date chart with a gray bar marking Saturdays,
Sundays and declared Holydays so I can Gantt my deliveries? Just a few tips
on how to get there...

PS: by the way Jon, I've been learning A LOT from your examples. Thx for
your excellent pages.

Hey

my problem is that i need to plot a time series:

My database is a time series of stock prices with volume (tick data) and i want to plot it as a line chart with a volume bar chart in the lower section.

Is this possible with Excel?

thx

I've plotted a column chart which has 6 different columns. Each column has a specific name and value for it as well as standard deviation which needs to be represented on the chart. I'm able to plot the columns with the correct labels underneath, but when trying to add the standard deviation error bars, it inserted the same standard deviartion for every column as well as at the same value. Any help with this would be great.

I have three categorical variables: "short" "normal" "long" that I want to be on the x-axis of this bar chart. On the y-axis I want average Maximum Heart Rate. In the attached file you'll see that there are three columns. Each column needs to be averaged and then displayed as a bar. Then I need whiskers for each bar showing the standard deviation. I'm working with Excel 2004. Any help would be very much appreciated. An explanation would be great as I'll probably have to do this again.

is it possible to build a chart with clustered stacked columns ?

Is there anyway that I can fill a bar/column chart with patterns as I can do
with Excel 2003?

I know that MicroSoft removed this feature in Excel 2007. But I don't
understand why they do it. Lots of academic journals prefers black and white
charts than shaded or charts with gradient. Also, it's hard to tell one
series of data from another series if they are printed in black and white.

Any help is appreciated.

How do you create a column chart with a secondary axis without having the
bars overplotting? I would like the bars to be adjacent to one another.

I'm trying to make some pretty straightforward column charts and then add error bars for standard deviation on the charts, but I can't get it to do the standard deviation for each individual column of data. I can only get all of the columns to use the same standard deviation (whichever one i choose as my custom value).

Anyways I am able to set each bar to have a unique standard deviation error bar?

Using Excel 2007 btw.

Thanks

Hello all,
I am having some trouble creating the nice 3D column chart in my macro.
When I code it, the chart always comes out much smaller (the 'walls' part,
not the chart area is smaller). Also, the depth of the 'walls' area is not
deep. Also, the 'walls' area is always tilted too much. I know how to
change the tilt, that's not the problem. But I just can't make the actual
columns part larger, even by increasing the size of the chart area.

The funny thing is that I am able to get a nice looking chart by using the
chart wizard manually. I have also tried recording a macro of the chart
wizard. Still, no luck. Does anyone have any suggestions? Any help would
be appreciated. Here is the code (not the recorded macro, but my own) that
I am using (with Excel 2000):


	VB:
	
 ActiveSheet.ChartObjects.Add _ 
    (Left:=450, Width:=375, Top:=300, Height:=225) 
    .Chart.SetSourceData Source:=ProgramSheet.Range("N3:O14") 
    .Chart.ChartType = xl3DColumnClustered 
    .Chart.HasTitle = True 
    .Chart.ChartTitle.Characters.Text = "title" 
    .Chart.Axes(xlCategory, xlPrimary).HasTitle = True 
    .Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x lab" 
    .Chart.Axes(xlValue, xlPrimary).HasTitle = True 
    .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y lab" 
    .Chart.HasLegend = False 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Daar All,

According to my provided data, would you pls help me to draw a stacked chart with double columns, each bar includes four types of data(Internet, PBS,Branches and Mail-in/Fax-in) and the x-axis are 1Q06, 2Q06 and 3Q06 repectively. It is urgent,pls provide the template for me. Many Many to all of you.

Thanks & Regards,
Bluebells


No luck finding an answer? You could always try Google.