How to calculate best with "words"

Dear Experts,
we have changed the score from 1 to 5 by words: Excellent, very good, good, sufficient and not sufficient.
I have defined data area with that values and the user can now choose only from a list with this values. In A1, A2, A3 etc.
In B1, B2, B3 etc. I have a simple formula which used so far the figure 1 to 5. Now I have words...
My first thought is to work with a IF formula with 5 cases but I'm sure there is a much simpler way.
thanks
Nils
Formula was A1/5 =


Petersburg1Asked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
So you should now use

=MATCH(A1,{"Excellent","very good","good","sufficient","not sufficient"},0)/5
0
 
Saqib Husain, SyedEngineerCommented:
=MATCH(A1,{"Excellent","very good","good","sufficient","not sufficient"},0)
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Assuming your values are in Column A, you can use a Countif formula as shown in the attached spreadsheet.

=COUNTIF(A:A,I4)*J4+COUNTIF(A:A,I5)*J5+COUNTIF(A:A,I6)*J6+COUNTIF(A:A,I7)*J7+COUNTIF(A:A,I8)*J8

If anything is not clear, please let me know.

Thanks,
Ardhendu
Ratings.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.