Covariance and variance

%3CLINGO-SUB%20id%3D%22lingo-sub-1625337%22%20slang%3D%22en-US%22%3ECovariance%20and%20variance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1625337%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20using%20the%20Covariance%20and%20Variance%20formulas%2C%20they%20give%20me%20the%20completely%20wrong%20numbers%20when%20I%20plot%20my%20data%20into%20the%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1625337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1626197%22%20slang%3D%22en-US%22%3ERe%3A%20Covariance%20and%20variance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778206%22%20target%3D%22_blank%22%3E%40VerenaSchwarzfischer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20give%20bit%20more%20details%20what%20exactly%20do%20you%20do%2C%20desirably%20with%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

When using the Covariance and Variance formulas, they give me the completely wrong numbers when I plot my data into the functions.

3 Replies
Highlighted

@VerenaSchwarzfischer 

Could you please give bit more details what exactly do you do, desirably with sample file.

Highlighted

@Sergei Baklan 

 

Basically, I need to calculate the Beta for Facebook stocks and Shell stocks. In order to do this, I need to calculate the variance using the Facebook stock returns and the covariance using the Facebook stock returns and the S&P 500. Then I can calculate the beta by using covariance/ variance. However, the answer that I get for the beta is 0.51 whereas the correct number is 0.81 for the Facebook stock. For the shell stock, excel gives me the answer of 0.43 but it's supposed to be 1.6. I checked where the mistakes were and saw that the values given through the covariance and variance formula are completely wrong. 

Highlighted

@VerenaSchwarzfischer 

That's easy to check with direct calculations:

image.png

COVARIANCE.S which you use returns correct result. Didn't check VAR.S and VAR.P but I believe they shall work, these functions were tested for years by millions of users.