Closed
Ended Data
Graham Tall
research@grahamtall.com
September 2003
Analysing closed ended questions (multiple choice/attitude questions/statements) can be done using SPSS or specially prepared spreadsheets (QUESTANAL.XLS or DATAANAL.WKS, both available from Graham Tall)..
I. Clarifying the Type of Numbers in the Columns of Data
II. Analysing Columns of Closed Ended Questionnaire Data Using Microsoft EXCEL
Using Questanal
Additional EXCEL files
III. Analysing Columns of Closed Ended Data Using SPSS
IV. Analysing Columns of Closed Ended Data Using Microsoft WORKS
I. Clarifying the Type of Numbers in the Columns of Data
This may seem a strange request, after all a number is a number - isn't it? See Types of Number
II. Analysing Columns of Closed Ended Questionnaire Data Using Microsoft EXCEL
Two versions of QUESTANAL.XLS have been prepared by Graham Tall; the first QUESTANAL-6.XLS is organised for 6 category attitude statements, the second QUESTANAL-5.XLS for five category attitude statements. QUESTANAL uses the functions available in EXCEL to provide a very speedy initial analysis of qhestionnaire data. The actual questionnaire needs to be entered, or copied, onto the sheet labelled "Questionnaire Data":
Row 1 Must contain an identification label for each column of data. With SPSS, the labels must: a) begin with a letter. b) be no more than 8 letters/numbers long.
Rows 2 to 251 are designed to contain questionnaire responses: All responses for each questionnaire must be on a single row. This program will count the frequencies of numbers 0 to 10 and responses 1.5 to 5.5. It will also count the frequency of single letter responses from A to G & M and whilst it will not record the number of negative responses, they like zero's and large numbers such as 99 will affect statistics such as number, mean, median, standard deviation, max and min. You are therefore strongly advised NOT to use a score like 0, -1 or 99 if the respondent hasn't answered a particular question, instead simply leave the response blank.
If more rows are required insert them between row 2 and row 252 because usually only questionnaires above row 252 will be analysed (Note: check the introduction sheet, because some versions of these progams analyse 1000 questionnaires).
Columns It is possible to analyse additional columns, though the "Analysis" sheet will need changing. To analyse extra columns, highlight column X and the additional columns required and select FILL RIGHT.
It is common practice to answer yes/no, Male/Female, questions using 1 and 2. If this data is to be analysed by SPSS with tests designed for interval data (eg. MANOVA), then, at that time, replace 1 and 2 with 0 and 1. Top of Page
Collations and Analyses in QUESTANAL:
The "Analysis" sheet reports the frequency of responses 0 to 10+ & A to G & M and the following statistics: number of responses, minimum score, maximum score, mean, median, mode & standard deviation for each column of the "Questionnaire Data" sheet.. A One-Way Chi square analysis, assuming 6 categories*, is carried out for all columns. Yates correction is used where the expected frequency is less than 5. BUT, remember, this analysis is only relevant to attitude questions!
The sheet labelled "2-Way Chi" in QUESTANAL-6.XLS will analyse ten, 6 category attitude questions using a single column group identifier. The group identifier column identifying gender, status, etc. must be copied into column A of "2-Way Chi". Attitude statement/question columns need to be copied into columns C to L of "2-Way Chi".
If there are more than 250 questionnaire rows, add new rows between rows 122 & 252. Highlight Row 121 and all new rows and FILL DOWN.
A separate file named CORRMAT.XLS will produce a correlation matrix for ten columns of data. The limitation of this file is that it cannot take account of missing data. Such data must be replaced as follows:
1. Consider deleting all data for that individual. Or,
2. If columns are marks, insert average mark for that column. Or,
3. If columns are attitude scores either as 2. above OR replace with middle category mark (no answer is likely to be equivalent to don't know { with a 6 category scale, therefore, replace with 3.5})
Additional EXCEL files, prepared by Graham Tall, available are:
| Interval Data: | ANVAR.XLS (Analysis of Variance), REGANAL.XLS (Regression Analysis & Analysis of Covariance), ANV2WAY.XLS (Two way Analysis of Variance). |
| Ordinal Data: Nominal Data: |
ORDINAL.XLS (Mann Whitney U test, Wilcoxon
Rank Sign and Spearman's Rho.) 1WAYCHI6, 2WAYCHI6 (Uses collated data, includes Yates Correction). See Chi |
III. Analysing Columns of Closed
Ended Data Using SPSS
(multiple choice questions, attitude questions or statements, exam/test marks)
SPSS provides a wide range of analyses. Its general structure fits normal Windows software parameters and hence whilst it is well worth working with an expert initially, it can be tackled by those who have confidence in tackling new software. The key point to recognise is that whilst SPSS can use data supplied by a wide range of spreadsheets, the SPSS file contains a single not a 'book' of sheets, thus if the researcher's spreadsheet is in 'book' format, the questionnaire spreadsheet must be saved as a single sheet (ie as EXCEL version 4 or earlier) with the top row containing an 8 letter/number code for each column of data (first digit must be a letter).
When EXCEL is opened, ensure that it is looking for the correct type of file "Files of Type" (see below), and once the file has been selected, remember to tick the "Read Variable names" (see below).
|
|
SPSS has two screens, one containing the data, the other containing results of any analyses etc. For anyone familiar with Windows, several of the options at the top of the screen (File, Edit, View, Data, Transform, Analyze, Graphs, Utilities, Window and Help) will be very familiar. Three which are particular valuable in analysing data are Data, Transform and Analyze. Data allows the researcher to "split" the file and analyse or compare, say, the reponses of male and female teachers. Transform allows the researcher to "recode" the data within one or more columns, thus all the data in attitude questions could be recoded or copied to fit a two or three category classification.
| a)
|
Assessment of Reliability eg.
split-half reliability and Cronbach's Alpha Reliability analysis both allow the
researcher to assess the reliability of measurement/atitude scales. Cronbach's
analysis compares each item with the whole group.
|
| b)
|
The key to carrying out comparisons is in the Analyze
(in earlier SPSS versions Statistics)
option:
|
IV. Analysing Columns of Closed Ended Data Using Microsoft WORKS
Copy each column individually and paste into column A of DATAANAL.WKS (available in statistics file in IT lab or direct from Graham Tall: research@grahamtall.com. If comparisons of different groups are necessary, create a separate file for each group. After each column has been pasted into DATAANAL.WKS print page 1 - the page will summarise the frequency of each number and give it in percentage format. IF the column is of an attitude question using 6 category format, the one-way chi-square information can be used to check the null hypothesis for 6, 3 and 3 categories.
Home Page Research
Introduction Questionnaires Quantitative Advice Index Statistical
Tests
Research and
Statistics Courses