Autopopulate a cell based on answer selected from drop down list

I have been trying to get this fixed for the last few hours but haven't had any luck. I have a survey created that I want to use Excel to deliver the survey and do some statistical functions. I have several questions, all of which require an answer from a drop down list that includes "Outstanding, Very good, Satisfactory, Limited, and None." What I want to do is create a column next to the response column that auto populates with a number that is associated with the response text… e.g. if I select "Outstanding" in A2, I want A3 to populate with "5", but if I select "None" in A2, I want A3 to populate with "1".

I got excited for a minute when I got so far as trying this =IF(A2="Outstanding",5) and it worked… but I still need to get the other four responses coded and I cannot get it to work.

Can someone give me a suggestion for how to get the cells to populate based on the following scale:

Outstanding = 5
Very good = 4
Satisfactory = 3
Limited = 2
None = 1

Many thanks!

Thanks Popipipo,

I was trying to remember how to do that.

However there are two typos in your formula,

Please Login or Register  to view this content.

Etaf, Exactly what I needed, THANK YOU!!!