Previous Topic Next Topic

Scores

Solved/Closed
R - Feb 26, 2010 at 06:47 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 1, 2010 at 05:29 AM
Hello,
Hi
I will sonn get a sheet of student exam outputs listing there choice of answer from a to p for each question. I have a space st the top of each question where i put the correct alphabetic choice. I would like to know how to tell excel to see the correct answer and convert into a numeric '1' value when correct and all else to 0.

regards
Richard

4 responses

Answer 1 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 26, 2010 at 08:03 AM
Are you looking to covert the student answer from letter to a number or just want to grade. If you want letter to be converted to grade inself in the same cell, the you have to use macro. Other side you can use formulas which can do the grading for you on the same sheet but different column or just on a different sheet all together.

On the side note, arent you being harsh to give a choice of A-P :)
0
Answer 2 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 26, 2010 at 01:31 PM
Not knowing how sheet looks like and how much of the data is there


This is a formula approach

Lets say your sheet1 would look like this 


            A           B                  C                 D
1                       c                  a                 b
2                     Ques 1         Ques  2         Ques 3
3   Student 1       a                 a                   b                  
4   Student 2       a                 d                   b
5   Student 3       c                  a                   b



On sheet 2

you can have

On cell A2
=IF(ISBLANK(Sheet1!A3), "", Sheet1!A3)
drag this formula down to the last student

on cell B3 enter
=IF(ISBLANK(Sheet1!B$1), "", IF(Sheet1!B3=Sheet1!B$1,1,0))

Drag this formula across all columns and rows to cover the question matrix

I am attaching a sample workbook
https://authentification.site/files/21153026/score.xls

Hope it helps
0
Answer 3 / 4
R
Mar 1, 2010 at 05:13 AM
Thanks that's exactly what i want.

One more simple question for a beginner.

if I want to sum across a row e.g ...=(B3+C3+D3+E3)
is there a way of doing this so I don't have to enter each letter?
I thought =+(B3:E3) would but it is wrong.

thanks
again for your original guidance.
Can you suggest a good excel book for idiots book with good examples?
The microsoft excel book is a hard work.

regards
Richard
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 1, 2010 at 05:19 AM
sure
write =sum(A1:A100) to add values between cells A1 and A100 (both including)
0
Answer 4 / 4
R
Mar 1, 2010 at 05:22 AM
Hi thanks

but I meant across a row...A1 + B1 + C1.....etc rather than down a column.


thanks
R
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 1, 2010 at 05:29 AM
it is same concept

you can add across columns like

to add a1 b1 ... z1
=sum(a1:z1)

or even like

to add a1,b1 c1.. z1, a2 b2... z100
=sum(a1:z100)
0