Hi, I am stuck with a problem I am trying to solve. I have a series of multiple choice questions which all have 5 answers. A score is allocated to each answer as follows...
A=0
B=1
C=2
D=3
E=9
I need to calculate a total score. If anyone could help me with this I would really appreciate it as I am stuck. Thanks in advance.
It would help if you attached a sample Excel workbook - details of how to do this are given in the yellow banner at the top of the screen.
Pete
depending on the layout , so a spreadsheet attached as described in the yellow banner at top of thread would be better
A lookup(), Choose(), Switch () or even sumproduct() may all work
Or a SUMIF maybe ()
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you for replying. I have found a solution although as I am a novice I do not not if it is the best solution. I have attached a workbook.
glad you have a solution
Create a lookup table on another worksheet with all the possible MC answers and their scores, then you can use a VLOOKUP to generate the score column from that.
Your title is misleading - it's not really about the adding, as far as I can see.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yeah, that's great advice AliGW. Much better for me to use VLOOKUP. Thank you.
There are often many ways of achieving a certain result in Excel, so it is difficult at times to know what is the "best solution". It could be said that if it works then stick with it.
However, there are many functions in Excel that overcome the need for multiple IFs in a formula. Many of these require a list or table to be set up, and then search through that set of data to return some corresponding value. The advantage of doing this is that the data items can easily be amended if required, whereas if they are embedded in the formula like yours are, then the formula itself will need to be changed (and you then have to re-apply the formula to the other cells where it appears). I see also that you have embedded the list of choices within your data validation drop-down, and again this would be awkward to edit if it were necessary. Instead, you could set the DV to look directly at the list of data items.
So, one approach would be to list your data choices in order of "value" (let's say in column L), and next to them you could have the appropriate values in column M, and then your multiple-IF formula could become:
=VLOOKUP(G3,$L$1:$M$6,2,0)
which can then be copied down. In your data validation you could have as your list:
=$L$1:$L$6
Hope this helps.
Pete
Yes brilliant Pete. I'm very happy that I looked on this forum for assistance.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks