Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Plotting Mean and SD on the same Line graph

Hello,

I have a requirement in which I need to plot the average and the Standard Deviation (Error bar) in the same graph. I am using Excel 2010.

I am making a line graph. I am able to plot average easily but with standard deviation, I am facing problems.

For every point on the line graph, I have different standard deviation and when I select "Custom" on the Standard Deviation Options and I specify the range of SD values for each point, I get the SD lines of the same size and they are so huge that it crosses the chart itself. I am sure I can't select "Fixed", "Percentage" as each of the SD value is different corresponding to each point (average value) on the line graph.

Any help on this will be highly appreciated.

Thanks!

EDIT: Sample File Attached - As you see in the chart, the line graph is plotted based on the "Average" values, I need to plot SD on those points as well.


Post your answer or comment

comments powered by Disqus
Sorry the title is very bad - i want to plot 3 data sets on the same line graph- i want to connect the data points of the values within each data set, but i don't want to attach a line between each data set.

So i'm using the following data:

Time ---- 0 Hour Cultue--------2 Hour Culture--------18 Hour Culture
0 --------- 0.094 --------------------- 0.179 ----------------------1.11
20 ---------- 0.088--------------------- 0.28----------------------- 1.19
40 ---------- 0.075---------------------- 0.498------------------------ 1.29
60 ---------- 0.083 --------------------- 0.802 --------------------- 1.305
80 ---------- 0.106----------------------- 0.935 --------------------- 1.36
100 ----------- 0.117 --------------------- 1.033 ------------------------ 1.41

essentially i want to make an xy scatter line graph, and have all this information plotted together for form a standard bacterial growth curve. If you can get the idea from the data, the first data set was taken right away (0 hours) and it lasts for approx 2 hours. Then the 2nd data set starts and lasts again for another 2 hours approx. The last data set is after a huge gap in time (18 hours).

So I modified the data because i want the x axis to be time (in hours):

Time ------------Absorbance
0---------------- 0.094
0.3333-----------0.088
0.66666---------0.075
1----------------0.083
1.3333----------- 0.106
1.66666----------- 0.117

--------------------------- ( next data set - break in line graph)

2----------------- 0.179
2.3333------------ 0.28
2.33333------------0.498
3-------------------0.802
3.333333----------- 0.935
3.666666------------1.033

----------------------------- (next data set - break in line graph)

18---------------------- 1.11
18.33333---------------1.19
18.666666--------------1.29
19--------------------1.305
19.333333-------------- 1.36
19.666666---------------1.41

My problem is that when i graph this- using an xy scatter plot, i get all the points connected by lines. i would like however for there to be no lines connecting the data points ( x y value 1.666,.117 to 2,.179) or (xy value 3.666,1.03 to 18, 1.11). this way you can clearly see that 3 different experiments were conducted.

I'm pretty inexperienced with excel. sorry if this isn't explained very well.

How do I get excel to give an answer when two conditions must be met?
Basically, if L42>L43 and L43>0. If true, it goes on to a calculation. If
false, the result is supposed to be 0. I can get condition one to work,
but not both. Is it even possible to have two on the same line?
Mathmatically, x>y>0 is valad, is it not? Here is what has been tried so far:
if L42>L43 AND L43>0
if L42>L43 & L43>0
if L42>L43 + L43>0
if L43

I am trying to take a spreadsheet with a line for 2004, 2005, and 2006 and
put it in one line graph. I want all of the years on the same line in the
graph because I have several centers in the same spreadsheet on different
sheets that I want to put on the same graph. Is that possible

How do I get excel to give an answer when two conditions must be met?
Basically, if L42>L43 and L43>0. If true, it goes on to a calculation. If
false, the result is supposed to be 0. I can get condition one to work,
but not both. Is it even possible to have two on the same line?
Mathmatically, x>y>0 is valad, is it not? Here is what has been tried so far:
if L42>L43 AND L43>0
if L42>L43 & L43>0
if L42>L43 + L43>0
if L43<L42 and 0<L43

Anyone have any suggesations?
Thank you in advance for any help

Chart Label wraps on the same line. example "Wavelenght [nm]" appears as
"}Wavelenght [nm"

in general the chart labels are not what i typed.

I need help! I have spent hours working on a graph that includes the following output: 1) four lines that share an x and a y axis. 2) three different points on the same graph that need to share the same x and y axis as the line. Another detail that makes this complicated is that the ranges of the y and x axis can change based on inputs. If the ranges do not change, there at least one of the points or part of the lines will not appear on the graph. I would appreciate any help or guidance. I have not tried any VBA code as I am just an average user, but I can copy and paste code if I need to create a button. I am willing to learn fast. Thanks for any time and assistance you can provide.

I have plotted data A and thought it would be a case of drag and drop when plotting data B on the same graph

I am trying to plot 3 phase voltages and currents on the same xy scatter plot by using VBA. The 3 currents should have their own Y axis, and the 3 voltages should have their own Y axis. Both the collections need to be plotted on the same graph by sharing the X axis. I am able to get all 3 currents on their own graph, and I can plot the voltages on their own graph, but how can I get them together on the same graph?? I have manipulated Excel to where the X axis (time) information is in column A, currents are in columns B-D, and voltages are in columns E-G. Please see the attached document for an example of what I am trying to do. Thanks

Hello,

i would like to plot mean and standard deviation like this:

http://www.nzfooddaily.co.nz/Portals...ience-blog.jpg

I already have a list of mean and SDs, but i really dont have a clue on how to show them on a graph.

Thanks for any hint

Cheers!

Hi,

I have been trying to implement this for a while. My situation is this: My column E is dependent on column D. Column D is a drop down list independent of E. I can use VLOOKUP() to find my column E values once the user selects a value on D. BUT there is only ONE exception to this scenario, one of my column D values need to trigger a drop down list on column E since that value has multiple values associated with it. Following is an illustration:
Column-D Column-E
1 a
2 b
3 {x,y,z}
4 c
5 d
...
So if user selects 3 on column D then column E should give the user the option to select from a drop down (x,y,z will be the members of the drop down).
Now for drop down I know we need to use data validation. Also a cell cannot have data validation and VLOOKUP() at the same time, which probably does not make sense. In this case how can I implement this logic? probably some macro will be necessary.
Please share your thoughts. Any help will be much appreciated.

- SC

Hi all,
can anyone help me w/ a VLOOKUP (or Index, Match)

I have the following

A B C

SSY new 10
SSY old 12
SSH new 5
SSH old 20

I want to write a VLOOKUP to return the value in Column C
ONLY IF column A and B are true and they are on the SAME row

Data in B is not just old and new, columns A and B contain hundreds of different data, and a new one can pop up anytime.

So it would be helpful to have a VLOOKUP

Thanks

There are two different fields that are included in a database, one is Actual Start Date, the other is Actual Finish Date.

The field includes a time and date such as
2006-09-07 10:03:45.060

Is there a way to set it up so that it only pulls records that have an Actual Start and Actual Finsih time that are on the same day? I know the time is included, but I want to disregard time in the query.

I would figure I could somehow setup the query like this, I'm not sure how though, since I want it to only look at the day.

Thanks,

~J

excel.jpg

Hi,

I am trying to search for a string of numbers (column 2) in an array, and have "YES" be written on the same line in column 3 if the string is found in the names ANYWHERE in column 1. Please see the desired results on the picture in column 3.

I have tried many things, including SEARCH function which can only work with 1 cell not many, COUNTIF and more advanced functions, but I think have not succeeded because of my lack of knowledge in arrays.

Any help would be very much appreciated. Thanks!

Cheers,
Mart

Hi

I need to plot a graph with temperature on Y axis and the date and time values on the X axis. However, the date needs to be shown only when it changes. Time needs to be shown at regular intervals, e,g. on the grid line. I need to use the scatter smooth line with nomarkers option of MS-Excel. I am using MS Excel 2003.

I also need to be able to zoom selected region in the graph and unzoom it.

Can anyone show me how to code with VBA for the above.
Thanks in advance.
Bala

Word 2003 + Word 2007 on the Same Computer

Hey guys!

I was curious if it's possible to run Word 2003 and 2007 on the same computer, without having to setup each one each time. Right now I have 2003 and 2007 on the same computer as separate installations but when I open 2003 it says "Preparing to Install" takes 10 seconds and opens. Then when I open 2007 it says "Preparing to Install" behind the Splash Screen, and takes 3 minutes+ to open up. It also changes my file association each time.

The reason I need 2003 and 2007 is I have several files with built in wizards, that only work in 2003. In 2007 they do not function correctly. But I have tons of files that use 2007 also, so I really need to figure out how to get them to live together.

Any ideas?

Thank you very much!

I want to print rows 3, 9, and 18 on the same page. When I select them with
Control and try to print them, I get 3 pages. I also can "Set the Print
Area" but it also prints on 3 pages. Other than a workaround to copy and
paste to a new sheet, how can I get these 3 rows to print on one page? Thank
you. Mary

I want to print rows 3, 9, and 18 on the same page. When I select them with
Control and try to print them, I get 3 pages. I also can "Set the Print
Area" but it also prints on 3 pages. Other than a workaround to copy and
paste to a new sheet, how can I get these 3 rows to print on one page? Thank
you. Mary

Hi,

How can I count the number of consecutive empty cells on the same line in a cycle? More exactly I want to make a cycle that when encounters two consecutive empty cells in a specified row, do some task I ordered. How can I make this?

Regards

I would like to show a 2-D column graph and a 2-D line graph on the same chart.
The independent variable on the X-axis (name of employee) is the same for
both graphs.
The dependent variables on the y-axis are different:
(1) years of experience (column graph), (2) cumulative years of experience
(line graph).
I need to use two different scales for the y-axis to make both graphs
visible, as the year of experience of the employees ranges between 1 and 40
years and the cumulative years of experience ranges between 1 and 1500 years.
The input data for the graphs are listed in three columns of a single
worksheet.
Could anybody make a suggestion about how to combine the graphs?
Many thanks
Filippo

I am using stacked bar charts to show the various stages a patient goes through in their visit to the Doctor's office. Each stacked bar represents one patient. Most of the stages all follow in a linear sequence, but a few key points do not. For those, I am trying to add markers using line charts. So, there are the stages that follow one another, each with the time value for that particular patient, one stacked on the other, and then there are the markers indicating where the non-linear events occurred.

I add the non-linear events, such as when the MD walks into the exam room, as another series but change that series to a line chart instead of a bar chart, and take out the "line" but leave the marker.

I end up with stacked bars showing how long each patient waited at each stage, and a marker where the MD intervened (sometimes they come out to the waiting area, sometimes they see the patient early in their exam room, sometimes the patient waits in the exam room a long time before the MD enters --- the marker for the MD can fall anywhere on the stack).

The problem I am having is turning this sideways so that you can follow the stages left to right. You can select a sideways stacked bar chart, and that does show the stages for each patient from left to right. But it does not appear that excel allows you to do the same with the line chart series. Changing the chart type to a side-ways stacked bar only works for the bars, and not the line. So, you end up with line markers that are not related to the same axis as the stacked bars, and Excel now adds a second axis when you try to do this.

Is there a way to have both a sideways stacked bar chart with a line chart that also runs sideways on the same axis?

Hey there,

I have a vba procedure that determines the size of a dataset collected from another source, processes it, and builds embedded control charts for the user to view. All the formatting is set by code as well as the additon of the data series and labels. The graphs are set up right now to display the data on a line chart with a time-scale category axis. The category labels are set to named divisions of time (such as a shift colour) and are displayed after every 2nd tick mark. The categories have a predefined width, and the chart is made to widen as more datapoints are plotted.

What I'm trying to do now, is to display a second set of labels below (or above) the first on the same axis. These labels would denote the date on which the original labels fall (ex. Red shift - Jun 23).

I've found that Excel will do this to some extent just by selecting two rows containing the desired labels and setting those as the category labels (through Chart->source data). However, selecting the information this way causes Excel to "forget" the original tick mark spacing and labels, as well as extending the tick marks down to the bottom of the labels.

ie.
|---'---|---'---|---'---|---'---
Red _ _ Blue _ _ Green_ Red

Vs.
|---|---|---|---|---|---|---|---
|Jun| _ |Jun| __|Jun| _ |Jun|
|01 _ _ | 02 _ _ | 03 _ _| 04
| _ _ _ | _ _ _ _| _ _ _ _|
| Red _ | Blue _ | Green | Red

The date label gets compressed into representing only the first tick mark whereas the shift colour is spaced evenly between two. I'd like to have them both spaced evenly across the categories.

I can just turn off the tick marks and eliminate the lines, but this defeats the purpose of having them in the first place. Is there a way to programatically show both labels in the same fashion? The first label is set already through VBA from a range.

The end result would look like this:

|---'---|---'---|---'---|---'---
Red _ _ Blue _ _Green _Red
Jun 01 _Jun 02 _Jun 03 Jun04

SEE NEXT POST BELOW

I am a plotting an area and a line on seperate y-axes (a secondary axis) with different time periods, how do I get my lines to only correspond to the fitting times?

For example,
28/05/2009 07:01 2479.48 28/05/2009 08:05 10
28/05/2009 07:02 2479.48 28/05/2009 08:10 20
28/05/2009 07:03 2479.48 28/05/2009 08:15 30

At present, all three lines are starting at the zero point on the x-axis whereas as you can see they should start just after this point. As well as this there is only a limited range of options for x-axis changes as supposed to the usual full range.

Any advice is much appreciated.

Hello to all readers

Let's say I have a variable "Time" for x-axis. On vertical axis, I want to plot "Pressure" and "Temperature", such that Pressure axis/scale is on the left vertical axis and Temperature on the right vertical axis, both on the same plot. I have seen graphs like this but I don't know how to do this.
Can Excel do this? If not, which other program can do this? I have Matlab/Maple but I don't know if it can do that.

Thanks for your time

Hello everyone

I have a question about graphing. Let's say I have a variable A that I would plot on x-axis. Now I have 3 other variables B, C and D that I want to plot on y-axis, all in the same graph. The scale and units of these quantities are very different. E.g, temperature, pressure and voltage. Therefore, if I simply plot a chart, some curve would appear very small compared to the other 2.

I have seen graphs in books which have multiple y-axis next to each other, either all on left hand side of the chart or one on right hand side and one on the left hand, each axis has its own scale and divisions. I want to make such a chart. How can I do that in Excel?

Thanks to all the readers and respondents.


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