Self Scoring Spreadsheets in Excel
·
Leave row 1 blank.
Method 1
|
|
A |
B |
C |
|
|
1 |
Question |
Student Answer |
Displayed Response |
This is the formula
that goes in Column C |
|
2 |
What is the capital of
Kansas? |
Topeka |
You got it! |
=IF(B2="Topeka","You got it!",(IF((ISBLANK(B2)),"","The
correct answer is Topeka"))) |
|
3 |
What is the capital of
Kansas? |
Omaha |
The correct answer is
Topeka. |
=IF(B3="Topeka","You got
it!",(IF((ISBLANK(B3)),"","The correct answer is
Topeka"))) |
|
4 |
What is the capital of
Kansas? |
Omaha |
Try again. |
=IF(B4="Topeka","You got
it!",(IF((ISBLANK(B4)),"","Try again."))) |
After entering the formula, be sure to hit Enter and not click on another cell.
Method 2 – Hidden
column
|
A |
B |
C |
D |
|
|
Question |
Answer key |
Your Answer |
Displayed Response |
|
|
1 + 1 = |
2 |
2 |
You got it! |
=IF(C4=B4,"You
got it!",(IF((ISBLANK(C4)),"","Sorry."))) |
|
1 + 1 = |
2 |
4 |
Sorry. |
=IF(C4=B4,"You
got it!",(IF((ISBLANK(C4)),"","Sorry."))) |
·
Select only the input area, right-click, and choose Format
Cells. On the Protection tab, remove the check next to Locked and click on OK.
·
Right-click the cell header of column B and choose Hide from the
pop-up menu. Add appropriate column headers in row 1.
·
In cell D2, enter the formula =IF(C2=B2,"YES","").
·
Copy that formula down column D for all of the question rows.
·
Below the last question row—still in column D—enter the formula
=COUNTIF(D2:D##,"YES"), where ## is the
number of the last question row.
·
Choose Tools | Protection | Protect sheet from the menu, uncheck
all the boxes except Select unlocked cells, and click on OK.
·
Enter a password when prompted, then save the sheet—and you're
done!
You can now give copies of this spreadsheet to others to use as a self-test. As
soon as a user enters an answer that matches the hidden correct answer, the
adjacent column will display YES. Uppercase and lowercase distinctions don't
matter, but a user must enter the precise words or numbers that you defined as
correct.
http://forums.computingondemand.com/index.php?showtopic=6057
http://www.sycd.co.uk/aka_science/pdf/gen-res/selftest/selftest_excel.pdf