# DESPERATELY Need Help With Creating Text Function

##### New Member
I need help with a school assignment. The assignment has given me 3 students that have taken a multiple choice test with the answers either "a", "b" or "c" along with the answer key. I need a formula that does the following: "write functions that will grade each answer, giving 1 point for a correct answer and 0 otherwise. Assume that all answers are in lowercase letters, therefore, the function that tests the answer to the first question should check for a "c" rather than a "C". Basically, I need a formula that will display a "1" if the answer is correct and a "0" if it's incorrect. This is for an online school assignment and I've gone everywhere I can think of and the instructions on how to create this function just aren't there, not even in the 900+ page book. PLEASE HELP

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### riddenstudios

##### New Member
Are you planning on different worksheets for each student (would be the easiest) or putting it all onto one worksheet? Further more do you need to grade these in the worksheet as well?

#### Robert Mika

##### MrExcel MVP
I need help with a school assignment. The assignment has given me 3 students that have taken a multiple choice test with the answers either "a", "b" or "c" along with the answer key. I need a formula that does the following: "write functions that will grade each answer, giving 1 point for a correct answer and 0 otherwise. Assume that all answers are in lowercase letters, therefore, the function that tests the answer to the first question should check for a "c" rather than a "C". Basically, I need a formula that will display a "1" if the answer is correct and a "0" if it's incorrect. This is for an online school assignment and I've gone everywhere I can think of and the instructions on how to create this function just aren't there, not even in the 900+ page book. PLEASE HELP

Hello and welcome.
Does this help:

Excel 2010
AB
1c
2C
3C
4C3
5C
6c
7c
Sheet1
Cell Formulas
RangeFormula
B4=SUMPRODUCT(--(ISNUMBER(FIND("c",A1:A7,1))))

##### New Member
Are you planning on different worksheets for each student (would be the easiest) or putting it all onto one worksheet? Further more do you need to grade these in the worksheet as well?

They want it all on one sheet and I do need to grade them in the worksheet.

##### New Member
I will try it and let you know if it works. Thanks!

#### West Man

##### Well-known Member
on another sheet create a table of question # in column A and correct answer in column B. Use vlookup to get the correct answer, use an if to compare the student's answer to the correct answer. I have supplied an approach rather than a complete solution including the formulas so that you will do the actual work, learning from it.

#### riddenstudios

##### New Member
Most instructors use the machines or do it by hand. Then just input the grade points into the system. You can find a "grade example" under lookup function by going into function, type in lookup, click on help, go down to the bottom under "array" there is a good example of grading. You could do your points similar to this by the way you discribed. You could use combinations of the if statement to match answers to students answer 1, correct, 0 false...Then points earned add them up in the row. Total points is whatever the tot. points are, and the grade% just divide and then format to %.

 Answers c d a b Points earned Points Tot Grade % Student 1 c a b b 1 0 0 1 2 4 50% Student 2 c d a b 1 1 1 1 4 4 100% Student 3 c d b b 1 1 0 1 3 4 75%

<colgroup><col span="6"><col><col span="2"></colgroup><tbody>
</tbody>

Replies
1
Views
300
Replies
0
Views
423
Replies
3
Views
794
Replies
0
Views
583
Replies
1
Views
231

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back