+ Reply to Thread
Results 1 to 9 of 9

How to add up a multiple choice quiz with different score for each answer

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    Wales
    MS-Off Ver
    Office 365
    Posts
    4

    How to add up a multiple choice quiz with different score for each answer

    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.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,794

    Re: How to add up a multiple choice quiz with different score for each answer

    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

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,333

    Re: How to add up a multiple choice quiz with different score for each answer

    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.

  4. #4
    Registered User
    Join Date
    02-23-2021
    Location
    Wales
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to add up a multiple choice quiz with different score for each answer

    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.
    Attached Files Attached Files

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,333

    Re: How to add up a multiple choice quiz with different score for each answer

    glad you have a solution

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,473

    Re: How to add up a multiple choice quiz with different score for each answer

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    02-23-2021
    Location
    Wales
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to add up a multiple choice quiz with different score for each answer

    Yeah, that's great advice AliGW. Much better for me to use VLOOKUP. Thank you.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,794

    Re: How to add up a multiple choice quiz with different score for each answer

    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

  9. #9
    Registered User
    Join Date
    02-23-2021
    Location
    Wales
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to add up a multiple choice quiz with different score for each answer

    Yes brilliant Pete. I'm very happy that I looked on this forum for assistance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to make a multiple choice, 10 question quiz using UserForm
    By pvega96 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2019, 02:04 AM
  2. Multiple-Choice and True-False Test/Quiz Maker
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2018, 09:56 AM
  3. Make an multiple choice quiz with randomized choice
    By shaterroi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2015, 11:04 AM
  4. multiple choice answer
    By Getfothers in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2014, 09:54 AM
  5. [SOLVED] Making a multiple choice quiz
    By supern0va in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2012, 11:40 AM
  6. Replies: 5
    Last Post: 03-26-2012, 10:42 AM
  7. how do u set up multiple choice answer buttons/dots?
    By pam123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-01-2006, 08:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1