Content uploaded by Laura Wetzel

Author content

All content in this area was uploaded by Laura Wetzel on Feb 22, 2017

Content may be subject to copyright.

Available via license: CC BY-NC-ND 4.0

Content may be subject to copyright.

Spreadsheets in Education (eJSiE)

#3091) ?779) 68-'0)

3:)1&)6

Quick Correct: A Method to Automatically

Evaluate Student Work in MS Excel Spreadsheets

Laura R. Wetzel

Eckerd College;)8>)006)'/)6()(9

Peter J. Whicker

Eckerd College;,-'/)4.)'/)6()(9

3003;8,-7%2(%((-8-32%0;36/7%8 ,D4)49&0-'%8-327&32()(9%9).7-)

B-7;36/-70-')27)(92()6% 6)%8-:)311327D6-&98-3232'311)6'-%03)6-:%8-:)$36/7

-')27)

B-728,)0%77633168-'0)-7&639+,883=39&=8,)32(97-2)77 ',330%8)9&0-'%8-327&32(8,%7&))2%'')48)(*36-2'097-32-2 46)%(7,))87

-2(9'%8-32) -&=%2%98,36->)(%(1-2-786%8363*)9&0-'%8-327&32(36136)-2*361%8-3240)%7)'328%'832("2-:)67-8=7)437-836=

336(-2%836

)'311)2()(-8%8-32

$)8>)0%96%%2($,-'/)6)8)69-'/366)'8)8,3(839831%8-'%00=:%09%8) 89()28$36/-2 <')0

46)%(7,))87 Spreadsheets in Education (eJSiE)#307768-'0)

:%-0%&0)%8 ,D4)49&0-'%8-327&32()(9%9).7-):30-77

Quick Correct: A Method to Automatically Evaluate Student Work in MS

Excel Spreadsheets

Abstract

B)59-'/'366)'81)8,3(%003;7-27869'836783)%7-0=%77)77<')0746)%(7,))8%77-+21)287%2(238-C)7

789()287-11)(-%8)0=-*8,)-6%27;)67%6)%'')48%&0)B)-27869'836'6)%8)7%746)%(7,))88)140%8)*36

789()28783'3140)8)!3):%09%8)789()28%27;)67;-8,-28,)8)140%8)8,)-27869'83640%')703+-'*92'8-327

)+-283%'30912%(.%')2883789()286)74327)7B)7)@59-'/'366)'8A*36190%)%6)8,)2

4%77;36(4638)'8)(%2(,-(()2*631:-);*%789()28)28)67%2-2'366)'8%27;)6;,-0)'3140)8-2+8,)

746)%(7,))88)140%8)8,)03+-'*92'8-326)89627%2%446346-%8);%62-2+8,%8)2'396%+)7'366)'8-327

Keywords

746)%(7,))87<')0+6%(-2+03+-'*92'8-327

Distribution License

B-7;36/-70-')27)(92()6% 6)%8-:)311327D6-&98-3232'311)6'-%03)6-:%8-:)$36/7

-')27)

B-7-28,)'0%776331%68-'0)-7%:%-0%&0)-2 46)%(7,))87-2(9'%8-32) - ,D4)49&0-'%8-327&32()(9%9).7-):30-77

©2007SpreadsheetsinEducation,BondUniversity. Allrightsreserved.

http://epublications.bond.edu.au/ejsie/

Quick Correct: A Method to Automatically Evaluate

Student Work in MS Excel Spreadsheets

Laura Reiser Wetzel

Department of Marine Science

Eckerd College

Saint Petersburg, FL

wetzellr@eckerd.edu

Peter J. Whicker

Department of Marine Science

Eckerd College

Saint Petersburg, FL

whickepj@eckerd.edu

Abstract

The quick correct method allows instructors to easily assess Excel spreadsheet assignments

and notifies students immediately if their answers are acceptable. The instructor creates a spread-

sheet template for students to complete. To evaluate student answers within the template, the in-

structor places logic functions (e.g., IF, AND, OR) into a column adjacent to student responses.

These “quick correct” formulae are then password protected and hidden from view. If a student

enters an incorrect answer while completing the spreadsheet template, the logic function returns an

appropriate warning that encourages corrections.

Keywords: spreadsheets, Excel, grading, logic functions.

1. Introduction

One of the most onerous tasks in teaching is correcting student papers. By using

logic functions and password protections within MS Excel, instructors can create

spreadsheet templates that automatically evaluate students’ work. This also allows in-

structors to change spreadsheets slightly from student to student and automatically cre-

ate an answer key.

2. Quickly Correcting Spreadsheets

The basic idea is to automatically check for the correct value when students input a

formula. In this way, students will know immediately if the work is correct or the in-

structor may hide this information and display it later to correct student work with

greater ease.

1

Wetzel and Whicker: Quick Correct

Published by ePublications@bond, 2007

SELF‐EVALUATINGSPREADSHEETSINMSEXCEL

282

2.1. Creating a Template

For any assignment, an instructor creates an Excel template that is electronically dis-

tributed to students. The minimum information in the template is a column specified for

student answers and a corresponding column containing logic functions that automati-

cally assess the student answers. In the example in Fig. 1, students are provided with

numbers in the yellow cells (left), are asked to fill in the appropriate formulae in the or-

ange cells (center), and are provided with “quick correct” information in the green cells

(right).

B C D

2 Magnitude Seismogram Quick Correct

3 MW Amplitude Amplitude

4 mm mm

5 5 0.1

6 6 **Change**

7 7 **Change**

8 8 **Change**

B C D

2 Magnitude Seismogram Quick Correct

3 MW Amplitude Amplitude

4 mm mm

5 5 0.1

6 6 =IF(C6=C5*10,"Correct","**Change**")

7 7 =IF(C7=C5*100,"Correct","**Change**")

8 8 =IF(C8=C5*1000,"Correct","**Change**")

Figure 1: Assignment template as seen by a student (top) and as created by an instructor (bot-

tom). The earthquake magnitude scale is logarithmic, so each step in magnitude (MW) represents

a ten-fold increase in seismogram amplitude.

In this case the quick correct formula consists of the IF function with the correct for-

mula, a “correct” answer message, and a “change” answer message, respectively. The

logic functions AND, OR, TRUE and FALSE may also be used in Excel. The logic func-

tions compare the numerical values of the student’s and instructor’s answers. In the ex-

ample from Fig. 1, Excel determines if the student’s responses in column C produce the

same numerical answer as the formula used in the IF statement (Fig. 2). In this case, cell

C7 might contain any of these correct answers:

• =C6*10

• =10*C6

• =C5*100

• =100*C5

• =10*10*C5

• =100*0.1

• =10*1

2

Spreadsheets in Education (eJSiE), Vol. 2, Iss. 3 [2007], Art. 1

http://epublications.bond.edu.au/ejsie/vol2/iss3/1

L.R.WETZELANDP.J.WHICKER

eJSiE2(3)281‐288InTheClassroom283

• 1.0E+01

• 10

B C D

2 Magnitude Seismogram Quick Correct

3 MW Amplitude Amplitude

4 mm mm

5 5 0.1

6 6 1.0 Correct

7 7 10.0 Correct

8 8 100.0 Correct

B C D

2 Magnitude Seismogram Quick Correct

3 MW Amplitude Amplitude

4 mm mm

5 5 0.1

6 6 =C5*10 =IF(C6=C5*10,"Correct","**Change**")

7 7 =C6*10 =IF(C7=C5*100,"Correct","**Change**")

8 8 =C7*10 =IF(C8=C5*1000,"Correct","**Change**")

Figure 2: Spreadsheet with correct answers as seen by a student (top) and an instructor (bottom).

The strategy discussed thus far checks for an exact answer as calculated by Excel to

15 significant figures. Although this works for many applications, calculated answers

can vary slightly because of computer number formats. For example, in Fig. 3 the seis-

mogram amplitude appears to be correct, but the answer is not always an exact match to

the quick correct formula in column D because the student has input 100^(1/33)^33 in

column C. The answer in cell C8 appears to be exactly 100, but in the course of perform-

ing this calculation Excel has slightly changed the value. To account for this, a second

quick correct formula in column E checks for answers within 1% of the exact value by

using a nested IF statement. To avoid misleading quick correct statements, an instructor

can decide on an acceptable range of correct values and design nested logic statements

accordingly. This is especially valuable for formulae using rational numbers that require

converting numbers written as the quotient of two integers into a decimal value.

B C D E

2 Magnitude Seismogram Quick Correct Quick Correct

3 MW Amplitude Amplitude Amplitude

4 mm Exact Value? Value within 1%?

5 5 0.1

6 6 1 Correct Correct

7 7 10 Correct Correct

8 8 100 **Change** Correct

B C D

3

Wetzel and Whicker: Quick Correct

Published by ePublications@bond, 2007

SELF‐EVALUATINGSPREADSHEETSINMSEXCEL

284

2 Magnitude Seismogram Quick Correct

3 MW Amplitude Amplitude

4 mm Exact Value?

5 5 0.1

6 6 =(C5*10)^(1/33)^33 =IF(C6=C5*10,"Correct","**Change**")

7 7 =(C6*10)^(1/33)^33 =IF(C7=C5*100,"Correct","**Change**")

8 8 =(C7*10)^(1/33)^33 =IF(C8=C5*1000,"Correct","**Change**")

E

Quick Correct

Amplitude

Value within 1%?

=IF(C6>C5*10+(C5*10/100),"**Too High**",IF(C6<C5*10-(C5*10/100),"**Too Low**","Correct"))

=IF(C7>C5*100+(53*100/100),"**Too High**",IF(C7<C5*100-(C5*100/100),"**Too Low**","Correct"))

=IF(C8>C5*1000+(C5*1000/100),"**Too High**",IF(C8<C5*1000-(C5*1000/100),"**Too Low**","Correct"))

Figure 3: Answers as seen by a student (top) and an instructor (middle and bottom). Student an-

swers are compared to a single value in the simple IF statement in column D and a range of an-

swers within 1% of that value in the nested IF statements in column E.

2.2. Conditional Formatting

In addition to logic functions, Excel has a Conditional Formatting tool to compare

cell values and formulae. In this case, instead of providing separate columns with mes-

sages for students, the conditional formatting is applied to the cells where students will

provide their answers (Fig. 4). To apply conditional formatting, follow these steps:

1. Click in a cell where students will type an answer.

2. Within the Format menu, select Conditional Formatting.

3. Within the Conditional Formatting tool (Fig. 5), complete the following tasks:

• In the top central menu, choose the appropriate option from selections such as

equal to, between, and greater than.

• In the top box on the right, specify the appropriate cell value or formula by

typing directly or clicking on cells in your spreadsheet.

• Click on Format to change the cell’s background color, border type, and font

style or color for appropriate student responses. Note that if you do not

change the format, then the cell’s appearance will remain unchanged in the

spreadsheet.

• Click Add on the bottom of the tool to apply up to three conditions to the cell.

• Click OK when done.

4. To copy the conditional formatting to other cells, click on the Format Painter in

the tool bar symbolized by a paintbrush. Then copy and drag in the appropriate

cells as you would when copying formulae.

The explicit instructions provided here are appropriate for Excel 2003 and may re-

quire revision for use with Excel 2007.

B C

4

Spreadsheets in Education (eJSiE), Vol. 2, Iss. 3 [2007], Art. 1

http://epublications.bond.edu.au/ejsie/vol2/iss3/1

L.R.WETZELANDP.J.WHICKER

eJSiE2(3)281‐288InTheClassroom285

2 Magnitude Seismogram

3 MW Amplitude

4 mm

5 5 0.1

6 6 1

7 7 100

8 8 10

Figure 4: Answers as seen by a student using the conditional formatting parameters specified in

Fig. 5. The green cell contains a correct answer, while the pink and blue cells contain incorrect an-

swers that are too large and too small, respectively.

Figure 5: The Conditional Formatting tool in Excel 2003 with parameters for cell C6 in the spread-

sheet shown in Fig. 4.

2.3. Checking for Formulae

If it is important to ensure that students input formulae rather than the numerical

answers to complete an exercise in Excel, then instructors may use one of the following

strategies:

5

Wetzel and Whicker: Quick Correct

Published by ePublications@bond, 2007

SELF‐EVALUATINGSPREADSHEETSINMSEXCEL

286

• To display formulae rather than the resulting values on an entire worksheet, use

CONTROL+` (simultaneously hold down the CONTROL key and the accent

grave located on the key with the tilde). The spreadsheet will show formulae un-

til you press CONTROL+` again to restore the numerical values. This allows you

to quickly switch between seeing the values and the formulae to confirm that

students did not simply input numbers. This feature was used to contrast the

student and instructor versions in Figures 1-3. If students print out their results,

they can print one version showing the values and another showing the formu-

lae. If students complete the spreadsheets in a supervised setting, the instructor

can quickly spot-check spreadsheets while students work.

• Require students to type an example formula on the sheet to confirm they know

the general Excel equation format (Fig. 6).

• Require calculations that produce enough significant figures to dissuade stu-

dents from typing in the values (Fig. 6).

B C D

2 Magnitude Moment Quick Correct

3 MW M

o M

o

4 dyne-cm dyne-cm

5 Student typed value with 3 significant figures

6 5 3.55E+23 **Change**

7 6 1.22E+25 **Change**

8 7 3.33E+26 **Change**

9

10 Magnitude Moment Quick Correct

11 MW M

o M

o

12 dyne-cm dyne-cm

13 Student calculated value with appropriate formula

14 5 3.55E+23 Correct

15 6 1.12E+25 Correct

16 7 3.55E+26 Correct

Example Formula:

C14 = 10^(1.5*(B14+10.7))

Figure 6: To be correct in this case, a student must type in the correct formula or a number with

15 significant digits to match the calculated value produced by Excel for the moment magnitude

(Mo) of an earthquake.

2.4. Hiding and Protecting Answers

The strategy of using quick correct formulae is useless if students can view the an-

swers contained within the logic functions. To use Excel’s password protection feature

to prevent students from seeing or changing formulae, follow these steps:

1. Select the range of cells containing the quick correct formulae you wish to protect.

6

Spreadsheets in Education (eJSiE), Vol. 2, Iss. 3 [2007], Art. 1

http://epublications.bond.edu.au/ejsie/vol2/iss3/1

L.R.WETZELANDP.J.WHICKER

eJSiE2(3)281‐288InTheClassroom287

2. On the Format menu, click Cells, and then click the Protection tab located on the

top right.

3. Select both Locked and Hidden; click OK.

4. Because the default for all cells is “Locked”, select the range of cells where stu-

dents will enter their answers.

5. On the Format menu, click Cells, and then click the Protection tab.

6. Deselect Locked; click OK.

7. On the Tools menu, select Protection, Protect Sheet.

8. Input a password, click OK, and reenter the password to confirm. Note that the

password is case-sensitive and cannot be retrieved if forgotten, so be sure to re-

cord the password for future reference.

9. Click on cells in the spreadsheet to confirm that students can enter data in the ap-

propriate columns.

10. Click on individual cells or use CONTROL+` to confirm that your quick correct

formulae are hidden. (If you have followed steps (1) through (8) correctly, then

the cells hidden with password protection will appear blank in the formula bar

and when using CONTROL+`.)

11. If you have inadvertently locked or hidden some cells and want to make changes,

then on the Tools menu, select Protection, Unprotect Sheet and enter your pass-

word. Retrace steps (1) through (11) as necessary.

Furthermore, instructors may wish to completely conceal the quick correct columns

from students. To hide a column from sight, follow these steps:

1. Click in a cell in the appropriate column.

2. In the Format menu, select Column, Hide before you use the Tools menu to pro-

tect the worksheet (i.e., hide the column before step 7 above).

3. To unhide the column, highlight cells in the columns before and after the hidden

section. (For example, if you hid column D, highlight cells in columns C and E.)

4. In the Format menu, select Column, Unhide.

Note that the same procedure may be used to hide multiple columns or rows. The

explicit instructions provided here are appropriate for Excel 2003 and may require revi-

sion for use with Excel 2007.

3. Conclusions

The quick correct method is flexible and may be adapted to many Excel spreadsheet

assignments, provided the instructor can supply students with an electronic copy of the

template. In addition to checking numerical values, logic functions can also assess mul-

tiple choice or simple text answers. The primary caveat of this technique is that the Excel

logic functions compare values and do not discriminate between calculated answers and

typed numbers (e.g., 10=5*2=40/4). Despite this limitation, in most cases quick correct

makes Excel spreadsheet assignments simpler for students to complete and instructors

to grade.

4. Acknowledgements

We would like to thank three anonymous reviews for helpful comments as well as

Courtney Kniss and Dorien McGee for closely reading the manuscript prior to submis-

7

Wetzel and Whicker: Quick Correct

Published by ePublications@bond, 2007

SELF‐EVALUATINGSPREADSHEETSINMSEXCEL

288

sion. The quick correct technique was developed during the Spreadsheets Across the

Curriculum project supported by the National Science Foundation, Division of Under-

graduate Education, through award DUE 0442629 to H.L. Vacher, University of South

Florida, Tampa.

8

Spreadsheets in Education (eJSiE), Vol. 2, Iss. 3 [2007], Art. 1

http://epublications.bond.edu.au/ejsie/vol2/iss3/1