This worksheet provides you with experience in calculating (by hand or using Excel®) formulas from the weekly readings. Using these formulas and performing these calculations provides you with an understanding of how and why statistical formulas work and what they mean. This worksheet also provides you the opportunity to interpret results in the context of specific problems, which assists you in critically evaluating current research in your field.
Getting Started with Excel, SPSS, and the TI-84 Calculator
USING EXCEL 2013 OR 2010
Installing the Analysis ToolPak Before you begin to use Excel to analyze data, you may need to install the Data Analysis ToolPak. For Excel 2013, this can be accomplished by launching Excel and then clicking on the File tab at the top left of the page. At the bottom of the drop-down menu, select Options.
After clicking on Options, a dialog box of options will appear. On the left-hand side of the dialog box is a list of options; click on Add-Ins to open a pop-up window. The very top option in the pop-up window should be Analysis ToolPak. Click on this and then GO.
A dialog box in which Analysis ToolPak is the first option will appear. Check this and then click OK.
The ToolPak should now be installed on your computer.
For Excel 2010, the process is similar. Begin by clicking on the File tab at the top left of the screen.
Next, click on Options toward the bottom of the drop-down menu. From here the process is similar to that for 2013. A dialog box of options will appear. On the left-hand side of the dialog box is a list of options; click on Add-Ins to open a pop-up window. The very top option in the pop-up window should be Analysis ToolPak. Click on this and then GO. A dialog box in which Analysis ToolPak is the first option will appear. Check this and then click OK.
Launching Excel When you open Excel, you will be presented with a spreadsheet.
You can see in the preceding worksheet where the File tab is, along with the Home, Data, and Worksheet tabs. The spreadsheet has a series of columns and rows. The columns are labeled with letters, and the rows with numbers. A cell is the point at which a column and row intersect. Thus, cell A1 is highlighted in the preceding screen capture. We typically refer to cells with the column letter first and the row number second (e.g., D5). We will be entering data into the cells for the analyses we’ll be conducting using Excel in subsequent modules. In addition, we’ll be using the Data Analysis ToolPak for most of our analyses. The ToolPak can be accessed when the Data ribbon is active (see the following screen capture).
When the Data ribbon is active, as in the preceding image, you can see the Data Analysis option at the upper right of the screen. This option indicates that the ToolPak has been installed properly. If the Data Analysis option is not visible at the top right of the ribbon, then the ToolPak was not installed properly (see previous instructions on installing the ToolPak).
We can enter data into Excel by simply entering the numbers in the cells. For example, in the following image, I’ve entered some annual salary data for a small business. You can see that I labeled the data Salary in the first cell and then entered the numerical data in a column below the heading.
If I want the data to have a different appearance—for example, to indicate that it is currency—I can format the number type through the Home ribbon, Number window. To begin, highlight the data in Column A (see the preceding image). Then activate the Home ribbon by double-clicking on it (this has been done in the preceding image). You should see the Number window in the top right-hand side of the Home ribbon in the preceding image.
You can activate the Number window by clicking on the arrow in the bottom right of the window; you will receive a dialog box as in the following screen capture that will allow you to format the way you want the numerical data to appear in the cells. Because these data are salaries, let’s choose Currency and then click OK.
The data in Column A now appear as currency as can be seen in the following image.
USING SPSS 18 THROUGH 22
To begin using SPSS, launch the SPSS program. When you initially start the program, you may receive a startup window such as the one below. Unless otherwise instructed by your professor, click Cancel. (Please note: SPSS 18 was marketed as PASW Statistics; it returned to being named SPSS in version 19)
You should now have a view of the data spreadsheet, or the Data Editor, similar to the following image.
The main menu in SPSS runs across the top of the Data Editor and gives you the options from File on the far left to Help on the far right. The icons that appear below the main menu provide some shortcut keys to allow you to do such things as Open Files, Save, Insert Cases, Insert Variable, and so on.
We’ll mainly be using the Analyze option to statistically analyze data. You can see the drop-down menu for the Analyze tab in the following image.
To enter the data into an SPSS spreadsheet, launch SPSS and enter the data into a column, as in the following spreadsheet in which I have entered the salary data from the previous Excel example. Given that I had already entered the data into Excel, I simply cut and pasted the data column into SPSS, minus the Salary heading.
Notice that the variable is simply named VAR0001. To rename the variable to something appropriate for your data set, click on the Variable View tab at the bottom left of the screen. You will see the Variable View window active in the following image.
Type the name you wish to give the variable in the first column (labeled Name). The variable name cannot have any spaces in it. Because these data represent salary data, we’ll type in Salary. You can also see that we can format this variable while the Variable View window is active by specifying the type of data, the width of the data, the number of decimals, and so on. To get back to the original spreadsheet, highlight the Data View tab at the bottom left of the screen. In the Data View window, we can now see that the Salary heading appears at the top of the column.
If I had not had data to paste into the Data Editor, I could have entered the data by highlighting each cell and typing in the data.
USING THE TI-84 CALCULATOR
Entering data into the TI-84 calculator is considerably easier than entering it into Excel or SPSS. With the calculator on, press the STAT key. EDIT should be highlighted at the top left of the screen along with 1:Edit in the first row under the menu. Press the ENTER key at the bottom right of the calculator. You should now have a spreadsheet with six columns labeled L1 through L6 (you may only be able to see L1 through L3, but if you scroll to the right, columns L4 through L6 become available). To enter data into L1, move the cursor to the first position under L1 and type in the data. Press ENTER after you type each number. Your data should now be entered into L1 and be displayed in the column under L1.
To clear the data from a column, highlight the column heading (for example, L1) and press the CLEAR key on the calculator. Never press the DEL key after highlighting a column heading because this action will delete the entire column. However, if you make this mistake, you can recover the column by pressing the STAT key, scrolling down to 5: SetUpEditor, and pressing ENTER. You will receive the command SetUpEditor with a flashing cursor. Press ENTER on the calculator, and you should receive the message DONE. Your variable should now be restored.
If you need help getting started with Excel or SPSS, please see Appendix C: Getting Started with Excel and SPSS.
MODULE 5 Measures of Variation
To begin using Excel to conduct this analysis, the data must be entered into an Excel spreadsheet. This simply involves opening Excel and entering the data into the spreadsheet.
You can see in the following image that I have entered the exam grade data from Table 5.1 into an Excel spreadsheet.
Once the data have been entered, we use the Data Analysis tool to calculate descriptive statistics. This is accomplished by clicking on the Data tab or ribbon and then clicking the Data Analysis icon on the far top right side of the window. Once the Data Analysis tab is active, a dialog box of options will appear (see next).
Select Descriptive Statistics as is indicated in the preceding box, and then click OK. This will lead to the following dialog box:
With the cursor in the Input Range box, highlight the data that you want analyzed from column A in the Excel spreadsheet so that they appear in the input range. In addition, check the Summary statistics box. Once you have done this, click OK. The summary statistics will appear in a new Worksheet, as seen next.
As you can see, there are several descriptive statistics reported, including measures of variation (range, standard deviation, and variance).
As with the Excel exercise above, we will once again be using the data from Table 5.1 to calculate measures of variation. We begin by entering the data from Table 5.1 into an SPSS spreadsheet. This simply involves opening SPSS and entering the data into the spreadsheet. You can see in the following image that I have entered the exam grade data from Table 5.1 into an SPSS spreadsheet:
Notice that the variable is simply named VAR00001. To rename the variable to something appropriate for your data set, click on the Variable View tab on the bottom left of the screen. You will see the following window:
Type the name you wish to give the variable in the highlighted Name box. The variable name cannot have any spaces in it. Because these data represent exam grade data, we’ll type in Examgrade. Note also that the Type of data is Numeric. Once the variable is named, highlight the Data View tab on the bottom left of the screen in order to get back to the data spreadsheet. Once you’ve navigated back to the data spreadsheet, click on the Analyze tab at the top of the screen and a drop-down menu with various statistical analyses will appear. Select Descriptive Statistics and then Descriptives…. The following dialog box will appear:
Examgrade will be highlighted, as above. Click on the arrow in the middle of the window and the Examgrade variable will be moved over to the Variables box. Then click on Options to receive the following dialog box:
You can see that the Mean, Standard Deviation, Minimum, and Maximum are all checked. However, you could select any of the descriptive statistics you want calculated. After making your selections, click Continue and then OK. The output will appear on a separate page as an Output file like the one below where you can see the minimum and maximum scores for this distribution along with the mean exam score of 74 and the standard deviation of 13.87. Please note that if you had more than one set of data, for example, two classes of exam scores, they could each occupy one column in your SPSS spreadsheet and you could conduct analyses on both variables at the same time. In this situation, separate descriptive statistics would be calculated for each data set.
Using the TI-84
Follow the steps below to use your TI-84 calculator to calculate the standard deviation for the data set from Table 5.1.
TI-84 Exercise: Calculation of σ (standard deviation for population) and s (estimated population standard deviation).
1.With the calculator on, press the STAT key.
2.EDIT will be highlighted. Press the ENTER key.
3.Under L1 enter the data from Table 5.1.
4.Press the STAT key once again and highlight CALC.
5.Number 1: 1—VAR STATS will be highlighted. Press ENTER.
6.Press ENTER once again.
Descriptive statistics for the single variable on which you entered data will be shown. The population standard deviation (σ) is indicated by the symbol σχ. The unbiased estimator of the population standard deviation (s) is indicated by the symbol Sχ.
MODULE 6 Standard Scores or z Scores
Using Excel to Determine z Scores
To illustrate how to calculate z scores, we’ll use the example in Module 6, also presented in the following table. We can see in the table the mean and standard deviation for two groups of students—one group who took an English exam and a second group who took a psychology exam.
Mean and standard deviation for English and psychology exams for two classes.
The scores for two of the students who took each of these exams follow:
|Person||English Exam||Psychology Exam|
We’ll use Excel to calculate the z scores for each of these individuals on the two exams. To calculate z scores using Excel, we use a function other than the Data Analysis ToolPak. Open Excel and click on the Formulas tab. You can see in the following Excel worksheet that this tab is highlighted:
We’ll start with the English exam data from the preceding table to calculate Rich’s z score. You can see from that table that the English exam had a mean of 85 and a standard deviation of 9.58. To calculate the first z score, click on the fx button on the far left side of the formulas ribbon. You should receive the following dialog box:
Make sure that Statistical is selected in the “Or select a category” field and then scroll down and select STANDARDIZE as in the preceding window. Finally select OK to receive the following dialog box. Enter Rich’s English exam score into the X box and the mean and standard deviation where indicated. Then click OK.
Excel will give you the preceding output, where you can see the z score of +0.6263 in the A2 cell. This z score indicates that Rich scored 0.63 standard deviations above the mean on the English exam. If we want to compare this score to his performance on the psychology exam, or to Debbie’s performance on the English exam, we must calculate these z scores also. Thus, to calculate Rich’s z score on the psychology exam we use the same procedure as above. Use this procedure to calculate Rich’s z score on the psychology exam and Debbie’s z scores for both the English and psychology exams. You should receive the following results:
Rich’s psychology exam z score = +1.03 Debbie’s English exam z score = −.31 Debbie’s psychology exam z score = + .44
Thus, we can see that although Rich’s English exam score was higher than his psychology exam score (91 vs. 88), his z score on the psychology exam was larger than his z score on the English exam, indicating that he was 1.03 standard deviations above the mean on the psychology exam, but only .63 standard deviation above the mean on the English exam. Debbie, on the other hand, had a negative z score on the English exam, indicating that she scored − .31 standard deviation below the class mean. However, her psychology z score was + .44 standard deviation above the mean of the class, even though her raw score on the psychology exam was lower than her raw score on the English exam (80 vs. 82).