Downloading test banks into excel. A Visual How-To Guide (Best viewed in Slide Show Mode). Objective. The primary purpose of this tutorial is to provide a process that allows grade administers to identify problematic examination questions.
A Visual How-To Guide
(Best viewed in Slide Show Mode)
1. Select “Auto Score” Column2. Open “Find & Select”3. Choose “Replace”4. Select the Replace Tab5. Replace the value for possible points (could be 1, 2, 4, 10, etc.) with 1. Find What: Enter the value the question is worth. Replace With: Enter “1”Incorrect Answers will default as 0. 6. Click on “Replace All”.
1. Click on the upper left corner arrow to select the excel sheet2. Switch to the “Insert” tab3. Click/Choose the Pivot Table4. Confirm “OK”
Your Excel sheet should look similar to this.For our purposes, we can arrange the data meaningfully by first selecting the “Question” Category and dragging it into the bottom left Quadrant.
1.After dragging the “Question” Data source into the “Row Label” field, your screen should list all the questions to the left.2. Then drag the “Question” into the bottom right quadrant labeled “∑ Values”.3. Now drag the “Auto Score” down to the “∑ Values” box.4. Repeat Step 3 so that there are two “Count of Auto Scores” in the Values Box
1. Select the first/top “Count of Auto Score” from the “∑ Values” box.
2. Then select from the Drop Down Menu
“Value Field Settings”
3.The pop-up box defaults on “Count”. Instead, select “Sum”.
4. Finally select the bottom “Count of Auto Score” but this time choose “Average” instead of “Count”.
You may find it helpful to decrease the “Question” Column width to about 100. To do so right-click the A-Column, select “column width”, and enter 100.
1. To organize the questions according from most problematic to least, click on any score in the final column (Average of Auto Score) and use the “Sort & Filter” button. 2. To display the averages as a percentage, click on the “D” Column (or whichever column Average Auto Score falls under) and Select the % button in the center of tool bar under the Home Tab
The Second Column should display the total number of times the question occurred.
The Third Column should display the number of times it was answered correctly.
The Final Column should display the average score of it being correctly answered .
0 – All answers incorrect
.5 or 50%- Half answered
1 or 100%– All students who had the question answered correctly.