Need Help with a Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jmsjr - 25 Apr 2008 15:19 GMT

I first created a spreadsheet with answers to a series of questions. Let's
say there are 10 questions and I want a final column to tabulate how many
questions are answered correctly. The questions could be done with
true/false or multiple
choice, whichever is easiest. What formula(s) should I use to do this? I
am thinking that the final column would display a number that is the sum of
the total number of right answers.

Reply to this Message

T. Valko - 25 Apr 2008 18:00 GMT

You would need to have an answer key and then you'd just compare the users
answers to this key.

Suppose the 10 questions are in the range A1:A10 and the user enters their
answers in the range B1:B10.

In some range (out of sight of the users) you'd list the correct answers for
each question. Let's assume you enter the correct answers in the range
AA1:AA10.

Then you can use a formula like this to get the number of correct answers:

=SUMPRODUCT(--(B1:B10=AA1:AA10))

Signature

Biff
Microsoft Excel MVP

>I first created a spreadsheet with answers to a series of questions. Let's
> say there are 10 questions and I want a final column to tabulate how many
[quoted text clipped - 4 lines]
> of
> the total number of right answers.

Reply to this Message

Gord Dibben - 25 Apr 2008 18:21 GMT

Easy enough to sum up correct answers with a formula like

=COUNTIF(A1:A10,"Y") which counts the number of questions with the answer Y

Sounds like you want something a little more complex which can probably be done.

But Excel needs some guidance on which is correct or incorrect answer.

You know which, so now just a matter of letting Excel know.

Gord Dibben MS Excel MVP

>I first created a spreadsheet with answers to a series of questions. Let's
>say there are 10 questions and I want a final column to tabulate how many
[quoted text clipped - 3 lines]
>am thinking that the final column would display a number that is the sum of
>the total number of right answers.

Reply to this Message

Rate this thread: