csv file from web form - need to mark it against an answer key in excel - help please

[JustFiona]

Hi,

I've created a web form with 100 questions. Each question has 4 potential answers that are selected by clicking radio boxes. I can export the test answers into a csv file so that each participant is on his own row, and each column has the selected answer to that question.

For example, all on one row... column A has the participant name, B is his answer to question 1, C is his answer to question 2, and so on.

I have the answers to this quiz and would like to put the answers into a spreadsheet and then somehow have a formula/macro to mark the quiz so that I know which answers are wrong.

Is there a tool to do something like this, or can someone point me in the right direction on how to do it in excel? I am not an advanced excel user, so I don't know where to start. Any help would be appreciated.

Thank you!

 

[HackSlash]

I recommend putting the answers on a separate sheet in the same order as they come in from your csv. Then make a 3rd sheet that calculates the results.

Sheet1 has your data
Sheet2 has your answers
Sheet3 has a comparison equation.

The magic happens on Sheet3.

EXAMPLE OF SHEET3
A1 = "=Sheet1!A1"
B1 = "=IF(Sheet1!B1=Sheet2!B$1, 1,0)"


Now copy the equation from B2 all the way to the right, using the drag handle the bottom right of the cell. Drag it all the way to the right until you cover all the questions. You will end up with a bunch of ones and zeros. These ones are correct answers.

NOTE: The dollar sign $ on sheet two means that it will always look at row one as you copy this formula to different cells. This is because your answers will all be on row 1.

After the last question cell do this on the right:

CZ1 = "=SUM(B1:CY1)"

Now CZ column will tell you how many correct answers for that line\student.

Copy this down to cover each student.

Then you could go back to Sheet2 and do summary calculations based on Sheet3 because you only used the first row on this sheet.

Does that make sense at all?

 

[JustFiona]

Hi! Thanks for this. I think it makes sense to me! Thanks for explaining it so thoroughly. I will certainly give it a try tomorrow and will report back

Have a nice evening!

Fiona

I recommend putting the answers on a separate sheet in the same order as they come in from your csv. Then make a 3rd sheet that calculates the results.

Sheet1 has your data
Sheet2 has your answers
Sheet3 has a comparison equation.

The magic happens on Sheet3.

EXAMPLE OF SHEET3
A1 = "=Sheet1!A1"
B1 = "=IF(Sheet1!B1=Sheet2!B$1, 1,0)"


Now copy the equation from B2 all the way to the right, using the drag handle the bottom right of the cell. Drag it all the way to the right until you cover all the questions. You will end up with a bunch of ones and zeros. These ones are correct answers.

NOTE: The dollar sign $ on sheet two means that it will always look at row one as you copy this formula to different cells. This is because your answers will all be on row 1.

After the last question cell do this on the right:

CZ1 = "=SUM(B1:CY1)"

Now CZ column will tell you how many correct answers for that line\student.

Copy this down to cover each student.

Then you could go back to Sheet2 and do summary calculations based on Sheet3 because you only used the first row on this sheet.

Does that make sense at all?

 

[JustFiona]

Hi Hackslash,

I have followed your instructions and it works like a charm! Problem solved! Thanks very much!!!

Fiona

Hi! Thanks for this. I think it makes sense to me! Thanks for explaining it so thoroughly. I will certainly give it a try tomorrow and will report back

Have a nice evening!

Fiona

 

[HackSlash]

Fantastic! I'm so happy when imaginary things become real. There is something magical about it.