## Recipe 5.3. Calculating Confidence Intervals## ProblemYou need to compute confidence intervals for certain estimates and you're not sure what support Excel provides for these calculations. ## SolutionExcel offers a built-in function called ## DiscussionIn Recipe 5.1, I showed you how to use Excel's built-in functions to compute summary statistics. I also showed you how to compute summary statistics using the Descriptive Statistics tool available from the Analysis ToolPak. In both cases, confidence intervals for the mean were computed and I pointed out that the confidence interval returned by Excel's The difference in the computed confidence interval is due to the fact that the
You can use ## See AlsoYou can use the formulas discussed in this recipe to compute confidence intervals for other estimates as well. In Recipe 8.8, I show you how to compute confidence intervals for regression curves. |

## Recipe 5.4. Correlating Data## ProblemYou're working with a multivariable problem and want to compute the correlation coefficient between variables. ## SolutionUse Excel's built-in functions ## DiscussionIn Recipe 8.4, I show you how to perform linear regression where the dependent variable depends on more than one independent variable. The example I discuss in that recipe involves six independent variables. This is a typical example where it's prudent to assess the correlation between independent variables before conducting the regression analysis. This way you can avoid using highly correlated (or colinear) independent variables, which can give you trouble. Excel provides two built-in functions that allow you to compute the Pearson product-moment correlation coefficients between variables. These functions are Figure 5-8 shows the data from the multiple linear regression example I discuss in Chapter 8. For now, I'll show you how to examine the correlation between independent variablesthe x variablesusing Excel's correlation functions as well as the Analysis ToolPak. To compute the correlation coefficient between variables ## Figure 5-8. Multiple linear regression dataTo save some time when dealing with numerous variables, you might consider using the Correlation tool available in the Analysis ToolPak. Select Tools images/U2192.jpg border=0> Data Analysis from the main menu bar to open the Data Analysis dialog box shown in Figure 5-9. ## Figure 5-9. Data Analysis Correlation toolSelect the Correlation tool and press OK to open the Correlation tool dialog box shown in Figure 5-10. ## Figure 5-10. Correlation tool dialog boxThe Correlation tool allows you to select multiple datasets at once; it will compute the correlation coefficients between all the datasets. For example, in Figure 5-10 I entered cells E6 through J22 in the Input Range. This cell range includes all of the x variables shown in Figure 5-8. I included the header labels in the input range, so I checked the "Labels in first row" option. Further, I specified cell M7 (on the same worksheet) as the starting cell where I'd like the output displayed. Figure 5-11 shows the results. ## Figure 5-11. Correlation resultsThis table presents the correlation coefficient between each given variable and every other variable. It also correlates each variable with itself, which of course results in a correlation coefficient of 1 (as you can see in Figure 5-11). Sometimes it's useful to compute There are other types of correlation coefficients common in statistical analysis. For example, For example, you can readily prepare a spreadsheet to compute the Spearman rank correlation coefficient between variables The first two columns in the table contain the raw data. The third and fourth columns contain the rank of each data point. I used the The formula for computing the Pearson rank correlation coefficient is: Here, d is the difference in ranks between corresponding values for both To compute d Cell G57 computes the sum of d ## Figure 5-12. Spearman rank correlation coefficient calculation## See AlsoTake a look at Recipe 8.4 to see the resulting regression model for this example dataset. Chapter 8 also discusses coefficients of determination in the context of least-squares curve fitting. Read Recipe 5.5 to learn more about |