Hello,
I am looking for a way (other than pivot tables) to average customer satisfaction survey results. Responses are given 1-5 and I need to average the total number of 5's for a category based on the total number of responses. With that said, not all questions are answered so I sometimes I have a blank answer and would not like to figure a blank answer into the average. I need to do this across State, Month and a time of day category (ex 7:00 AM - 11:59 AM and so on). I have been able to count the number of "5" answers and divide by the total number of survey returns, but like I said not all questions are answered so I need to base this on the number of answers given for that question. I figure I can do this with a COUNTIFS statement but the formula gets lengthy since I am counting multiple criteria in addition to 1-5 answers. Is there a way to do this with an AVERAGEIFS statement or possibly a better way? Here is my current formula that counts the number of 5 responses. Any help is greatly appreciated!
=COUNTIFS('Raw Data'!$A:$A,"Jan 2017",'Raw Data'!$C:$C,">=7:00 AM",'Raw Data'!$C:$C,"<=11:59 AM",'Raw Data'!$D:$D,"ARKANSAS",'Raw Data'!$L:$L,"5")
Column A = Month & Year
Column C = Time of Day Category
Column D = State
Column L = Satisfaction Question based on 1-5 that included blanks for no response given.
I am looking for a way (other than pivot tables) to average customer satisfaction survey results. Responses are given 1-5 and I need to average the total number of 5's for a category based on the total number of responses. With that said, not all questions are answered so I sometimes I have a blank answer and would not like to figure a blank answer into the average. I need to do this across State, Month and a time of day category (ex 7:00 AM - 11:59 AM and so on). I have been able to count the number of "5" answers and divide by the total number of survey returns, but like I said not all questions are answered so I need to base this on the number of answers given for that question. I figure I can do this with a COUNTIFS statement but the formula gets lengthy since I am counting multiple criteria in addition to 1-5 answers. Is there a way to do this with an AVERAGEIFS statement or possibly a better way? Here is my current formula that counts the number of 5 responses. Any help is greatly appreciated!
=COUNTIFS('Raw Data'!$A:$A,"Jan 2017",'Raw Data'!$C:$C,">=7:00 AM",'Raw Data'!$C:$C,"<=11:59 AM",'Raw Data'!$D:$D,"ARKANSAS",'Raw Data'!$L:$L,"5")
Column A = Month & Year
Column C = Time of Day Category
Column D = State
Column L = Satisfaction Question based on 1-5 that included blanks for no response given.