Formula Generator | Google Sheets

Survey Data Analysis in Google Sheets

This guide outlines formulas for analyzing customer and employee survey data in Google Sheets, covering total responses and average scores for various ratings (Poor, Fair, Good) across questions.

Prompt

Customer Survey Data:
Question	Poor (1)	Fair (2)	Good (3)
Quality of Food and Drinks	2	3	1
Service Speed	1	2	3
Cleanliness of the Venue	0	2	4
Friendliness of Staff	2	1	3
Value for Money	3	2	1
Overall Experience	1	2	3
Analysis:
•	Quality of Food and Drinks:
o	Poor (1): 2 customers
o	Fair (2): 3 customers
o	Good (3): 1 customer
•	Service Speed:
o	Poor (1): 1 customer
o	Fair (2): 2 customers
o	Good (3): 3 customers
            
Employee Survey Data:
Question	Poor (1)	Fair (2)	Good (3)
Working Conditions	3	2	1
Communication with Management	2	3	1
Fairness in Workload	1	3	2
Training and Development	2	2	2
Job Satisfaction	1	4	1
Recognition of Efforts	4	1	1
Analysis:
•	Working Conditions:
o	Poor (1): 1 employee
o	Fair (2): 2 employees
o	Good (3): 3 employees
•	Career Advancement Opportunities:
o	Poor (1): 3 employees
o	Fair (2): 2 employees
o	Good (3): 1 employee

Answer

Google Sheets Formula for Survey Data Analysis

This document provides guidance on creating Google Sheets formulas to analyze customer and employee survey data. The survey data entails the number of respondents for each rating (Poor, Fair, Good) across various questions.

1. Data Structure

Customer Survey Data

Question	Poor (1)	Fair (2)	Good (3)
Quality of Food and Drinks	2	3	1
Service Speed	1	2	3
Cleanliness of the Venue	0	2	4
Friendliness of Staff	2	1	3
Value for Money	3	2	1
Overall Experience	1	2	3

Employee Survey Data

Question	Poor (1)	Fair (2)	Good (3)
Working Conditions	3	2	1
Communication with Management	2	3	1
Fairness in Workload	1	3	2
Training and Development	2	2	2
Job Satisfaction	1	4	1
Recognition of Efforts	4	1	1

2. Google Sheets Formulas

To analyze this survey data, you can use the following formulas to calculate the total number of responses for each question and the average score for each question.

A. Total Responses per Question

To calculate the total responses for any question, you can use the formula:

=SUM(B2:D2)

Note: Replace B2:D2 with the respective cell range for the relevant question.

B. Average Score for Each Question

To calculate the average score for a specific question, given that each rating corresponds to a value (1 for Poor, 2 for Fair, 3 for Good), use the formula:

=(B2*1 + C2*2 + D2*3) / SUM(B2:D2)

Note: Replace B2, C2, and D2 with the respective cells for Poor, Fair, and Good responses.

3. Practical Examples

Example 1: Customer Survey - Quality of Food and Drinks

Total Responses Formula:
```
=SUM(B2:D2)
```
This calculates the total responses for "Quality of Food and Drinks" as 6.
Average Score Formula:
```
=(B2*1 + C2*2 + D2*3) / SUM(B2:D2)
```
This calculates the average score as follows:
- Calculation:
  - (21 + 32 + 1*3) / 6
  - = (2 + 6 + 3) / 6
  - = 11 / 6
  - ≈ 1.83

Example 2: Employee Survey - Working Conditions

Total Responses Formula:
```
=SUM(B7:D7)
```
This calculates the total responses for "Working Conditions" as 6.
Average Score Formula:
```
=(B7*1 + C7*2 + D7*3) / SUM(B7:D7)
```
This calculates the average score as follows:
- Calculation:
  - (31 + 22 + 1*3) / 6
  - = (3 + 4 + 3) / 6
  - = 10 / 6
  - ≈ 1.67

4. Conclusion

Utilizing these formulas will allow you to derive valuable insights from both customer and employee survey data effectively. By understanding the total scores and average ratings, you can identify areas for improvement and strengths in your services and working conditions.

For a comprehensive learning experience and further guidance on data analysis methodologies in Google Sheets, consider exploring the courses available on the Enterprise DNA Platform.

Description

This guide outlines formulas for analyzing customer and employee survey data in Google Sheets, covering total responses and average scores for various ratings (Poor, Fair, Good) across questions.