First, let's examine how a spread sheet program benefits language instructors. The most straight forward use of a spread sheet program is to keep track of various student records such as test scores, homework, and absences. With the help of computer, end-of-semester calculations can be done automatically. An instructor can also arrange students according to different criteria, compare groups of students (among multiple sections or between current groups and past groups). This data manipulation gives the instructor and program coordinator a measure to evaluate curriculum and instruction for improvement. Another example, if data is recorded on how each question was answered in regularly scheduled exams such as placement tests, it is possible to conduct a simple item analysis to improve the quality of the test.

I. Electronic grade sheet

In the following, the basics of a spread sheet program is introduced in the context of an electronic grade sheet. Traditionally, instructors use "green books" to record students' performance throughout a semester, and calculate totals and grades at the end. If we are to computerize this process by making the time investment in learning how to use software, an electronic grade book must be able to do much more than the traditional one. The following list summarizes merits of the electronic worksheet.

• An electronic worksheet can contain formulae for calculations; hence, it eliminates end-of-semester calculations. It also eliminates calculation errors.
• An electronic worksheet can be used repeatedly.
• It is easy to make modifications such as grading criteria.
• It is easy to make corrections when data needs to be changed.
• It is possible to give students progress reports at any time without taking up much of the instructor's time. (It reduces the number of complaints concerning the student's grades at the end.)
• An instructor can look at the student's performance in various ways. (sorting, computing averages)
• It is possible to compare groups of students. (among multiple sections or between current groups and past groups.)

All of these can be achieved with a relatively small time investment. If you do not feel comfortable with not having records in print form, you can do both. When you deal with the electronic data, however, it is extremely important to always back up important data. When you back up data regularly, there is very little chance of serious data loss.

Now look at Figure 2.1. This is a sample blank worksheet. A worksheet is divided by vertical and horizontal lines and contains a lot of cells. Each line is numbered from 1 and each row is marked by a letter of the alphabet. When a single letter runs out, rows are marked as AA, AB, AC, etc. A worksheet is huge extending downward and to its right. Since the computer screen cannot show the entire worksheet, it works as a window to look at a small portion of it. Thus, the word "scroll" is used to move the window to look at different parts of the worksheet. Each cell can contain numbers, text, or formula, and it is identified by the combination of line number and row name. For example, A1 is the left most cell in the first line. The next cell to its right is B1, and the cell below it is A2.

We now need to decide what data to record. The main characteristic of a student's record in foreign language courses is that language courses have many more scores to record than regular lecture-based courses. In addition to major exams, such as a midterm and the final, language courses tend to have numerous small quizzes and homework. Attendance or class participation is also a part of the record. In some cases, participation is rated daily or weekly. In order to record all of these in a worksheet, it is reasonable to enter the student's names in Row A and start entering data to subsequent columns. At the end of the sheet, one might want to create a summary page to see the results of computation and grades. You would also want to reserve Line 1 for labels. (e.g. test 1, Quiz 5, HW3, etc.) Since there is much data to be recorded, the worksheet will be a wide one . Figure 2.2 illustrates this stage.

This configuration, however, has a few problems. Since the summary page is attached at the end, it is off the computer screen; thus, you have to scroll all the way to the end every time you need to look at the summary data. In order to solve this problem, you can create the summary page at the beginning of the worksheet. (see Figure 2.3.)

Another problem is that names go off the screen when you scroll to the right. This would be very inconvenient when one wants to enter data. Fortunately, spread sheet programs provide a feature called "split screen." (See Figure 2.4.) In Figure 2.4, the worksheet is divided into four sections, spliced both vertically and horizontally, and each section scrolls separately. Therefore, Row A stays even though other sections scroll and Line 1 stays on the screen when you have a large number of students. This is a very convenient feature, so make good use of it. (In Excel, you can split the screen by dragging a small black box next to the left-arrow or on top of the up-arrow.)

Next, Let's write some formulas for calculation. The most important thing to remember is that you must know your grading criteria. How many tests will you give? What is the full mark for each test? What is the weight of each assignment? The following sample is an example of simple grading criteria for the sake of illustration.

Look at Figure 2.3 and 2.4 carefully. Row B is for average scores of quizzes, Row C for homework, and Row D for composition. Since Row B is for the average score of quizzes, cell B2 (score for Mr. Kennedy) should contain the average of K2, L2, M2 cells. In this particular software, the formula is written as =(K2+L2+M2)/3 or =AVERAGE(K2:M2). One needs to type this formula into cell B2. The first equal sign (=) indicates that the content of this cell is a formula. = (K2+L2+M2)/3 tells the computer to add the values in K2, L2, and M2 and divide it by three. This formula, however, gets longer as the number of items increases. If you use the second formula with the AVERAGE function, the formula stays compact. It causes the computer to compute the average
value of all numerical values between cells K2 and M2. The formula for other calculations should be quite similar. In Figure 2.4, the attendance record is marked either with 1 (present) or 0 (absent). Thus, the simple addition of all values between the first class (R2) and the last class (AR2, further right of the worksheet not shown in the figure) is possible. The formula for this calculation is =SUM(R2:AR2). The SUM function is used for summation.

Next, The total points (row H) need to be computed based on the average scores of different tests (row B through row G). If the total points are the sum of each category, the formula to be placed in cell H2 would simply be =B2+C2+D2+E2+F2+G2 or =SUM(B2:G2). However, some values need conversion. For example, vocabulary quizzes are designed as 10-point quizzes, but their weight for the entire grade is 15%. Therefore, the average score of the vocabulary quizzes must be adjusted. The formula is B2/10*15 or simply B2*1.5. The asterisk is used for multiplication. The average score for the homework requires the same adjustment. The score of the midterm exam requires a formula F2*0.2 to convert a 100-point test to a 20-point test. Similarly, the score of the final exam needs to be converted to a base of 30-point with the formula G2*0.3. As for attendance, if the total number of classes is 50, the formula E2/50*10 (or E2*0.2) gives a point based on a 10-point scale. The score from the compositions need no adjustment. Thus, the correct formula to compute the total score is

=(B2*1.5)+(C2*1.5)+D2+(E2*0.2)+(F2*0.2)+(G2*0.3).

Lastly, let's create a formula which produces a letter grade based on the total score. In this case, since we would like to have a letter in cell I2 based on the value in cell H2, it is different from regular numerical calculations. You can achieve this by using the "IF" command. For example, a simple formula =IF(H2>=90,"A","B") performs the following action: if the value in cell H2 is equal to or greater than 90, place letter A. Otherwise, place letter B. This, of course, results in B for any score less than 90. The formula can improve if another "IF" command is embedded in the original "IF" command.

=IF(H2>=90,"A",IF(H2>=80,"B","C"))

This formula now gives letter B for scores between 90 and 80 and C for scores less than 80. When a couple of more embedded IF commands are added, the full range of letter grades can be computed automatically. The final formula looks like the following. It looks complicated, but it simply is repetitious. The number of the closing parentheses at the end is important.

=IF(H2>=90,"A",IF(H2>=80,"B",IF(H2>=70,"C",IF(H2>=60,"D","F"))))

This completes all the necessary formulae. They now need to be copied for the rest of the students. The spread sheet program adjusts cell numbers automatically, when a formula is copied so that the user does not have to worry about changing H2 to H3 and E2 to E3, etc.

The simple worksheet for student records is now finished. Although the grading criteria used here is simplified, it contains necessary basics.

< Download Grade1.xls (Microsoft Excel Worksheet) >

II. Item analysis

Lastly, let us introduce a different type of application of the spreadsheet program. Figure 2.6 shows performance data of a test which contains ten questions. Each line represents a student and each row represents a question in the test. The value of 1 indicates the correct answer while the value 0 indicates a wrong answer. The order of the students have been sorted according to their total points.

The worksheet reveals characteristics of each item. For example, questions 6 and 8 are answered correctly by students with higher total scores, and incorrectly by those with lower total scores. This means that these questions are good questions. However, question 6 shows exactly the opposite tendency. Thus, it must be examined for its content. This is the beginning of a technique called "item analysis". Books on language testing contain discussions on item analysis. Refer to specialized books for further information. (Bachman , Oller) As you can see, it is easy to perform simple item analyses, once you understand the basics of a spreadsheet program.

< Download ItemA.xls (Microsoft Excel Worksheet) >

Exercises for Chapter 2

1. Clearly articulate your grading criteria.
   
   
2. Based on your criteria, make your own electronic grade sheet. (Tip: When you make your own grade sheet, create "the perfect student" as the first student before the list of the real students. Fill all cells with the perfect scores. Test your formula using the perfect student.) See Figure 2.7.
   
   Figure 2.7 < Download Grade2.xls (Microsoft Excel Worksheet) >
   
   
   
   
3. Find the sorting feature in the spread sheet program you are using. Experiment with it to see what kind of manipulations you can perform. Make sure you do this experiment with sorting on a copy of a data file. This prevents you from losing important data.
   
   
4. Work with your colleagues to compare student's performance in different sections. Compare students across a timeline as well.
   
   
5. Can you help the instructors who want to include the following conditions? If you are using other conditions, think how you can handle them.
   
   a. I give 12 quizzes in total, but I discard the worst two scores for the final calculation.
   
   b. I allow three absences. After that, one point will be subtracted for each unexcused absence.
   
   
6. If you can get data for placement exams of a sort, conduct an elementary item analysis to examine the test.