Excel, If / Then or is there a better way?

[Closed]
Report
Posts
1
Registration date
Friday March 15, 2013
Status
Member
Last seen
March 15, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I have a spreadsheet that lists each kid in my class. I have entered the answer they gave for each and every question on a 40 quesiton test. (Choices were A,B,C or D)
Although there may be a better way, under each student I have inserted a row that contains If/then statements. If the student has answered correctly, it gives a "1", if they answered wrong, it gaves a "false". I thought I could then write up a formula to add up all of the "1""s to figure out their grade...but I don't think it looks at the "1" as a number. I've tried changing the format of the cell.
Summary:
1. I have 25 kids.
2. Each took a 40 question quiz.
3. Answers are either an A,B,C or a D (these are listed in Row 1 of the Spreadsheet)
4. I want to enter the answer they chose (A,B,C or a D)
5. I want a column to show the number of correct answers given
5. I want the final column to figure out their grade...(percentage correct out of 40 questions)

Please help!


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Zohaib has provided a solution. Since you asked "if there is a better way", i thought i would throw in my two cents too

1. Column A, Questions

2. Column B, Correct Answer for questions

3. Column C, Number of correct answers for a question by class. Kind of summary for a question

4. Column D:AB : just answer of student where each column is a student

5. Summarize results at bottom
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
Hi WMCAROLI,

I have used the following formula to evaluate the result for each question:

=IF(B2="A",1,0)

The answer is in Cell B2 which has a drop down that allows you to select from four options "A", "B", "C" and "D". The correct answer for this above question is "A", if "A" is selected from the drop down the result will be 1 else 0. This formula can be used for different answers also using the same logic. Because the result is either 1 or 0, it helps in getting aggregate of all the 40 questions.

I have prepared a sample sheet for two students you can extend it for the next 25. You can download the sample sheet from the below mentioned link:

http://speedy.sh/RU6QU/QuestionSheetfor25Studentand40Question.xlsx

Do reply with results.