Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

| Customer ID | Survey Q1 | Survey Q2 | Survey Q3 | Survey Q4 | Survey Q5 |

|-------------|-----------|-----------|-----------|-----------|-----------|

| 1           | 4         | 3         | 5         | 4         | 2         |

| 2           | 5         | 4         | 5         | 3         | 4         |

| 3           | 3         | 2         | 4         | 3         | 5         |

| 4           | 5         | 3         | 4         | 5         | 5         |

| 5           | 4         | 5         | 3         | 4         | 4         |

| 6           | 3         | 4         | 5         | 3         | 3         |

| 7           | 2         | 3         | 2         | 4         | 2         |

| 8           | 4         | 5         | 4         | 5         | 4         |

| 9           | 5         | 4         | 5         | 3         | 5         |

| 10          | 3         | 2         | 4         | 3         | 5         |

 
Task :

1. Create a table to store customer survey data with columns for Customer ID, Survey Q1 to Q5.
2. Calculate the average score for each survey question using formulas.
3. Use COUNTIF function to count the number of customers giving a score of 5 for each survey question.
4. Create a radar chart to visualize the average scores for each survey question.
5. Use data validation to create a drop-down list for selecting different survey questions to analyze

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1.      Create a Table for Customer Survey Data:

• Enter the sample data into Excel starting from cell A1.
• Select the data range.
• Go to Insert tab > Table to convert the range into a table.

2.      Calculate Average Score for Each Survey Question:

• In cell B2 (next to "Survey Q1" column header), enter the formula =AVERAGE(B2:B4) to calculate the average score for Survey Q1.
• Copy the formula across to calculate averages for Survey Q2 to Q5 (C2 to F2).

3.      Use COUNTIF Function to Count Scores of 5:

• In cell B7 (next to "Survey Q1" under a new "Count of 5" header), enter the formula =COUNTIF(B2:B4, 5) to count the number of customers giving a score of 5 for Survey Q1.
• Copy the formula across to count for Survey Q2 to Q5 (C7 to F7).

4.      Create a Radar Chart to Visualize Average Scores:

• Select the range of average scores including headers (A1).
• Go to Insert tab > Charts group > Choose Radar > Select a suitable style.
• This chart visualizes how each survey question performs on average across all customers.

5.      Use Data Validation for Selecting Survey Questions:

• In a cell (for example, H1), go to Data tab > Data Tools group > Data Validation.
• Choose List under Allow, and in the Source box, enter Survey Q1,Survey Q2,Survey Q3,Survey Q4,Survey Q5 to create a dropdown list for selecting different survey questions.
• Use this dropdown to dynamically change the analysis based on the selected survey question.