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.
Formula was A1/5 =

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


If anything is not clear, please let me know.

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.