Can You Help Me Setting Up Automatic Scoring Of A Survey Set Up In Ms Excel

I would like your assistance.

I am creating a survey marking sheet and have array of different questions. I would like to have this done on MS Excel, however I am having difficulties with the formulas.

Column A consists of the question and column B is the scoring.

Within column B I would list a drop down list, which I am able to choose from 4 different options, i.e. Yes; No; Rated and N/A, however these respective text cells should be allocated to a score, i.e: if I select Yes it should score 5; if I select No, it should score 0, rated should score 2.5 and N/A should not score at all.

There are 15 questions, and from the above scoring it should automatically work out a percentage at the end.

I am able to work the formula’s separate, but I am having difficulty culminating it with the drop down list.

Please assist.