ajayshahane
New Member
- Joined
- Jul 11, 2010
- Messages
- 12
Hi
Greetings to all.
I am a teacher working in a small educational institute. My institute tests a large number of students – about 100 – every year. Each student gets to write upto 120 tests of about 20 questions each. The tests carry negative marking, as in the student is penalized for incorrect answers.
A typical scoring process is like this:
Marks for each correct answer: +1
Marks for each incorrect answer: –0.25
Marks for questions not attempted: Zero.
E.g. If a student attempted 16 questions, out of which 11 are correct (which means 5 are incorrect), he should be scored as follows:
Correct: 11 * 1 = 11 marks
Incorrect: 5 * (–0.25) = –1.25
Net Score: 11 – 1.25 = 9.75
We enter this into an excel sheet the number of questions attempted and the number of correct questions correctly attempted. When we started out, we used to enter correct answers and total answers in separate cells in Excel sheets. (So the above example would mean cell A1 would contain 11 and A2 would contain 16). Then we used a simple formula to get the net score, displayed in A3.
However this method used too many cells, so we started using a different format:
Correct answers POINT Total Answers. Hence now we use 11.16, which will use only cell A1, (instead of two cells as shown above). Then we extract 16 and 11 separately and display the net score.
We face two kinds of issues
1. Error while entering the data.
a. the data entered in a cell must be of the format CORRECT POINT ATTEMPT i.e. 11.16 as above, but manual errors may lead to entering a data like 16.11, i.e. swapping the two values.
b. the data entered may, by manual error, show more questions attempted than are in the actual test. Each test has exactly 20 questions, but someone entering the data of 11 correct out of 13 attempted may enter 11.31 instead of 11.13
2. Errors while calculating the scores
This is an inherent problem of Excel, I guess. A score of 1 correct out of 5 attempts is entered as 1.05, and should give a net score of ZERO (1*1 – 0.25*4 = 0), but displays a silly message like “-1.11022E-15”. This is true for all scores that should have been ideally reported as ZERO (viz. 2.10, 3.15 and 4.20). Besides an entry with all correct answers, say 7 out of 7, too gets an error message. This too, I suppose is a problem with Excel.
I have pasted the formula hereunder. I’m embarrassed that the formula is not very graceful
but this is what we could manage.
Cell A1: (entered scores) 12.13 (meaning 12 correct out of total 13 attempts).
Cell A2: = IF(A1*100>2020,"ERROR",IF(((A1-INT(A1))*100)<INT(A1),"ERROR",INT(A1)-((A1-INT(A1))*100-INT(A1))*0.25))
If you could kindly paste the above two in your excel sheet and then enter 12.12 or 2.10
in cell A1, you will see the problems we face.
We tried =IF(A1*100>2020,"ERROR",IF(INT(A1)>((A1-INT(A1))*100),"ERROR",INT(A1)-((A1-INT(A1))*100-INT(A1))*0.25))
but that gives unreliable scores.
Thank you for your time and patience for reading this elaborate post. Many thanks in advance too, for any help or suggestions. (I understand this is lengthy, hence my apologies too!)
A Shahane
Greetings to all.
I am a teacher working in a small educational institute. My institute tests a large number of students – about 100 – every year. Each student gets to write upto 120 tests of about 20 questions each. The tests carry negative marking, as in the student is penalized for incorrect answers.
A typical scoring process is like this:
Marks for each correct answer: +1
Marks for each incorrect answer: –0.25
Marks for questions not attempted: Zero.
E.g. If a student attempted 16 questions, out of which 11 are correct (which means 5 are incorrect), he should be scored as follows:
Correct: 11 * 1 = 11 marks
Incorrect: 5 * (–0.25) = –1.25
Net Score: 11 – 1.25 = 9.75
We enter this into an excel sheet the number of questions attempted and the number of correct questions correctly attempted. When we started out, we used to enter correct answers and total answers in separate cells in Excel sheets. (So the above example would mean cell A1 would contain 11 and A2 would contain 16). Then we used a simple formula to get the net score, displayed in A3.
However this method used too many cells, so we started using a different format:
Correct answers POINT Total Answers. Hence now we use 11.16, which will use only cell A1, (instead of two cells as shown above). Then we extract 16 and 11 separately and display the net score.
We face two kinds of issues
1. Error while entering the data.
a. the data entered in a cell must be of the format CORRECT POINT ATTEMPT i.e. 11.16 as above, but manual errors may lead to entering a data like 16.11, i.e. swapping the two values.
b. the data entered may, by manual error, show more questions attempted than are in the actual test. Each test has exactly 20 questions, but someone entering the data of 11 correct out of 13 attempted may enter 11.31 instead of 11.13
2. Errors while calculating the scores
This is an inherent problem of Excel, I guess. A score of 1 correct out of 5 attempts is entered as 1.05, and should give a net score of ZERO (1*1 – 0.25*4 = 0), but displays a silly message like “-1.11022E-15”. This is true for all scores that should have been ideally reported as ZERO (viz. 2.10, 3.15 and 4.20). Besides an entry with all correct answers, say 7 out of 7, too gets an error message. This too, I suppose is a problem with Excel.
I have pasted the formula hereunder. I’m embarrassed that the formula is not very graceful
but this is what we could manage.Cell A1: (entered scores) 12.13 (meaning 12 correct out of total 13 attempts).
Cell A2: = IF(A1*100>2020,"ERROR",IF(((A1-INT(A1))*100)<INT(A1),"ERROR",INT(A1)-((A1-INT(A1))*100-INT(A1))*0.25))
If you could kindly paste the above two in your excel sheet and then enter 12.12 or 2.10
in cell A1, you will see the problems we face.
We tried =IF(A1*100>2020,"ERROR",IF(INT(A1)>((A1-INT(A1))*100),"ERROR",INT(A1)-((A1-INT(A1))*100-INT(A1))*0.25))
but that gives unreliable scores.
Thank you for your time and patience for reading this elaborate post. Many thanks in advance too, for any help or suggestions. (I understand this is lengthy, hence my apologies too!)
A Shahane
