One
of the tasks we have on EdTech 2 is one where each group will create an interactive
test utilizing Microsoft Excel.
As
we all know, Microsoft Excel is a powerful application
that can be used for simple database reports all the way up to a complicated application
full with windows forms, macros, and add-ons. You can use Excel to calculate anything
from a loan payment, graph data, manage records, keep an address book, etc. Excel
is currently used by most large financial institutions for daily financial data
analysis. It has a huge range of financial functions, formulas, and add-ons
that allows you to use Excel to store and analyze data in a simple, quick way.
In the group project we were
assigned, we are to re-create half of a 100-item examination paper into an
interactive Excel file where student can readily key-in their answers and then automatically
validated by the Excel Application if correct or otherwise.
Basically, if an answer is keyed in with the correct choice, Validation Remarks will appear on the far-right column confirming if the answer is indeed correct or not. See picture below.
The
trick in creating this Excel test is the design of the formulas in generating
the variables that will reflect the process of answering, verifying and
validating.
If the correct answer to a particular question is choice
no. 2, and the student keyed-in the number 2, the formula created to validate
and reflect that the answer is indeed correct is, “IF(B5=1,"Wrong",IF(B5=2,"Correct",IF(B5="","")))”.
Now,
If the correct answer to a particular question is choice no. 1, and the student
keyed-in the number 1, the formula created to validate and reflect that the
answer is indeed correct is, “=IF(B7=2,"Wrong",IF(B7=1,"Correct",IF(B7="","")))”.
In order for the application to take note of the total
number of correct answers, another formula was created to simply tally the
number of correct responses validated in the Remarks Column. The formula used, “=COUNTIF(Z4:Z54,"correct")”.
