Skip to content

Some Formulas for Test Results Spreadsheet

May 22, 2016

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

box

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.  

Pcg026

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.

Conclusion

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.

Advertisements

Reorganizing and Learning

May 22, 2016

I have been thinking about the process of learning as I am participating in a professional development course at my college. We are aiming to come up with an action research project. At present, I am considering a project on adding gamification elements into my reading and my writing classes. In considering this, I have been reflecting on learning.

Teaching involves getting students to reorganize their knowledge in some ways. The process involves making students uncomfortable with what they know; i.e., I don’t know enough to do this, or what I know doesn’t match with my textbook/teacher’s lecture/video information. The teacher helps the students deal with the failures that go along with the learning while also enabling the student to go through the successful reorganization.

Setting up a Test Results Spreadsheet

May 16, 2016

For some time I have been working on developing a means to learn more about the tests I give and give feedback to the students about their tests.  I find the most effective way is through using Google Sheets or Excel.  I tend to use Google Sheets because my work machine is a PC with the Office package (Word, Excel, PowerPoint, and Outlook) and my home computer is a Mac without the Office package.

This post will show how to begin setting up the spreadsheet file for one type of test.  Subsequent posts will show some formulas I use and how to set up the reports page.

For a reading comprehension test that tests skills like words in context, main idea, and supporting information, I set up the main page with the test item number and the key under it like this.

Screen Shot 2016-05-15 at 9.32.09 PM

I then add additional pages for each skill with the items from the test that criteria. To add additional sheets or pages, go to the bottom of the spreadsheet and click on the plus sign.

Make_a_new_sheet

For example if question 1 and 3 are main idea questions, then that page will have links to question 1 and 3 on the new sheet.  First, it is a good idea to rename the sheets.  I renamed mine as student results and main idea.

rename

Now, we want to link the main idea questions to the answer sheet.  We can do this quickly using the formula bar.  First, we find the cell we want to use as our landing space.  From this space, we enter the = sign in the formula bar and switch sheets to the Student Results sheet and click on the desired space.  In the formula bar, a formula should appear that looks something like this =‘Student results’!B2.  The information between the apostrophes is the sheet title.  The exclamation point tells us that we are linking to a different page.  This is followed by the cell reference where the information will be copied from.  I have experienced difficulties with this in Google Sheets and sometimes have to manually adjust the cell number to get the proper reference cell.  

Next we fill the spaces with the reference cells to be able to copy the selected information when we record the scores as is shown in this video.

Make_a_fill

We now have the basic elements for setting up the spreadsheet.  To complete it, we create new sheets for the the different skills and link them to the student results page.  We complete the fills.  We are now ready to record the student answers.

 

Google Drawing for Timelines

May 15, 2016

With reading classes, I try to design activities that require the students to read and reread the text.  One way to encourage this type of interaction with the text is through having students do different kinds of illustration or diagramming activities.  One of the first activities that I did with my students this last semester was to make a timeline for a reading.  I have done this in the past using a spreadsheet, but I was looking at videos on YouTube for ideas and found one that showed how to put a timeline on top of an image.  I decided to do this with my students.

I made a video explaining how to make the timeline.

I took the students to the lab and worked with them on making the timeline.  The lesson went well in that the students could complete the timeline in the time we had for the class and most timelines were accurate.  If they were not, I gave students feedback and encouraged them to revise their timelines until they were correct.

It is important with Google Drawing that all of the students have a Google account preferably setup before class.

This activity can help prepare for later illustration and diagramming of reading texts for showing the relationships between main ideas and supports, for showing organizational patterns, and showing statements and evidence.  The advantage of using Google Drawing early with a lesson like this is we don’t have to provide as much guidance on how to use Google Drawing after the first lesson.  Thus, the students can spend more time with the reading.

Thoughts on Zipgrade

May 14, 2016

During the last semester, I tried out Zipgrade, an application for Android and Apple systems.  I put in on my iPad and iPhone to use in my reading class and ran a few trials with it.

Zipgrade as the name suggests is a tool for quickly grading students’ work.  It grades multiple choice activities quickly giving an almost immediate score and can show the correct and incorrect items.  Zipgrade functions using the device’s camera whether tablet or phone to take a picture and process the information.  In addition, the website where the information goes puts the information into analysis so the teacher can see quickly how well the students perform on each item as well as how well they perform on the test or quiz as a whole.

Let’s walk through the procedure for using the application.  The teacher creates a key for a quiz or an exam on his or her device.  If the students’ information has been uploaded to the teacher’s account on the website, the teacher can assign the quiz or test to a class.  The teacher prints out the proper answer sheet (there are three options: 1o items, 20 items, and 50 items) for the quiz.  The answer sheet is a scantron type form with multiple choice options for five answers.  The quiz is administered with the students blackening their choices for the answers.  When they finish, the teacher lets the app take a picture of the answer sheet and the score is processed.  The teacher can share the results immediately with the students.

I found that setting up a class made it very easy for me to get on with the quizzes, grading, and analysis.  I imported my class from a .csv file.  Students were assigned id numbers which makes it possible to use different types of answer sheets.  Since I had set up the class, I also could print out individualized answer sheets with the students’ names and id number on the print out.  This made grading with the application quick and easy for the most part.  I also had data available after the test for analysis which not only told me how my students did, but also the data helped me see item by item the troublesome items and the items the students had mastered.

It was a quick process when it worked.  However, I experienced some difficulties.  The application is finicky about reading the answer sheets because they have to be perfectly level.  Also, it took me a while at first to get my phone and tablet aligned properly to read the answer sheets.  The bothersome part was that after a few answer sheets were read, the application quit processing the answer sheets on my iPad.  I switched to my iPhone and finished.  I don’t know what would have happened if I had not had my phone with me.  I don’t know if the same problem would have arisen if I had begun with my phone.

Pricing is reasonable.  The first one hundred answer sheet readings are free.  They charge 1.99 USD for two months and 6.99 USD for one year.  There is also a Value Purchase Program for Education through Apple that costs 12.99 USD that appears to have no time limit.

I plan to use this application in my next semester and will prepare for it.  I found Zipgrade in the middle of the semester and just did some trial runs.  It is useful and makes grading much easier and quicker so I can concentrate on teaching and analysis instead of making checks on papers.  I like having the data available though the report includes a lot of information which I found superfluous, but I think there are ways in Google Sheets for me to manage it better in the future.

In summary, a quick grading tool that removes the drudgery and provides some very useful analysis to help improve my tests and my teaching.

 

cheating to collaboration

October 3, 2014

A few students in my communication class made the exact same errors on a listening test while others got items correct but made similar spelling errors.  My first reaction was to be upset that they were cheating and upset with myself being unable to catch them.  However, after thinking about it for a time, I decided to go along with what they were doing by allowing them to collaborate on the test today.

I told the classes (a morning and an afternoon class) about the amazing similarities in errors.  Heads went down, and there were a few embarrassed smiles.  Then I told them that I would like them to write on their papers whose help they got by looking at the paper or asking (unfortunately in the afternoon class, I wasn’t so specific about the looking on).  I also told the students I would give a student a 0 on the test if I thought they got help but did not write down the person’s name they got help from.

The results were that the morning class not only wrote their names down, but some wrote their names beside the items where they got help.  The afternoon class was less forthcoming about getting help and seemed to think I was trying to trick them despite my assurances that I would consider this collaboration.

Am I caving into cheating?  I suppose I am.  Will it matter in the long run? I don’t know.  I am not getting as much information about students with problems, but in another way I am since those with problems are relying on help.  Also, they don’t seem to collaborate on many items of the test.  I will continue to experiment with this.

Since I don’t want them cheating/collaborating on the final exam, I have decided the final will be given in shifts so that cheating will be more difficult.  I will split the class and give the test to one half then repeat the test for the second half.

Show Me Assessment

October 2, 2014

I have been trying out some ways to gain more information about how my reading students approach some of the skills we work on.  I began last spring with what I call a show me.  I was not trying to build off of the ShowMe app.  In fact, I doubt if any of my students were aware of the app.  The idea came to me from writing class where I keep telling my students that they need to show me not tell me.

My approach to the show me has been to have students make a PowerPoint, video, or other illustration showing me how they found, for example, the main idea or supporting details.  The challenge is not just to get the correct answer, but to show how they got their answers.  In return, I can understand better what they are doing.  One pair of students provided me with a PowerPoint deck that showed me a distorted sense of supporting information.  They only highlighted the details but left out the explanation part of the support.

They tend to favor PowerPoint, but I am going to work on finding other applications that will illustrate better.  I tried with educreations this time, but the interface confused the pair that tried it.  Next time, I will put together or find a tutorial for the suggested sites or apps.

Today, I gave the students an extra credit opportunity to explain the differences between 5 word pairs that they had difficulties with on the last vocabulary quiz.  Again, the goal is that they show me how they understand the problem.  The attempt is tentative, but I am looking for ways to follow through on this.

The main goal of a show me is somewhat like the math show me your work approach.  I want to find out how they understand what they are doing.