Free Microsoft Excel 2013 Quick Reference

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

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

- Adding column clustered chart
- Average With Standard Deviation In Pivot Graph/Chart
- Excel Charts: Can I show standard deviation on selected series?
- Bar Chart With Variable Height Bars
- Line-column combination chart, with stacked columns
- Standard Deviation when only one value is available
- Standard deviation calculations with excel
- Creating a bell chart for standard deviation
- Line-column combination chart, with stacked columns
- Combined Stacked Column / Line Chart with three axis
- Standard Deviation of a subset
- ?make a combo chart with 1 stacked bar and 1 not stacked, + show t
- Plot line with standard deviation????
- Cluster charts and the secondary y-axis
- Chart with colored weekends
- Time Series Chart with Volume
- Displaying Separate Error Bars For Each Column On Chart
- Help making a bar chart with whiskers
- Is it possible to build a chart with clustered stacked columns ?
- Fill bar/column charts with patterns in Excel 2007?
- Excel column chart with a 2ndary y axis w/o bars overplotting?
- Column charts and std deviation
- Excel VBA - Problems with 3D Column Clustered chart
- Stacked Chart With Double Columns

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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?

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!!

line (or data point), just like it is possible to make a combination chart

with "regular" bars and a line.

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

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?

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

line (or data point), just like it is possible to make a combination chart

with "regular" bars and a line.

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.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.

of them and leave the 3rd unstacked. I want the table data to show each line

of data individually. How can I?

--

yvonne

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.

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.

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

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.

bars overplotting? I would like the bars to be adjacent to one another.

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

Using Excel 2007 btw.

Thanks

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 WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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