Survey Score Formulas

Darla Brown

Hi!

We are launching a customer satisfaction survey this week. When completed, they will want a dashboard showing results. We have 5 departments with 2 questions each. The scoring is shown in screenshot below.

I need to create a formula that will tally results and also give average score. My problem is that it's not just numbers. I'm assuming I will need a helper column for each question. Is it an "IF/Then" formula? Suggestions?

Paul Newcome

Yes. You will need a helper column. But as long as your scores are single digits and always the leftmost character, you can use...

=VALUE(LEFT([Column Name]@row))

This will give you numbers you can average. Then to get a tally, you would use a COUNTIFS.

Paul Newcome

Yes. You will need a helper column. But as long as your scores are single digits and always the leftmost character, you can use...

=VALUE(LEFT([Column Name]@row))

This will give you numbers you can average. Then to get a tally, you would use a COUNTIFS.

Darla Brown

Yes! I was trying to do a multiple IF statement, but this works and is cleaner!

Thanks, as always, Paul!

Paul Newcome

Happy to help. 👍️