Some Formulas for Test Results Spreadsheet
In setting up the test results spreadsheet, there are some formulas that are needed to help report and analyze the results. I am not a spreadsheet maven, so most of my formulas lack sophistication and do not use such powerful one as VLookUp which I often see referenced on YouTube or in some posts about setting up reports.
Number of Students
After entering the data for all of the students, I want to know how many students took the test. This is important for some later calculations. Now I could just count them, but I like to use one of two formulas either =COUNTA(beginning cell, ending cell) as in =COUNTA(A3:A28). I can also use the formula =COUNTIF(A3:A28,“*”). I prefer the first formula. Now, I have the number of students who have taken the test.
Students with Correct Answers
Next, I want to know how many students made the correct choice for each item. My test was a 15 item test. However, I only need to write the formula one time. I use the COUNTIF formula for this. First, I choose the column of the item from the first to the last response, then after a comma, I enter or choose the correct answer from the key.
The formula used in this spreadsheet looks like this =COUNTIF(B3:B28,B2). I then grab the box in the lower right-hand corner of the cell
and drag it over the other columns to quickly calculate the number of correct answers for the other items on the test.
To calculate the percent correct, we can divide the number correct by the number of students who took the test. We can do this by manually entering the number of students who took the test or by entering the cell number of formula for the number of students and using $ to make it an absolute. Our formula would look something like this =B30/$A$35.
Analyzing the Responses
To further analyze the responses, we can see a breakdown of choices by using the COUNTIF formula some more. The format changes somewhat. First, I go down the first column and enter a, b, c, and d for the possible choices. Then in the column under the first item, I enter =COUNTIF(B3:B28,“a”). We cannot simply grab the corner and pull down without going back and correcting the cell numbers because they change with the pull-down. We also cannot use the absolutes ($) because we want to pull sideways to copy the formula with the adjustments made by the spreadsheet. So, the next one down looks like =countif(B3:B28,“b”), =countif(B3:B28,“c”). Note that it doesn’t matter if the formula is in upper case or lower case. Once we have all the items for one column, we can copy sideways.
Getting the students’ scores
The next formula we need will give us the correct numbers for the choices the students made. In other words, we can find out how many correct answers each student has. The formula is =SUMPRODUCT(––($B$2:$P$2=B3:P3)). The formula allows the comparison of the students choices in the row B3:P3 with the key row, $B$2:$P$2 using the absolute $ in front of the cell numbers. The results from this formula will provide the number of correct choices. The formula can be copied down by grabbing the square and pulling it down. The key will not change, but the row for each student will change to match the student.
These formulas provide the basis for helping report and analyze student results and the test items. In a subsequent post, I will show how to use these formulas to develop individualized reports for students and how to analyze student results and test items.