pivot chart, mean and standard error

hello,
I'm using a pivot table to calculate the mean and the stdevp of values 
collected in different condition.
My problem is to draw the corresponding chart for the means of the 
values, and using the stdevp values for the error bars.
Using a stacked histogram, I have something which resembles to what I 
would like but I would prefer to have a classical error bar. Is there a 
way to manage ? I want to keep something automatic and not static, with 
a copy/paste values to draw manually my charts.
Thanks for your help
-- 
Misange migrateuse http://www.excelabo.net
mail : http://cerbermail.com/?k5Q8Dh2mta
0
misange (3)
5/12/2004 8:37:56 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
497 Views

Similar Articles

[PageSpeed] 26

:-( No ideas ?

Misange migrateuse http://www.excelabo.net
mail : http://cerbermail.com/?k5Q8Dh2mta

  le 12/05/2004 10:37:
> hello,
> I'm using a pivot table to calculate the mean and the stdevp of values 
> collected in different condition.
> My problem is to draw the corresponding chart for the means of the 
> values, and using the stdevp values for the error bars.
> Using a stacked histogram, I have something which resembles to what I 
> would like but I would prefer to have a classical error bar. Is there a 
> way to manage ? I want to keep something automatic and not static, with 
> a copy/paste values to draw manually my charts.
> Thanks for your help
0
misange (3)
5/13/2004 7:02:29 AM
You will have to make a regular chart from the data, because pivot 
charts aren't flexible enough to manage this. Select a blank cell that 
is surrounded by blank cells (so Excel won't try to guess which data you 
meant to select) and start the chart wizard. In step 1, select the chart 
type you want for the mean values: bar, column, line, whatever. In step 
2, select the ranges for the values and category labels using the Series 
tab. In step 3, click on the error bars tab, click in the Custom + box 
and select the range with the standard deviation values. Click in the 
Custom - box and select the same range. Hi OK a couple more times and 
you've got your chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Misange wrote:

> hello,
> I'm using a pivot table to calculate the mean and the stdevp of values 
> collected in different condition.
> My problem is to draw the corresponding chart for the means of the 
> values, and using the stdevp values for the error bars.
> Using a stacked histogram, I have something which resembles to what I 
> would like but I would prefer to have a classical error bar. Is there a 
> way to manage ? I want to keep something automatic and not static, with 
> a copy/paste values to draw manually my charts.
> Thanks for your help

0
DOjonNOT (619)
5/15/2004 1:17:02 AM
Hi Jon,
The advantage of your solution is that it always work. And you can 
arrange your data in the pivot table as you want.
GeeDee in the french newsgroup had another suggestion wich works 
although it's not perfect : the data in the pivot table have to be 
organized so that the columns contain side by side only the mean and the 
stdevp values. This is OK but means that you loose the flexibility to 
move your row or column labels...
The chart will then show side by side 2 bars, one for the mean, one for 
the stdevp. Create a custom graph : Hide this second bar with an 
appropriate format. Then double clic on the mean serie, and in the error 
bars tab, select the proper range, which is in the pivot table.
This custom format has to be applied each time the table is updated (or 
with a macro).
I am often stuck with the problem to synchronize mean and standard deviation
Thank you for your help Jon.

Misange migrateuse http://www.excelabo.net
mail : http://cerbermail.com/?k5Q8Dh2mta

  le 15/05/2004 03:17:
> You will have to make a regular chart from the data, because pivot 
> charts aren't flexible enough to manage this. Select a blank cell that 
> is surrounded by blank cells (so Excel won't try to guess which data you 
> meant to select) and start the chart wizard. In step 1, select the chart 
> type you want for the mean values: bar, column, line, whatever. In step 
> 2, select the ranges for the values and category labels using the Series 
> tab. In step 3, click on the error bars tab, click in the Custom + box 
> and select the range with the standard deviation values. Click in the 
> Custom - box and select the same range. Hi OK a couple more times and 
> you've got your chart.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> Misange wrote:
> 
>> hello,
>> I'm using a pivot table to calculate the mean and the stdevp of values 
>> collected in different condition.
>> My problem is to draw the corresponding chart for the means of the 
>> values, and using the stdevp values for the error bars.
>> Using a stacked histogram, I have something which resembles to what I 
>> would like but I would prefer to have a classical error bar. Is there 
>> a way to manage ? I want to keep something automatic and not static, 
>> with a copy/paste values to draw manually my charts.
>> Thanks for your help
> 
> 
0
misange (3)
5/17/2004 7:27:16 AM
Reply:

Similar Artilces:

Great Plains Basic, Standard, Professional. . . which one?
There are three versions of Great Plains: basic, standard, and professional. -Which one works best with RMS, or is there even a difference among the three? -Will all of them allow sync'ing of open and closed POs? -What, in general, are the price breaks for those three versions? David David, You need to work directly with a Great Plains partner. Implementing GP is no small task, and your GP Partner will be able to assess your workflow needs and show you which version and modules for GP would work best for you. If you need a referral, let me know. -- Mobitech Lady Amy Luby Mobite...

Move Mailbox errors
I'm currently migrating from Exchange 2000 to Exchange 2003. We're in a native mode Windows 2003 AD. I am migrating about 500 mailbox from a standalone Exchange 2000 to a WIndows 2003 / Exchange 2003 Active/passive cluster configuration. Mailbox moves have been going great up until yesterday. While moving the users in the normal way,2 mailboxes hung at 100%. In the "state" column, it says: "Saving changes to the directory". I let it sit at this stage for more than 10 hours. I then have to cancel the task, then it says cancel running tasks, and this wil...

outlook 2007 error message in archive folders
for no discernable reason some and only some of my archive folders will no longer open and give the following error message: the message interface has returned an unknown error. If the problem persists restart Oultlook I have restarted not only outlook but rebooted my computer with no positive result. I have also gone back a month to a restore point but this has not changed the position. As I say it is only some files and then only some subfiles in larger files. One month ago there were no issues. I have added no new programs to the computer Try running scanpst.exe against y...

Callouts in Charts
Does anyone know a way to keep the pointer of a callout assigned to a point in a graph? I am trying to add additional data points to graphs and my callouts don't follow the point. Is there a way to do this without moving the call out every time? You could use a datalabel instead of a callout. Not the same visual effect but... If you do decide to go that route, you may want to use Bovey's XY Chartlabeler (www.appspro.com) or Walkenbach's ChartTools (www.j- walk.com). -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS O...

How to change pivot table source database?
How can I change the source database for a pivot table in an Excel spreadsheet. Our database server has been changed and when I create a new dsn pointing to the correct server, I get an error message indicating that the query on the old server is still trying to be used. Thanks, Steve This should work for you (?) Open the pivot table and click on it. Menu - Data/Pivot Table report. Click the Back button on the Wizard dialog that appears and connect t the new data source -- Message posted from http://www.ExcelForum.com ...

Error Number: 0x800ccc0e when I try to get messages
Hello, I am a postmaster of our network and I am having some problems with Outlook 2000 and our pop3 server. The problem is that randomly, Outlook's users can not download their mail because they get the error number 0x800ccc0e when they try to send amd receive mail. We are using Microsoft Outlook 2000 SP-3 (9.0.0.6627) and our server is a Linux Debian running a 2.4.27 kernel. I have tried with ipop3d and in.qpopper as a pop3 daemons and with both I get the error. There is no firewall between them and this is the tcpdump capture from the server (our pop3 server runs at port 40110, bu...

80040e19 error occurring with 2nd NIC installed
I am experiencing the 80040e19 error when expanding public folders in Exchange System Manager, Exchange 2000. I have exhausted all suggested MS articles and *still* have not resolved the issue. No evidence of URLScan or iislockdown being run on the server. Strange thing is is that when I disable the newly installed NIC through device manager the public folders can be expanded perfectly. On enabling the NIC, same error. The server is the PDC, Exchange and Intranet server, running IIS on port 80 for local intranet site and the admin website on port 7715. I have checked the host head...

refresh pivot tables
if i have my pivot table not set to refresh on open. is there a way to refresh all of them at once. when i want them to be refreshed? does F9 do this? from the help, F9 says this: Calculates all worksheets in all open workbooks. F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+...

Diagonal Pivot Table?
Hi, I currently have data that looks something like A | B | C 1 | 1 | 5 1 | 2 | 9 2 | 1 | 7 2 | 2 | 4 (but obviosuly many times bigger) If I put this in a pivot table, with A and B on the axis, it would loo something like: _ | 1 | 2 1 | 5 | 9 2 | 7 | 4 This is because it counts (1,2) as different from (2,1). Is there an way to concatonate the two? i.e.: _ | 1 | 2 1 | 5 | 2 | 8 | 4 Let me knwo if this doesn't make sense. Thanks -- Message posted from http://www.ExcelForum.com Wait, cancel that! Turns out I don't need to worry. thanks anyway, - -- Message posted from http://...

space then date causes error
On my spreadsheet they have to type in a date and other calculations are taken from this point. Of the hundreds that have used it there have been no problems, however as always there will always be one....... When they fill in the date they type a space and then type the date, this throws up errors in the other calculations. Not altogether important but is there an easy fix for this, so that if anyone types a space it is ignored, I dont want to get involved in VBA/macro stuff. Right click the sheet tab>view code>insert this>modify range to suit Private Sub Worksheet_Change(By...

Copy sheet in Excel
Hi Wonder if anyone of you encounter this problem before? When I try to copy worksheet in a workbook, the following prompt comes up, indicating name conflict. "A formula or sheet you want to move or copy contains the name 'aaaa', which already exists on the destination worksheet. Do you want to use this version of the name? - To use the name as defined in the destination sheet, click Yes - To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box" When I clicked yes, another prompt comes up indicating ...

ameritrade plus error
When I try to download my information from ameritrade plus i get a invalid org error. Anyone else experience this problem. Help Please.. >When I try to download my information from ameritrade >plus i get a invalid org error. Anyone else experience >this problem? YES......EVERYONE!!!!!!!!! ...

Money2002 Setup Error
When I try to setup (windows98) I get the following: "This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor." Here are the details: SETUP executed an invalid instruction in module ADVPACK.DLL at 015f:715f5858. Registers: EAX=0066cf04 CS=015f EIP=715f5858 EFLGS=00010206 EBX=0066d364 SS=0167 ESP=0066cdf4 EBP=0066d014 ECX=0066f8d0 DS=0167 ESI=00000348 FS=6017 EDX=0044003c ES=0167 EDI=0066d350 GS=0000 Bytes at CS:EIP: fe ff ff 50 ff 15 68 11 5f 71 56 8d 85 fc fe ff Stack dump: 00000080 bff77b14 71604004 00667000 0...

American Funds performance chart is incorrect
All of my American Funds mutual funds have incorrect performance chart. Basically, they show the account value dropping to 0.00 each night and then popping up to the correct value the next day. So the chart is not helpful at all; it's just a bunch of radical zig-zags. ...

Error Occured Trying to Promote this email
This happens when trying to promote a email to CRM activity. Can anyone help? Bob ...

New User
Using Outlook 2003 with Exchange Server 2000 on XP Pro SP2 - all service packs updated. Attempted to setup a new user account on this system because previous employee has left and need new profile established. When attempt to setup email through Exchange and click 'Check Name' we get the following error: C:\Windows\System32\shell32.dll, Control_RunDLL C:\Program~1\Common~1\system\MSMAPI\1033\MLCFG32.cpl' "@0" The new user account will work fine on another system on the network, so the problem is not with Exchange but with this local system. Have tried deleting the pro...

Bubble Chart Labels
I am using the "Series Name" as the data label for bubbles in my bubble chart. I want to add a second data label for each bubble, but I don't see an option for a second data label in excel. Do you know a work around? You might try Ron Bovey's XY Chart Labeler http://www.appspro.com/ "Aggie SB" <Aggie SB@discussions.microsoft.com> wrote in message news:E881ABC0-5D70-437F-BF27-D6DBE67EEAD8@microsoft.com... > I am using the "Series Name" as the data label for bubbles in my bubble > chart. I want to add a second data label for each bubble, ...

Publisher doc shuts down, no reason, cannot reopen. error
I work in Microsoft Publisher all the time with my publications. Today, my document shut down after saving and when I try to open it, I get a "cannot open Publisher file" message. Help! I have to get my newsletters out today! Thanks How to troubleshoot a damaged publication in Publisher http://support.microsoft.com/kb/198256/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Charlene" <Charlene@discussions.microsoft.com> wrote in message news:3B4F44C3-02EF-4D86-AD96-95866109453C@microsoft.com... >...

XML Serialization : Error reflecting field.... #2
I have to deserialize an XML document to objects and then serialize it back to XML to pass to the stored proc. I am attaching partial code. After this , I also have to serialize Here is the XML : <data> <orders> <order> <order_id>0</order_id> <issue_id>4460</issue_id> <action>add</action> </order> </orders> </data> This is the main.cs file where I call the Deserializer public class Main { public void AcceptOrdersDom( string strInputXML ) { OrderRootDeSerialize ordRoot =(OrderRootDeSerializ...

Misleading VS.NET 2003 error message
I got this error message compiler under VS.NET 2003 (no, I haven't tried it under 2005) ParseTrace.cpp i:\mvp_tips\Parser\RDParser\ParseTrace.h(42) : error C2226: syntax error : unexpected type 'BOOL' i:\mvp_tips\Parser\RDParser\ParseTrace.h(42) : error C2238: unexpected token(s) preceding ';' from the following class definition class AFX_EXT_CLASS StackTrace { public: StackTrace(BOOL pushing, TreeNode * n); // <= error was on this line ~StackTrace(); CString ToString(); }; Now try to guess what is wrong. What is wrong is that I forget to ...

Dynamic Charts #9
Does the charts have to be on the same worksheet as the data when creating dynamic charts? Hi dbaggett, No, all the techniques described here work with charts on different sheets. If you are having trouble writing the series formula, try building a chart on the same sheet as the data, then cut and paste the chart onto a different sheet. Generally, you need to enter the chart series as =SERIES(Sheet1!ser_nm,Sheet1!x_range,Sheet1!y_range,1) Where the named ranges are; ser_nm - the series name x_range - obvious y_range Ed Ferrero http://www.edferrero.com > Does the charts have to be ...

Please help
Only one of my users (who us utilizing IE8) is getting the following error when creating a new Timesheet in PWA 'g_oMainForm_name' is null or not an object mytssummary.aspx Code: 0 URI:http://[project server]/mytssummary.aspx Many other folks are using Timesheets - some with IE8 - and it works fine. This is the only user so far who has reported this. Any help would be greatly appreciated. Thanks! Andy Novak UNT Hi Andy, Maybe this will help: http://allfaq.org/forums/t/136987.aspx "anovak@unt.edu" wrote: > Only one of my users (who us...

Pivot Chart???
I'm trying to create a chart showing the number of orders per week fo each of our customers. Each row (excluding header) in my Data Worksheet is an order wit Client Name and Week# that the order was placed in. I have a pivot table with "Client Name" in the page field and Week and total (count) displaying correctly. I need the X axis to remai static for all 52 weeks (with labels) and not adjust and show just th weeks with data. I tried creating 2 more columns to chart from. Week (a list wit values of 1 - 52) and Total. I'm trying to pull data from the pivo table to fill...

Formula you typed contains an ERROR message
Hi Everyone - I am trying to enter the following formula: ***************************************************************** =IF($C7="SIL",0, IF((AND(OR($D7="Whole Shelled",$D7="Ground Shelled"),$R7<15.5,$R7>=0)),1, IF((AND($D7="Ground Cob",($R7-5)<15.5,$R7>=0)),0.5165, IF((AND($D7="Whole Shelled",$R7>=50)),0.5572, IF((AND($D7="Ground Shelled",$R7>=50)),0.6288, IF((AND($D7="Ground Cob",($R7-5)>50)),0.3016, IF(AND($D7="Ground Cob",$R7-5<=50,$R7-5>=15.5), INDEX(Tables!$A$131:$D$166,MATCH(($...

UTC time to standard time
I have an sql database that I am importing data from, into excel. One of the fields has a 32 bit integer that represents the number of seconds elapsed from 1/1/70 to current time based on UTC. I would like to convert this to a standard excel date and time. Anyone know an easy way to do this. I know I could write a function that would use divison and modela functions to arrive at the answer, but I thought there might be an easier way. Any suggestions appreciated. Dennis You could use this formula =integer/86400+"01/01/1970" can be written as =integer/86400+25569 ...