scores Solved/Closed

Question

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

rizvisa1 · Answer

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 :)

rizvisa1 · Answer

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

R · Answer

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

R · Answer

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

Scores

4 responses

Subscribe To Our Newsletter!