CODEWITHSUNDEEP
excelexcel-formula
capitalf
capitalf

Reputation: 482

Average of multiple survey responses (columns)

I feel a bit embarrassed for asking this but here goes:

I'm using Excel 2010 and I have a worksheet containing 700+ customer satisfaction survey responses. Each row is a survey with a 1-5 or NA response to 5 questions. It looks like the following:

+-Agent--+--Q1--+--Q2--+--Q3--+
|        |      |      |      |
| Jeff   |  5   |  5   |  5   |
+--------+------+------+------+
| James  |  1   |  1   |  1   |
+--------+------+------+------+
| Jack   |  5   |  5   |  5   |
+--------+------+------+------+
| Jeff   |  3   |  NA  |  5   |
+--------+------+------+------+
| Jeff   |  NA  |  NA  |  3   |
+--------+------+------+------+
| James  |  5   |  5   |  5   |
+--------+------+------+------+
| ...    |  ... |  ... |  ... |
+--------+------+------+------+

I want to create a worksheet listing each agent in column A and the average of all of questions answered regarding them. I tried a formula like AVERAGEIF(SurveyResponses!A2:A7,A2,SurveyResponses!B2:D7) where A2 contains the agent's name, but it doesn't appear to work properly.

For example, I get a 5.00 average for some agents when it should be a 4.61. When I look in the Function Arguments screen for my AVERAGEIF on this person, it looks like it recognizes the values properly. The Average_rage shows {"NA","NA","NA","NA",1;5,5,5,5,5;5,... but the returned value below that says = 5 which is not right since there is a 1.

Can anyone guide me in the right direction?

Upvotes: 0

Views: 3310

Answers (2)

barry houdini
barry houdini

Reputation: 46341

AVERAGEIF works like SUMIF, the second range used is actually the same size and shape as the first range, starting with the top left cell, so when you use this

=AVERAGEIF(SurveyResponses!A2:A7,A2,SurveyResponses!B2:D7)

because the first range is a single column then the second range used must be too (there's a 1 to 1 relationship between the cells) so because the top left cell in SurveyResponses!B2:D7 is SurveyResponses!B2 the range begins there and is the same size and shape as SurveyResponses!A2:A7

....so you are actually getting this....

=AVERAGEIF(SurveyResponses!A2:A7,A2,SurveyResponses!B2:B7)

AVERAGEIF help does tell you that but it isn't very clear

If you want the ranges to be different sizes you need an "array formula" like this

=AVERAGE(IF(SurveyResponses!A2:A7=A2,SurveyResponses!B2:D7))

You need to confirm that with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar. That formula will count any blanks as zeroes but ignore text values like NA

Upvotes: 2

Cindy Brozyno
Cindy Brozyno

Reputation: 171

Easiest way to do this would be a pivot table. They look scary, but they're fairly easy to use. Rows = names, Columns = Q, Average for the answers.

Upvotes: 0

Related Questions