Excel on a PC for grades

UNIX lovers compute grades using awk, sed, and perl, but others prefer to use a spreadsheet like Excel. I explain here how to use Excel on an ItaP PC to crunch grades. (The free OpenOffice also has an excel-like that works very similarly.)

First, logon to a ItAP PC.

Click on the word START with the left mouse button (in the upper left corner of the Win95 screen) to expose a menu. Choose

/Standard Software/Spreadsheet packages/Microsoft Excel

An Excel window will pop up in a few seconds and will cover your whole screen. (To bring up other windows that are now behind the Excel window, just click on their icons on the bar at the bottom of the screen.)

The online help that comes with Excel is rather easy to use. Just click on HELP at the upper right corner of the window and select INDEX.

I will now give a step by step description of how I set up a grade sheet for my MATH 262 class. First, I entered the students' names in Column A by clicking on cell A1 and then typing a name and then pushing RETURN to automatically enter the name and move down to the next cell. Don't worry about squeezing the names into the cells because after all the names are entered, you can click on a box in the column of names and then click on the FORMAT menu and choose COLUMNS/Width and type in a bigger number to increase the column width.

Next, I entered the scores into the proper cells exactly as I did the names above.

Next, I wanted the sum of the numbers in Columns B and C plus the number in Column D divided by 50 plus the number in Column E divided by 50 to go into Column F, rounded to an integer. I left mouse clicked in cell F1 and then clicked in the input box at the top (in the toolbar) and typed in

int(.5+B1+C1+D1/50+E1/50)

and pushed RETURN. The number I wanted, rounded to the nearest integer appeared in cell F1. To make similar numbers appear in all the rest of the cells in Column F, I simply clicked in cell F1 and selected COPY from the EDIT menu. The I left mouse clicked in cell F2 and dragged all the way down to the bottom row of Column F to highlight the empty cells below F1. Then I selected PASTE from the EDIT menu and voila! the numbers I wanted appeared.

This might be a good moment to PRINT your worksheet.

To sort the rows in the sheet into the order determined by the grand total in Column F, first left mouse click in cell A1 and drag down to the lower right corner of the area where you have entered scores to highlight your entire work area. Next, select SORT from the DATA menu and select Column F from the first RANGE box so that the order of the whole rows in the highlighted area get changed with respect to the numbers in Column F. Next, click on the DESCENDING radio button next to the dialogue box so that the sort will be done so that the highest score moves to row one and descends down from there. Finally, click on OK at the bottom of the dialogue box. You should get a sorted list based on the numbers in Column F and it should be easy to set cut offs for grades. (To put the rows back in alphabetical order, just select UNDO SORT from the EDIT menu. Or, if you change somehting else and it is too late to undo the sort, you can select SORT from the DATA menu and change Column F to Column A and then check the ASCENDING radio button and select OK.)

I also like to put the average scores at the bottom of each column. To do this, I enter AVERAGES in the last cell in Column A and then I click on the cell in Column B to the right of it. Then I left mouse click on the Sigma (summation) symbol on the toolbar. This makes the expression

=SUM(B1:B32)

appear in the input box in the toolbar. This is close to what I want. I click to the right of this formula in the input box and add /32 so that it looks like

=SUM(B1:B32)/32

When I push RETURN, I get the average score for my 32 students in Column B. To make similar numbers appear in the bottom row for all the columns, I mouse click on cell B33 and select COPY from the edit menu (or push CONTROL-C) and then mouse click and drag over all the cells from Column C to Column F to highlight them and select PASTE from the EDIT menu (or push CONTROL-V).

To save your spreadsheet for future use, select SAVE AS from the FILE menu and type a file name like math161.xls in the dialogue box. If you have logged on using your Purdue Career Account, the file should be waiting for you the next time you logon and start Excel and click on OPEN.

It is smart practice to SAVE and PRINT your gradesheet every time you enter new scores. Just select SAVE from the FILE menu to save the sheet to a file and select PRINT from the FILE menu to print to the printer in the lab.

Dropping the one lowest score is rather easy; just use a formula like

=SUM(B1:J1)-MIN(B1:J1)

in the input box for cell K1 to compute the total score in cells B1 to J1 minus the lowest score. To drop the TWO lowest scores in a row before computing a total, use

=sum(B1:J1) - small(B1:J1,1) - small(B1:J1,2)

In general, for example, the function small(B1:J1,5) returns the fifth smallest number in the cells from B1 to J1.

Back to the Network News Index Page

Back to the MATH 2000 HOME PAGE