Formula Generator | Excel

Excel Survey Data Analysis Guide

This guide provides Excel formulas to analyze customer and employee survey data, summarizing responses for rating categories (Poor, Fair, Good). It includes structured data examples and step-by-step instructions for calculating totals.

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

Excel Formula for Analyzing Customer and Employee Survey Data

This document outlines the Excel formulas to analyze survey data from customers and employees. The objective is to summarize and calculate the total number of responses for each rating category (Poor, Fair, Good) for both datasets.

Structure of the Survey Data

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

Excel Formulas for Analysis

1. Total Responses for Each Rating Category

To calculate the total responses for each rating across all questions for both customer and employee surveys, use the following formulas.

Formula for Total Poor Responses (Customer Survey)

=SUM(B2:B7)

Explanation: This formula sums the values in the 'Poor (1)' column (B2:B7) for all questions, providing the total number of poor responses.

Formula for Total Fair Responses (Customer Survey)

=SUM(C2:C7)

Explanation: This formula sums the values in the 'Fair (2)' column (C2:C7) for all questions, providing the total number of fair responses.

Formula for Total Good Responses (Customer Survey)

=SUM(D2:D7)

Explanation: This formula sums the values in the 'Good (3)' column (D2:D7) for all questions, providing the total number of good responses.

Formula for Total Poor Responses (Employee Survey)

=SUM(B10:B15)

Explanation: This formula sums the values in the 'Poor (1)' column (B10:B15) for all employee questions, giving the total number of poor responses.

Formula for Total Fair Responses (Employee Survey)

=SUM(C10:C15)

Explanation: This formula sums the values in the 'Fair (2)' column (C10:C15) for all employee questions.

Formula for Total Good Responses (Employee Survey)

=SUM(D10:D15)

Explanation: This formula sums the values in the 'Good (3)' column (D10:D15) for all employee questions.

Practical Example of Application

Assuming the data for customers is entered in cells A1 to D7, and the employee data is in A9 to D15, you would:

Input the customer survey data in specified cells.
Use the provided formulas in other cells (e.g., E1 for total poor responses) to calculate totals.
Repeat the same for the employee survey data starting from a new range (e.g., starting at E9).

Summary Formula Application

For Customer Survey Totals:
- Total Poor (1): =SUM(B2:B7) (cell E1)
- Total Fair (2): =SUM(C2:C7) (cell E2)
- Total Good (3): =SUM(D2:D7) (cell E3)
For Employee Survey Totals:
- Total Poor (1): =SUM(B10:B15) (cell E9)
- Total Fair (2): =SUM(C10:C15) (cell E10)
- Total Good (3): =SUM(D10:D15) (cell E11)

Conclusion

This structured approach allows for effective analysis of survey data, facilitating decision-making based on customer and employee feedback. For further improvement of data analysis skills, courses offered on the Enterprise DNA Platform may provide advanced training.

This guide should enable users to successfully analyze and summarize survey data with ease in Excel.