Q: Will I need to do calculations by hand?
A: Only very simple calculations will need to be done by hand. We will use Excel for the major statistical calculations (e.g., regressions, correlations). You will be responsible for interpreting the computer output and performing very simple calculations based on the output. For example, given a table of regression coefficients and standard errors, you should be able to construct a confidence interval.
Q: How do I install the statistical analysis functions for regression and correlation?
A: Go to the Tools menu, select Add-Ins, and install the Analysis ToolPak (and Analysis TookPak - VBA) . After you do this, there should be a “Data Analysis…” option under the Tools menu.
Q: How do I get Excel to perform regressions?
A: First, make sure the Analysis ToolPak has been installed (see above). To run a regression, select Data Analysis from the Tools menu and choose the Regression procedure. A dialog box will open that will allow you to select ranges of your worksheet defining the data for your dependent variable (Y, the outcome) and one or more independent variables (X, the predictors or regressors). If you include the names of your variables in the selected ranges, make sure you check the “Labels” box.
Q: How do I get Excel to calculate a single correlation coefficient?
A: If you want to calculate a single correlation in a cell of your Excel worksheet, you can use the CORREL function. Suppose your columns of data are in cells A1 thru A100 and B1 thru B100. Just type "=CORREL(A1:A100, B1:B100)" into a cell of your worksheet. Or, you can type in "=CORREL(" and then use your mouse to select the ranges of the worksheet that your data is in.
Q: How do I get Excel to calculate a correlation matrix (i.e., correlations among several variables)?
A: If you want an entire correlation matrix, you'll need to use the Analysis ToolPak. First, make sure the Analysis ToolPak has been installed (see above). To generate a correlation matrix, select Data Analysis from the Tools menu and choose the Correlation procedure. A dialog box will open that will allow you to select the range of your data. (Note that you need to select one continuous range; you can't select a series of unconnected columns.) Indicate whether your variables are in columns or rows (it will almost always be columns). If you include the names of your variables in the selected range, make sure you check the “Labels in First Row” box.
Q: How do I get Excel to create a scatterplot?
A: Select the columns of data you want to plot. Go to the Insert menu and select Chart. Pick the "XY (Scatter)" chart type and the chart sub-type that only shows points, and no lines connecting them. Continue through the rest of the Chart Wizard and add axis labels, legends, etc. as you like. You may have to play around with the Data Range panel in the second step ("Chart Source Data") to make sure Excel knows where the data resides, whether it's in rows or columns, etc.