Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Individual Error Bars In Bar Graph

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I've made a bar graph in Excel 2003 with four columns and two columns are clustered together in a series (so there are two series). I want to enter in a separate standard deviation value for each column. When I try that customs option and enter in the values for the first column it automatically adds an error bar of the same values on the second column of the same series. How can I fix that?

View Answers     

Similar Excel Tutorials

Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
How to Create and Manage a Chart in Excel
In this tutorial I am going to introduce you to creating and managing charts in Excel. Before you create a chart yo ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Simplify VBA Coding for Common Functions
' These modules exist just to make other VBA code easier to write and more readable ' For example, you can just ent ...

Helpful Excel Macros

Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor

Similar Topics







i have a chart with one series and three bars. i have already calculated the values of the standard deviation for each bar. the part i'm having trouble with is formatting those values for each of the bars in the series independently. I got the error bars to show up on my graph but I don't know how to change their values.. How do I do this??


I am using Excel 2007.
I wanted to create a bar graph in which series A and B are stacked, and next to it there is a series C (imagine that A are women from the US, B are men from the US and C are HIV positive cases in the US). So what I am trying to do is some kind of combination of stacked bar graph with clustered bar graph.

I have an horizontal bar graph in which I created two series (A and B) that are stacked and a third serie (C) that is not stacked. In order to do that, I followed the following approach.

1) I created a bar stacked graph with the three series.
2) Then I selected serie C and I set that it's shown on the secondary axis.
3) I set the secondary axis scale to match the principal axis.

The problem is that the series are overlapped, and even if I try to separate them (going to series C format settings and changing the overlap), it doesn't do it (tip: to visualize things better, I recommend to go to series C and set the Gap With to 300%).

If I try with series A and B settings, overlap does work, but it just separates every series (A from B, B from C, C from A) and I don't want to do that!

Any help?


Hi,

I have the following data:

July 07 Aug 07 Sept 07 ...................... Jul 08 Aug 08
Series 1 25 30 32 ...................... 42
Series 2 25 27 28 ..................... 32
Series 3 5 7 8 ...................... 10

Series 1 & 2 are bars, series 3 is a line graph, all on the same graph.

My graph currently shows the data for the last year (i.e. 13 months inclusive). Each month I enter new data and have to manually update the graphs by changing the Source Data and dragging it across.

What formula can I enter so that my graphs update as soon as I type in a value to the most recent month?

Currently the Series Names are in Cells B5,B6 and B7 respectively.
The current values in the graph are as follows:
Series 1 I5:U5
Series 2 I6:U6
Series 3 I7:U7

Next month's data will be entered in V5,V6 and V7.

Thanks in advance for your help.

Ed


First off, here is a copy of my worksheet/chart.

I am trying to put together a simple bar graph in Excel 2003 that shows the mean of one dataset as a single bar and the mean of another dataset as another bar. Both of these will include y-error bars with the standard deviation for each dataset.

The problem I having is if I put each mean under a different series, Excel only puts one name for the (X) axis data label ("Node 1" instead of "Node 1" & "Node 3").

If I put each mean under the same series, the y-error bars for the standard deviation are the same for each bar. Each mean has its own standard deviation and needs to be independent.


I'm working with six sets of data - two treatments and three timepoints. I made a bar graph of the means [two series, one for each of the treatments] and I need to add error bars for each separate bar, which I had already calculated on another sheet. I know how to change the custom value for the whole series but I can't figure out how to change the custom error for separate data points within this series. Hope this makes sense ... help please?


Hello,
I want to create a bar graph with six columns in which each column is a separate series. I want to split these columns into three groups ( say group A, B, C for example), with two columns side by side in each group and the three groups spaced apart from eachother but on the same graph. It is important to separate each column into a different series because after I group the appropriate columns together, I want to add a standard deviation that is specific to each column. I am using Excel 2010.
thank you!


I've an error bar query.

I've calculated the standard error margin (SEM) for each point in my bar graph - this varies as each point is the average of a varying number of samples.

This SEM value is represented in the same units as the data on the bar graph (second), and is in a column that corresponds to the values for the bars in the bar graph. (i.e. A1 = Bar 1 value, B1 = Bar 1 value SEM).

When I define the error bar using the 'custom error bars' option in the 'add error bars' tab I select the column (I drag from the top of the SEM data to the bottom) for the SEM data. However, Excel gives seemingly spurious error bars when I do this. I'm getting errors of several times the values according to the error bars, when the actual SEM is equal to zero, for example.

How do I define the error bars correctly? Is because I am working in seconds?

I have attached the sheet. The values of the bar chart are in column AU, and the values of each point's SEM is in column BB.

(You'll also notice the ridiculous formula I came up with for calculating the SEM! I'm not very good with Excel!)

Thanks for your help,

Adam


Hi everybody,

I am trying to create a bubble chart that has about 10 series. Column A
contains the names, column B the x value, column C the y values and column D
the size. I currently have to manually add each series in the graph. Is
there a way to capture all the series rather than me having to add each
series one by one? All though this graph only has ten series, I have have
about 13 more of these graphs to create, so adding a series one by one can
add up to a lot of time. Any help will be appreciated. Maybe some VBA
coding is needed.

Thanks
Kim





I need to graph a column graph with different (manually found) standard error for each column.

So I made a normal column graph where each of the columns is an average of other values. For each I have a different error. So I used the error bar options under layout, but it doesn't allow me to put a different error for each column in the column graph. Whenever I put a custom error in with 'more error bar options', it puts the same error across the board for every column.

How can I get excel to put a different error for each column of the column graph?

Any help would be greatly appreciated.


Hi,

Adding secondary axis to the clustered columns graph with 2 series puts the bars of the series one on top of the other. What is needed is to put the series bars one next to the other (as it is before adding the secondary axis).

Can anybody advise how to solve this problem?

Thanks.


Forgive me if this has already been explained, but I can't figure out how to make clustered columns. (The instructions I found here look like they're for people who already got clustered columns, and need to know how to do something extra/special.)

All I'm trying to do is, given two series of data, put them next to each other by year.

So you'd see 2000 Series A (red) and Series B (blue) together right next to each other, then a gap...

then 2001 Series A (red) and Series B (blue) together, then a gap...

then 2002 Series A (red) and Series B (blue) together... you get the drift.

Thank you!
Rachel


Hello, I have having a lot of trouble with a graph I am trying to create. I probably selected or have my data values in the wrong orientation, as I have always been much more familiar with the versatile Excel 2003's Graph Wizard function instead.

BIOL233_Graph.xlsx

In this situation, I have a problem similar to this (I tried following it, but got confused half-way):

http://www.excelforum.com/excel-char...-x-values.html

I have three series that all need to start at an x-axis value of 0 (but the NaCl and CaCl2 column will begin at their starting x-value of 0.025), but whenever I do so, by navigating:

Quote:

Right-click on chart > Select Data

I try to change my x-axis value on the right-side for a certain value point, but the change is done for all three series at the same time instead. What I want actually want to do, which also brings up my other issue, is to have the x-axis start at the origin with a value of 0 and a maximum of 0.225, with eight intervals between this range in increments of (0.025). My NaCl and CaCl2 values would start at an x-value of 0.025 and flow accordingly.

Lastly, I have also tried putting on error bars for each data point (meaning, each data point has a unique error bar), but once I...

Quote:

Layout > Error Bars > More Error Bar Options > Custom > Specify Value

and choose one of my values from its corresponding SEM column, it applies to all the points of the series. Is there a way to put unique error bars per data point?

Thanks in advance!




Hi,

I'm trying to create a bar chart with three series of data, which have different standard errors. When I add the error bars to the chart, they're all the same height, but the correct values. When I try to click the custom button to select the series to change, the dialogue box that should come up asking to choose the series doesn't appear.

Can anyone see where I've gone wrong?
Thanks
EggLayingData.xlsx

ok. this is really confusing. basically, i need to make a chart showing the average measured value of an element over the course of using 4 different bottles. the person i'm making it for wants each element grouped together so each group is one element and a different bar for each bottle.

so i made this sheet:

http://myownsection.homestead.com/files/sheet.JPG

which gives me this chart.pretend that i actually fixed the legend for you.
http://myownsection.homestead.com/fi...ttle_chart.JPG

each grouping of bars is one bottle and all the different elements are represented by different colors
then i can make the error bars, b/c each series is the same element.

but, he doesn't want that. he wants it to look like this

http://myownsection.homestead.com/files/bottles.JPG

see how each group has only 1 element and different colors represent different bottles.

to do this, i had to do something funky with the excel data, and did this:
http://myownsection.homestead.com/files/sheet2.JPG

to make each element next to each other, i had to make each series contain all the elements. B/c i did that, i can't put the error bars in anymore, bc the series is all different types of values.


i need to make the graph look like graph too, but everything i try seperates the series throughout the graph... like this
http://myownsection.homestead.com/files/example.JPG


i tried to make the gap between the columns smaller but as soon as i add a new series of data it seperates them again


Hi all,

I'm creating a clustered column graph through VB and am having some trouble getting the different series to each have their own x-axis label. I know I have to somehow get each dataset into it's own category, but have no idea how to do this. Each value to be graphed is calculated from a single series of data.

Any help would be great!


I am trying to create a graph which shows stacked columns for some values, and I also want to display some percentage rates in the same graph, so that I end up with stacked columns of 2 measures and two line % series comparing one account with a total product all on the same graph. I have created the stacked columns (including switching from actual to projected figures at a cut off date where the projected figures are different series' which appear in different colours). How do I now add two more series as lines?


Hi, thanks for reading. I did a search and came up with a lot of info on this topic, but my particular problem isn't answered.

I have a chart (attached) where I'm trying to put error bars with custom values for individual datapoints, and I can't figure out how to do it except by series. I don't want the error bar on both the "pre-tx" and "post-tx" sides. I have read that I can use the "custom error bar" tool to specify a range which will populate the graph by individual data point, but I don't understand how this works. I have tried selecting the range of standard errors, but it just populates the whole series again. Can someone show me how to structure this table?

I have specified on the attachment which error I want where. Thanks.


Hello,

Is there some way to assign custom error bars to many different data series on a chart all at the same time? I will try to describe what I mean with a pictu



So the picture has three data series (types 1, 2, and 3) all plotted against time (day). Below the means are the standared deviations corresponding to each point above.

I want to use the standard deviations as the magnitude of the error bars. In my actual workbook I many several more series and it has become difficult to add the error bars to each data series individually. If possible I would like to tell excel that for a particular chart it should look to the corresponding cells below for error bars for all the series on the chart.

Thank you very much,

-DanExcel


Hello,

I currently have a graph of results with error bars showing standard deviation. However, i need to show 1 and 2 standard deviation. Is it possible to do this on the same graph say in a different colour or something or do i need to create 2 seperate graphs, one with one standard deviation and a 2nd with 2 standard deviation?

Thanks,

Andrew


I have a tough elusive Excel bar chart question - has anyone been able to find a way around this? 100 "Einstein points" for anyone who can solve this :D

I have an Excel spreadsheet with two different data series (each series has 10 values). The resultant excel bar chart I created has 10 pairs of bars (each pair has a red and a yellow vertical bar chart, where each color represents one of the two series). Now here's the dilemma: I would like to individualize the error bars WITHIN each (red or yellow) data series. In other words, I'd like to specifiy the a unique value for the statistical error bars of each of the 20 vertical bars. This is a problem because by default, if I change the error bar displacement on one bar chart member of a given series, then the error bars of all the other members of the series change to the same value. Help!

Thanks-




Hey all,

I have an Excel export to PPT function that works well except that it only creates one series in the PPT graph. Although the data for the second series is copied into the PPT Graph Input Sheet, the graph does not add a series for this data. E.g., Col.A. is all the X-Series labels, Col.B. is Series 1, Col.C. is Series 2. The following code tries to add the series then format it appropriately; the formatting section works ok. The error code I receive is Method 'Extend' of object 'SeriesCollection' Failed. The directory mentioned to retrieve the chart template files has been altered.



Please Login or Register  to view this content.



Hello,
I am trying to put some data on graph using ChChart from Office Web Components.
I faced a problem when adding several Series. In attached file you can find three different ways to add series.
File is really rough.
I want to update later on data, that is why I want to have access to all series I add and to modify them.

When I use option 3 everything is almost perfect. However another use of button "option 3" seems that adds only one new serie 13. But series index says that number of series is ok.

When I use option 2 and 1 which are prepared to be more flexible I got comletely illogical result. First use of buttons "option 1" or "option 2" gives only two series on graph. Another use adds right number of new series. Names of these series are strange. Behaviuor of properties of Series .index and .layerIndex are strange too.

Please, do you know if there is some "special" way how to "talk" to ChChart?
Or is there anything wrong with the code options 1 or 2?


This might be a bit of a beginner's question but basically i have a set of data with the x values in a row and y values in rows below it. The actual graph is not a problem but what i need the most is a macro into which i can input a column of names to match each series. So basically the part where it says 'Series 1' is my real name and i need to rename my series in the graph to those. Any idea?
[IMG]file:///C:/Users/Dimig/AppData/Local/Temp/moz-screenshot.jpg[/IMG]
0 45 90 135
X values
Series 1 8264 8103 8048 8159

Series 2 8273 8095 8111 8151

Series 3 8275 8082 8043 8201

















































































I've created a graph which the series link to a range with values.

If the values changes, the graph series will also change.

However, I forgot what I did I do the graph, and the series became hard coded with values.

Linked to ranges:
=SERIES("Previous year",Dashboard!$C$99:$C$110,Dashboard!$D$99:$D$110,1)

Hard-coded:
=SERIES("Previous year",Dashboard!$C$99:$C$110,{13170.0559978919,11302.4710116538,9805,29005,8851,10429,10207,10777,12370,12265,12933,14758},1)

Does anyone know how did the graph series changed from linked ranges to hard-coded values? So that I will be aware of my actions.


Hi

I have a loop that creates a chart for some data. The loop shifts the data down a column for the next iteration. I was stepping through the code and i saw this occuring:
1
adds chart
create new series
adds data to series
2
adds chart (this time it already has the same data as previous graph)
create new series (now a second series)
adds data to series (i.e. the previous data is overwritten, but now have empty second series)
3
adds chart (this time it is blank)
create new series
adds data to series

then loop 2 and 3 proceed to repeat giving every second graph an extra series. why??? any help would be appreciated

thanks