Need Help with a Formula

  • Thread starter Thread starter jmsjr
  • Start date Start date
J

jmsjr

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.
 
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))
 
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
 
Back
Top