Sep 01 2020 07:07 AM
Hello,
When using the Covariance and Variance formulas, they give me the completely wrong numbers when I plot my data into the functions.
Sep 01 2020 01:26 PM
Could you please give bit more details what exactly do you do, desirably with sample file.
Sep 02 2020 01:54 AM
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.
Sep 02 2020 01:00 PM
That's easy to check with direct calculations:
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.