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           | 4         | 3         | 5         | 4         | 2         |

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

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

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

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

| 9           | 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 analyse

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

Step 1: Create a Table to Store Customer Survey Data

1. Enter Data: Enter your survey data into Excel with the following columns:
   • A1: Customer ID
   • B1: Survey Q1
   • C1: Survey Q2
   • D1: Survey Q3
   • E1: Survey Q4
   • F1: Survey Q5

Enter your data starting from A2 to F10 (assuming you have 9 customers as per your example).

2. Create Table: Select your data range including headers (A1

), then go to the Insert tab > Table.

Step 2: Calculate the Average Score for Each Survey Question

1. Formulas:
   • In cell G1, enter Average Q1.
   • In cell G2, enter the formula =AVERAGE(B2:B10).
   • Copy the formula from G2 to cells H2, I2, J2, and K2 to calculate averages for Q2 to Q5.

Step 3: Use COUNTIF Function to Count Scores of 5

1. Formulas:
   • In cell G3, enter Count 5 Q1.
   • In cell G4, enter =COUNTIF(B2:B10, 5).
   • Copy the formula from G4 to cells H4, I4, J4, and K4 to count scores of 5 for Q2 to Q5.

Step 4: Create a Radar Chart to Visualize Average Scores

1. Select Data:
   • Select cells G1 (Average scores).
   • Go to the Insert tab > Charts > Other Charts > Radar.
2. Customize Chart:
   • Right-click on the chart and choose Select Data.
   • Click Add under Legend Entries (Series).
   • For Series name, select cell G1 (Average Q1).
   • For Series values, select cells G2.
   • Click OK.

Step 5: Use Data Validation for Drop-Down List

1. Data Validation:
   • Select a cell where you want the drop-down list (e.g., A12).
   • Go to the Data tab > Data Tools > Data Validation.
   • Choose List under Allow.
   • In Source, enter =$B$1:$F$1 (assuming your survey questions headers are in row 1).
   • Click OK.
2. Analyze Data:
   • Use the drop-down list in cell A12 to select different survey questions.
   • The radar chart will update to show the average scores for the selected question.