Formulas and Functions

Chapter 13 Additional Examples and Topics 363
Function and arguments Description of result
=CORREL(B2:B11, C2:C11) Determines the correlation of question 1 and
question 2 using linear regression analysis.
Correlation is a measure of how much two
variables (in this case, answers to survey
questions) change together. Specically, this
would look at the question: If a respondent
answered question 1 with a higher (or lower)
value than the average for question 1, did the
respondent also answer question 2 with a higher
(or lower) value than the average for question
2? In this case, the responses are not particularly
well correlated (-0.1732)
=COUNT(A2:A11) or =COUNTA(A2:A11) Determines the total number of surveys returned
(10). Note that if the survey control identier was
not numeric, you would need to use COUNTA
instead of COUNT.
=COUNT(B2:B11) or =COUNTA(B2:B11) Determines the total number of responses to the
rst question (9). By extending this formula across
the row, you could determine the total number
of responses to each question. Because all the
data is numeric, COUNTA returns the same results.
If, however, the survey had used A through E,
instead of 1 through 5, you would need to use
COUNTA to tally the results.
=COUNTBLANK(B2:B11) Determines the number of empty cells,
representing invalid or no answers. If you
extended this formula across the row you would
nd that question 3 (column D) had 3 invalid or
not-answered responses. This might cause you
to look at this question on the survey to see
if it was controversial or poorly worded, as no
other question had more than 1 incorrect or not-
answered response.
=COUNTIF(B2:B11, “=5”) Determines the number of respondents that gave
a 5 to a particular question (in this case, question
1). If you extended this formula across the row,
you would learn that only questions 1 and 4 had
any respondents give the question a 5. Had the
survey used A through E for the range, you would
have used =COUNTIF(B2:B11, “=E”).