Up

           Computer Analysis Using Excel

This activity involves computing descriptive statistics and correlations using the NCA survey, "How America Communicates."   You may complete the assignment by using either SPSS or Excel. If you wish to use SPSS, do not  use this page, but go to the page Computer Analysis with SPSS.

As a starting point, we need a data set for analysis. 

bulletIf you have not already done so, go to the Data Sets location and download the file nca.xls under the category, "NCA Study of  'How Americans Communicate.'"   If this source no longer is available from the NCA website, then, download the file NCA12.XLS from the "Excel Files" category on the "Data Sets" webpage.
bulletStart your Excel program.  From the "File" menu load the data file "NCA12.XLS" from the location where the you saved it.

Of all the elements in the survey, we will examine two variables of interest:  amount of education and comfort giving a public speech.  The amount of education question is "Q18" and involved asking the respondents "What was the last level of education you have completed?" (responses range from 1=less than high school graduate to 7=Ph.D or equivalent).  The comfort giving a public speech question is "Q6speech" and asks "How comfortable do you feel giving a presentation or speech?" (responses range from 1=not at all comfortable to 4=very comfortable).

We will want to complete a descriptive analysis of the data and a Pearson correlation.

bulletSome data definition will be required.  Students should go to the column that corresponds to variable "Q6speech" (column AW) and find all cells that contain "###" or "#NULL!" and enter delete from keyboard to replace these items with a blank (blanks are found on rows 239, 428, 779, and 900).  Students also should go to the column for variable "Q18" (column CN) and find all cells that contain "###" or "#NULL!" and enter delete from keyboard to replace these items with a blank (these items are found on rows 179, 237, 328, 433, 751, 779, 791, 851, and 922).  If you leave the data cleaning at that--and the assignment does--missing values (0 for the Q6speech" variables and 8 for the "Q18" variable) will not be separated out.  To get a missing value, Excel requires that it, in fact, is missing.  Thus, it is important to go through the data and replace all missing values codes with blanks. Both sets of results will be reported here.  Of course, for ease of instruction, you have not been asked to delete all the items that are truly missing values, but the "cleaning" is sufficient for out purposes.
bulletTo begin this descriptive output, click on the column label for "Q6speech."   Click the "Tools" menu, and on the subsequent menu that appears, click on "Data Analysis."  In the dialog box titled "Data Analysis" select "Descriptive Statistics" and click on "OK."

In the dialog box for "Input Range" enter "$AW$2:" [AW is the column in which Q6speech appears and row 2 is the first row of data] and AW1002 [the location where the output should be placed on the spreadsheet.  Click on "Summary Statistics" and OK.

To complete this descriptive output, click on the column label for "Q18."   Click "Tools" menu, and on the subsequent menu that appears, click on "Data Analysis."  In the dialog box titled "Data Analysis" select "Descriptive Statistics" and click "OK." In the dialog box for "Descriptive Statistics" in the "Input Range" enter "$CP$2:" [CP is the column in which Q18 appears and row 2 is the first row of data) and "CP$1002" (the last row or data is row 1002).  Check the box to indicate "Labels in First Row."  Under "Output Options," click "Output Range:" and enter $CP$1005 (to indicate the location where output should be placed on the spreadsheet).  Click "Summary Statistics" and "OK."  This dialog box should appear as follows:

 

 

 

 

 

 

 

 

 

 

bulletWith Excel the two variables correlated must be in adjacent columns (Note: this condition is not true if you use the "Insert . . . Functions" menu commands instead of the data analysis options under the "Tools" menu).  Thus, you will have to move one variable.  To do so, one first must create a blank column.   Click on the column label at AX (to the right of the "Q6speech" variable).  Click on the "Insert" menu and then click on "Column."

Next, go to variable Q18 (now at column CQ).  Click on the column label to highlight the column.  From the "Edit" menu click on "Cut."  Go to the new blank column at AX and click on the column label to highlight the column.  From the "Edit" menu, click on "Paste."   
bulletNow, to run the correlation, click on the "Tools" menu and then click on Data Analysis."  In the dialog box titled "Data Analysis" select "Correlation" and click "OK."

In the dialog box for "Correlation" in the "Input Range" enter "$AW$2:" [AW is the column in which Q6speech appears and row 2 is the first row of data] and "$AX$1002" [the variable to be correlated is in column AX and the last piece of data in it is found in row 1002].  Check the box to indicate "Labels in First Row."

 

 

 

 

 

Under "Output Options," click "Output Range:" and enter $AW$1005" (to indicate the location where output should be placed on the spreadsheet).  Click "OK."

Print out your results and answer this question:  What is the correlation between these variables?