Course / Challenges

Challenge: customer survey results

Score each survey statement, measure the share of happy customers, label the results and name the best and worst areas. Intermediate · 7 min · AVERAGE, COUNTIF, IFS, INDEX/MATCH, MAX / MIN

You run a small bike repair workshop and asked your last ten customers to rate seven statements from 1 (strongly disagree) to 5 (strongly agree). Each row of A1:K8 is a statement and each column is one customer.

You want a summary you can read in ten seconds: an average for each statement, the share of customers who were satisfied (a 4 or a 5), a one-word verdict, and the best and worst areas.

The verdict cut-offs are in B10:B11. Use those cells, so you can tighten the standard later without touching a formula.

Steps

1. In L2:L8, work out the average rating for each statement.

2. In M2:M8, show the share of customers who gave each statement a 4 or a 5, as a percentage.

3. In N2:N8, label each statement Satisfied, Neutral or Unhappy from its satisfied share, using the cut-offs in B10:B11. Use IFS rather than nesting IFs.

4. In B14 and B15, show the text of the statements with the highest and the lowest average. Use formulas, so they update when new responses come in.

Remember

• COUNTIF(range, ">=4") / COUNT(range) turns ratings into a satisfied share.
• An average can look fine while many customers are unhappy; report both.
• IFS tests conditions in order: put the strictest first and finish with TRUE.
• INDEX/MATCH with MAX or MIN names the best and worst rows.

Previous: Challenge: weekly payroll