Converting text likert scale to numbers and getting a resulting average

[bjchecket]

I've been handed a spreadsheet with a set of rows for a group of employees, one employee per row. Along the rows are columns for ratings. Each rating is a data validation list selecting from one of 6 terms describing performance: "Strongly Agree," "Agree," "Neutral," "Disagree," "Strongly Disagree," "Too New to Rate." Each term needs to be assigned a numerical value (5-0 respectively) and then calculated to find the average rating in a final column at the end. (Example below

FirstName LastName Rating Item 1 Rating Item 2 Rating Item 3 Rating Item 4 Rating Item 5 Average Toni Jones Agree Strongly Agree Neutral Agree Disagree ??? etc... <tbody> </tbody>

<tbody>
</tbody>

The sheet goes on in this manner for approx. 30 employees or so. How do I: A) convert the terms to numbers, and; B) use those numbers to calculate a score?

 

[igold]

Hi bjchecket,

Welcome to the MrExcel Forum.

I have a couple of questions for you. First, are you looking for a formula or a macro for this. Second, as straightforward as this sounds, my business sense tells me that if someone submits the answer as "Too New to Rate" and you go ahead and assign that response as a zero and then do an average of the responses, the "Too New to Rate" response is in effect, going to falsely bring the average down.

 

[igold]

Assuming your Header Row is Row 1 and that the Names start in Cell A2 and your Average is in Cell G2, then this formula put in cell G2 and copied down should work for you.

=AVERAGE(LOOKUP(B2,{"Agree","Disagree","Neutral","Strongly Agree","Strongly Disagree","Too New to Rate"},{4,2,3,5,1,0}),LOOKUP(C2,{"Agree","Disagree","Neutral","Strongly Agree","Strongly Disagree","Too New to Rate"},{4,2,3,5,1,0}),LOOKUP(D2,{"Agree","Disagree","Neutral","Strongly Agree","Strongly Disagree","Too New to Rate"},{4,2,3,5,1,0}),LOOKUP(E2,{"Agree","Disagree","Neutral","Strongly Agree","Strongly Disagree","Too New to Rate"},{4,2,3,5,1,0}),LOOKUP(F2,{"Agree","Disagree","Neutral","Strongly Agree","Strongly Disagree","Too New to Rate"},{4,2,3,5,1,0}))

There is probably a more elegant way of doing this, but I hope this helps.

 

[bjchecket]

Absolutely brilliant! And works like a charm! Thank you so much.

 

[igold]

Happy to help. Thanks for the feedback.