Excel for dummies - Covariance/Correlation question

I have the following two arrays

A		B
0.656762726	0.502636772
0.607062936	0.358819486
0.176421847	0.177496945
0.850282971	0.408820642
0.840389619	0.238850111
0.677780042	0.95826463
0.689123518	0.32826283
0.09339563	0.080583459
0.449812814	0.62147784

The covariance is 0.025759373 and the standard deviations are a: 
0.269721719, b: 0.263401137.

I would expect the correlation to be Cov(a,b) / (Stdev(a) * Stdev(b)) = 
0.362578234

However, Excel calculates the correlation as 0.407900513.

Please educate me.

Thanks,
Schiz
0
schiz (16)
8/11/2006 9:37:55 PM
excel 39879 articles. 2 followers. Follow

1 Replies
794 Views

Similar Articles

[PageSpeed] 17

Note that 0.407900513/0.362578234 = 1.125
And 9/8 = 1.125
Now look at the formulas in Help for COVAR, STDEV and CORREL and you will 
see where the 9/8 (or n/(n-1) ) comes from.
Had you used STDEVP (population std dev) the agreement would have been 
perfect.
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Schizoid Man" <schiz@sf.com> wrote in message 
news:ebitbn$ddt$1@geraldo.cc.utexas.edu...
>I have the following two arrays
>
> A B
> 0.656762726 0.502636772
> 0.607062936 0.358819486
> 0.176421847 0.177496945
> 0.850282971 0.408820642
> 0.840389619 0.238850111
> 0.677780042 0.95826463
> 0.689123518 0.32826283
> 0.09339563 0.080583459
> 0.449812814 0.62147784
>
> The covariance is 0.025759373 and the standard deviations are a: 
> 0.269721719, b: 0.263401137.
>
> I would expect the correlation to be Cov(a,b) / (Stdev(a) * Stdev(b)) = 
> 0.362578234
>
> However, Excel calculates the correlation as 0.407900513.
>
> Please educate me.
>
> Thanks,
> Schiz 


0
bliengme5824 (3040)
8/12/2006 1:09:00 PM
Reply:

Similar Artilces:

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

Writing formula for excel worksheet
Item Rate1 Rate2 Rate3 A 3 4 6 B 8 5 23 C 56 5 78 Above is my excel sheet. I want to write some formula so that in every row cell having minimum rate should come bold. Like For Item A - Rate1(3) for Item B - Rate2(5) For Item C - Rate2 (5) There may be a neater way but this works. Somewhere in you sheet in a cell type =min(A1:a4), I used L1 Change this to watever range you are trying to evaluate. Then select this range and click format - conditional format - cell value - is equal to =($L$1) and apply your bold font. The lowest value in the range will then become bold. Mike "Gaur...

Excel 2000 fail to open workbook
Hi, I have one Excel template. If we open it on Excel 2000, the Excel will hang, and the process occupy cpu 95% ~98%. But we can open this tempalte in Excel 2003 normally, and if we save this tempalte in Excel 2003, and then open it in Excel 2000, it work normally too. Excel 2000 (9.0.3821 SR-1) Excel 2003 (11.8231.8221) sp3 Would you help us to find the reason for this issue? Thanks, Nancy Hi Nancy, Perhaps there was some (probably minor) corruption/inconsistency in the workbook that Excel 2003 could fix but Excel 2000 couldn't. -- Cheers macropod [Microsoft MVP - Word] ...

Is this possible within Excel 2000?
Hi, I've used Excel on a get by basis for many years at work, and I'm just beginning to realise its vast capabilities, having viewed several groups which I didn't know existed previously. I subsequently would like to ask if it is possible for Excel to look at the numerical or text content of a cell on one worksheet and then look at a predefined range on another worksheet and find the equivalent cell content and then move the cursor to that cell? This has me stumped presently. Thanks in advance for any assistance. J.J. To actually move the selection, you'd need to use a litt...

Excel -> Access Import Bizarreness
A guy gave me an Excel file that he needs me to covert to XML. No problem. Slurp it into a database and use a DataSet.WriteXML() call to turn it into an XML file. But I can't import it into Access because it has "Merged Cells". For example, data that should look like this: ColA ColB ----- ----- Rec1 this Rec2 this Rec3 this Rec4 that Rec5 Other The three "this" cells have been merged into one tall cell: ColA ColB ----- ----- Rec1 this Rec2 Rec3 Rec4 that Rec5 Other I need to "unmerge" these, but I know almost nothi...

simple question #3
Hi, I have a strange bug which I need help. The problem is I caculate the m_xscale and m_yscale in OnOK, but when I call GetInfo with the same dlg object, the m_xscale and m_yscale are not the value I saved in OnOK. It seems OnOk did't save the value to the dialog object. Can anyone see what's wrong? The following are my code. Thanks. CCalibratDlg dlg( boost::numeric_cast<float>(line_width), boost::numeric_cast<float>(line_height), xscale, yscale, unitType ); if( dlg.DoModal( ) == IDOK ) { float xScale, yScale; int unitType; dlg.GetInfo( xScale, yScale, unitTy...

Question regarding instal
Im the It Director at a Mortgage company and we want to use CRM 3.0 I think I have a problem though based on the CRM edition I have. First of I have a Domain already running on Server 2003 Enterprise Edition. The CRM 3.0 I have is Small Business Edition. From what I have read I have to install this on Small business Server 2003. That's not a problem I have Small Business server 2003 Standard and Premium. I think my problem is this Small Business Edition wants to be a Domain Controller and Active Directory. As I said I already have this running. ..I installed Server 2003 Small Buisness Pre...

Enumerate Excel Global variables
Anyone know if there's a way to enumerate the global variables within a workbook when opening it for automation? I know that you can write a macro in a general module that exposes a variable and then call that macro via: Excel.Application.Run(MacroName) But what if you have no control over the development of the Excel workbook and cannot dictate that a macro be written to expose the variable you're interested in? In fact in the case I'm dealing with I don't even know the name of the variable. All I know is that the workbook may have global variables of a COM component of a c...

Safari History Question
Safari v.3.2.1 OS 10.4.11 Hi, If I "Clear History" every day, does that list of items I visited that day still remain in Bookmarks (or somewhere) recording all places I have ever visited? The reason I ask is that I want to retain those places for future reference for research without going to the trouble of going through the steps of "Add Bookmark Folder" � "Add Bookmark" everytime I want to refer back to a site I visited. I find that time consuming even though I can categorize rather than have a long uncategorized list of history items. I have a program calle...

Headers in Excel #3
Can you tell me if it's possible and if so, how I can change the header cells, i.e. A B C D E ... etc? Thanks. Andrea (508) 842-3880 Hi Andrea no this is not possible. The best workaround you can achieve is: - use row 1 as header - goto cell A2 and to the menu 'Windows - Freeze Panes' to always show row 1 - in 'Tools - Options - view' you can hide the row and column headers -- Regards Frank Kabel Frankfurt, Germany "Andrea" <shrews.receptionist@nitco-lift.com> schrieb im Newsbeitrag news:12ff301c44354$b88982b0$a501280a@phx.gbl... > Can you tell ...

Error upon launching excel
I receive the following error every time I launch Excel (Office 200 Pro): Forward-most box: MICROSOFT VISUAL BASIC - Compile error: Can't find project or library (OK) I click OK, and behind it is: Large window: MICROSOFT VISUAL BASIC - GWXL97.XLA [break] small window within: GWXL97.XLA - Startup Macro (Code) I have to ok my way through these every time I attempt to start Excel, either by selecting the program, or selecting an *.xls file to launch it. Please help. Chris A reply from Rob Bovey (via google): This is the add-in for Novel Groupwise. It sounds like it was removed f...

Opening Excel '97
I have MS Excel '97 on my computer at work. I notice that when I open multiple excel files the program just puts all of the files in one window. I would prefer to have the files open up in individual windows. Is there a way to tell Excel to open everything in a new window? I know that I can just open up a new Excel window then open the file, but I would rather just click on the file and have it open a new window automatically. Any thoughts out there? xl2k was the first version to offer Tools|Options|view tab|windows in taskbar. It showed each window as a different icon on the ...

E-mail Part of excel sheet
How do you select and e-mail part of a work sheet, each time i try i get the whole sheets as part of the e-mail On Sun, 19 Oct 2003 01:52:02 -0700, "Ron" <anonymous@discussions.microsoft.com> wrote in microsoft.public.excel.misc: >How do you select and e-mail part of a work sheet, each >time i try i get the whole sheets as part of the e-mail I don't think it can readily be done programmatically. However, if you use an e-mail client which supports messages in HTML (I use Outlook), you can copy/paste a part of your worksheet into the body of the message. To create...

Sharing an excel spreadsheet
I am trying to make an Excel spreadsheet so that people from different departments can put information into it and then we can save it as one spreadsheet -- my hope is that from this spreadsheet we could see at any particular time just where this particular item is in our manufacturing process or when it left. I have put the spreadsheet in a shared folder and mapped a drive to the spreadsheeet. I am using 2007 and the other people are using 2003. I have made saved the file so that it is in compatibility mode. They can put the information in, but they can not save the information to ...

ADO FIND question
I have a VB program accessing non-indexed tables (by primary key). I'm using the RECORDSET.OPEN / RECORDSET.FIND syntax, coupled with RECORDSET.MOVENEXT. I would like to be able to stop the quesry when the last record with my specified value is found. How can I do that when the field I'm using is not indexed (even though it's a key field)? Any insight would be appreciated. Thanks! ...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

Excel example-formula C4+C5 adds before data in C5 Can I get it t.
I'm trying to create a formula (for example): In cell D5 I want it to add C4+C5. I would like it to work only after there is data in C5, but not before. How can I get it to work only when there is data in C4 & C5? Right now its showing what I have in C4. =if(count(c4:c5)<2,"Not enough values!",c4+c5) New Excel User wrote: > > I'm trying to create a formula (for example): > In cell D5 I want it to add C4+C5. I would like it to work only after there > is data in C5, but not before. How can I get it to work only when there is > data in C4 & C5? R...

Exchange 5.5 to 2003 Migration Question #3
Hello, We are currently migrating users to Exchange 2003 from 5.5. The 5.5 server is located here and the new 2003 server is located in a different office. One user (who we have moved already) is trying to access and user's calendar, but that user is still on 5.5. They are getting the following error: unable to display the folder. The information store could not be opened Any ideas on how to make the user on 2003 (running Outlook 2003) connect to the user's calendar on the 5.5 server? The user has the "Owner" permission on the 5.5 calendar. Thanks! ...

how do i enable dde in excel?
i am posting a DDE link into an excel cell from an external program and getting the message ' DDE Not enabled' in the cell once the link is pasted in. how do i enable DDE? ...

Jon Peltier
Jon, How would you do this with a Pivot Table? I'm not a Pivot Table novice and I just can't envision how this one would be done. Thanks, Barb Hi Barb - I didn't see this question at first, but I responded in the original thread. I didn't try to replicate the OP's one column setup from his first post, because it didn't match the more logical arrangement in his post in the other forum. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Barb Reinhardt wrote: > Jon, > ...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

reports question #2
Hi How do I generate a report that will tell me how many store credits (or Gift Certificates) have been issued/redeemed in a givin time frame? I have a report "Detailed Sales with Tax & Tender" but the only problem is it does not show more than one tender type per transaction. So if a customer combines two tender types only one is shown. Thanks throw me a bone?...anyone cheers "christian" wrote: > Hi > > How do I generate a report that will tell me how many store credits (or Gift > Certificates) have been issued/redeemed in a givin time frame? &...

New server question
I just installed a new exchange server on my network and it seems to work great for reciving and sending external mail but the users on the old server can not send mail to users on the new server. They are both in the same domain. I have check the relay restrinctions and nothing pops out at me. users get an error that says: Relaying denied. Proper authentication required TIA these servers are in the same routing group? -- Susan Conkey [MVP] "Josh" <Jsawyer@insightbb.com> wrote in message news:eICByC66FHA.4036@TK2MSFTNGP11.phx.gbl... > I just installed a new ex...