Microsoft Excel supplies several easy-to-use statistical functions for just such a purpose says bestselling computer book author Stephen L. Nelson
Excel provides useful statistical functions for measuring correlation between two variables.
As a reminder, the benefit of using a correlation coefficient to measure the relationship between two variables as opposed to using covariance is that the unit of measurement doesn’t matter.
But a caution: Remember that correlation does not show causation. That is, you could easily show that as the number of ice cream cones consumed increases during a year, so does the number of drownings. But this does not mean that eating ice cream causes people to drown—more likely, these variables are both independently related to another variable—that of temperatures. Correlation is symmetrical, so you get the same coefficient if you switch the variables. Don’t calculate a correlation coefficient if you manipulated one of the variables. Use linear regression instead.
CORREL
You use the CORREL function in Excel to determine whether two data sets are related, and if so, how strongly. The correlation coefficient ranges from +1, indicating a perfect positive linear relationship, to –1, indicating a perfectly negative linear relationship. To calculate a correlation coefficient for a sample, Excel uses the covariance of the samples and the standard deviations of each sample. To use the CORREL function in Excel, just select the two sets of data to use as the arguments and use the following syntax:
=CORREL(data set 1,data set 2)
For example, if you have a set of preliminary test scores for a sample of employees in column A and a set of performance feedback scores in column B, as shown in Figure 4-6, and you want to find out whether they’re related and if so, how strongly, you can use Excel to find the correlation coefficient for the samples.
The function returns the value 0.87, indicating that the sets are positively related (as the value of one goes up, the value of the other also increases), but the relationship isn’t perfect.
PEARSON
The Pearson product moment correlation coefficient function, PEARSON, uses a different equation for calculating the correlation coefficient. This formula doesn’t require the computation of each deviation from the mean. Still, the correlation coefficient ranges from +1, indicating a perfect positive linear relationship, to –1, indicating a perfectly negative linear relationship. The PEARSON function uses the following syntax:
=PEARSON(data set 1,data set 2)
Using the PEARSON function on the data shown in Figure 4-6 to compute the correlation coefficient returns the same value as the CORREL function does.
RSQ
The RSQ function calculates the square of the Pearson product moment correlation coefficient through data points in the data sets. You can interpret the r-squared value as the proportion of the variance in y attributable to the variance in x. The RSQ function uses the following syntax:
=RSQ(data set 1,data set 2)
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
