How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

Document Sample
How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet Powered By Docstoc
					                                                                                                                        16 months ago
                                                                                                                            by Richieb799

                How Teachers Can Automate their Grade
                Book Using a Google Docs Spreadsheet

                rate or flag this pageTweet                                  sible 24/7 from any computer with Internet access.
                                                                             As a part-time instructor I don’t have an office at
                   An Automation Tool for Teachers                           school and this having the grade book available in
                                                                             my Google Account on the Internet allows me to

                      ather than tackling a big issue in education, I        access it from home, school or work at my day job.
                      decided use this Hub to share a tool that I have
                      used for years in my capacity as an adjunct                Begin With a Google Account and
                or part-time economics instructor at a local Com-                      Add Google Docs to It
                munity College. Holding down two jobs requires
                a large time commitment and if I am going to do a            To get started you first need a Google account with
                good job teaching and still have time to relax with          the Google Docs feature.
                my family it is important that I be efficient. The area
                which provides the greatest opportunities for fin-           This is free and can be obtained by going to Google.
                ding efficiencies is the out of classroom paperwork          com, clicking on Sign In in the upper right and then
                of which grading is one of them.                             clicking on Create and Account Now link. You do
                                                                             not have to use a Gmail address for your sign in as
                As a teaching assistant while in graduate school             you can use any email address as your user sign-in.
                in the days before personal computers, I had the
                experience of recording grades in a paper grade              Once you have created your account, simply click
                book with a pen and then using a calculator to total         on More which appears in the upper left next to
                each student’s grades convert them to a percent and          various Google services such as Gmail, Shopping,
                record the corresponding letter grade.                       etc. and then click on Documents in the drop down
                                                                             box. This will add Documents to the Google services
                  Google Docs are Free and Portable                          available on your account and from this point on
                                                                             simply click on My Account and select Docs to go into
                However, the advent of the electronic spreadsheet            your documents and spreadsheets (spreadsheets
                changed all that and when I began teaching part-             are a part of documents).
                time I decided that the job of tracking and calculating
                grades could be done faster and more accurately on           To create a new document or spreadsheet, simply
                a spreadsheet. I have used a variety of spreadsheet          click on the New button at the top and then click
                programs over the years, beginning with the old              on Spreadsheet in the drop down box and a blank
                Lotus 1-2-3 for DOS and all of these work the same           spreadsheet will appear. It is a good idea to click the
                with only a slight variation in the way the calcula-         Save button at the top right and give the spreads-

                tions are written.                                           heet a name - once named, the spreadsheet will
                                                                             automatically save as you work in it.
                In recent years I have found the Spreadsheet in
                Google Docs to be the best in terms of cost (it is free)
 Printed with

                and versatility - it is on the Internet and thus acces-


                                                                                                                                   Page 1
                                                                                                                        16 months ago

                How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

                       Step by Step Instructions for                                    INSTRUCTIONS CONTINUE AFTER
                           Creating Grade Book                                             THE PHOTO BELOW

                For this example I created a simple spreadsheet
                with columns for five assignments and tests, one
                column for a student count, one for student names
                and three for grade calculation. Refer to the photo
                below for illustration. The instructions will continue
                below the photo.

                - STEP 1 - CREATE A TITLE LINE AT THE TOP: Place
                the mouse pointer in cell A1, click and hold the left
                mouse button and drag the pointer across the first
                row of cells to cell J1. Release the mouse button and
                the first ten cells at the top should be highlighted.
                Next, point to the merge button (see Example 1 be-
                low) and left click once. This will cause the ten cells
                to merge into a single large cell.
                                                                                    Sum Scores for Each Student
                Repeat the Process in row 3. You now have two lines
                for a title and subtitle. You can center your titles by      - STEP 3 - CREATE CALCULATIONS TO SUM STU-
                clicking on the cell in which you want to center the         DENT SCORES: In Row 8 enter the maximum point
                text and then click on the down arrow next to the            score beneith each test and assignment.
                Alignment button which is immediately to the left
                of the Merge button on the Formatting Bar and then           Click on cell C8, click on the Sum Button (last button
                click on the Centering Button.                               on right on Button Bar in photo below), select Sum,
                                                                             highlight cells F8 through J8, place a right parenthe-
                To bold the title, move further left on the bar and          sis «)» after the J8 in cell C8 and hit ENTER. This
                click on the black B button, and to change the font          gives the total points for the course.
                size go one more button to the left and click on the
                down arrow in the Font Size button (which reads 10           Leaving the cursor in cell C8, click Ctrl C to copy
                pt in the example) and click on the desired font size.       the contents of cell C8 (you can also right click your
                                                                             mouse and select copy).
                - STEP 2 - ADD COLUMN HEADINGS IN ROW 7:
                In Row 7 I entered my column headings in the ten             Highlight cells C10 to C19 and click Ctrl V (you can
                cells. You can bold and center these within the cells        also right click your mouse and select paste) to paste
                using the same process.                                      the formula in cell C8 into these cells. Of course,
                                                                             instead of summing cells F8 through J8, the formula

                                                                             in each cell will adjust to sum the cells in columns
                                                                             F through J in its own row.
 Printed with


                                                                                                                                   Page 2
                                                                                                                        16 months ago

                How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

                   INSTRUCTIONS CONTINUE AFTER THE                               INSTRUCTIONS CONTINUE AFTER THE
                            PHOTO BELOW                                                   PHOTO BELOW

                       Calculating and Displaying
                        Student;’s Percent Score
                                                                                 Change Decimals to Percentages
                STUDENT - In Cell D10 type the formula: =C10/$C$8            - STEP 5 - DISPLAY DECIMALS IN COLUMN D AS
                (see photo below) which tells the computer to divide         PERCENT - to change the display of the numbers in
                the number in Cell C to its left by the number in cell       Column D from decimal to percent place the mouse
                C8. Using the dollar signs ($) in front of the C and         pointer in Cell D10, click and hold the left mouse
                the 8 causes the computer to keep this cell constant         button and drag the pointer to Cell D19. This will
                in the formula and not adjust for each new row, as           highlight the column.
                we want to divide a student’s total points which are
                in the cell in Column C immediately to the left of the       Next, place the mouse pointer on the down arrow to
                calculation by the constant number that is in cell C8.       the left of the button labeled 123 on the Button Bar
                                                                             above (this button is located just below the word
                                                                             Format in the Title Bar).and click once to get the
                                                                             drop down box (see photo 2 below). Click on the
                                                                             10.12% to select the un-rounded percent option.

                                                                             By choosing the un-rounded percent display rather
                                                                             than the rounded display option above it you will
                                                                             cause the computer to display the student’s exact
                                                                             percent score which is what the computer will use in

                                                                             calculating the letter grade in the next column. This
                                                                             becomes critical when the difference in a student’s
                                                                             score is one half of one percent or less than the per-
                                                                             cent grade needed for the next higher letter grade.
 Printed with


                                                                                                                                   Page 3
                                                                                                                        16 months ago

                How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

                For instance, if you choose the rounded percent              2 Type the formula below into Cell E10 (NOTE: for
                display a student with a percent grade of 79.6%              the formula as it exists below to work properly
                will display as 80% in Column D but Column E will            your spreadsheet MUST be set up EXACTLY like
                display a letter grade of C because the computer will        my example cell for cell since the formula below
                base the letter grade on the exact percent score and         references specific cells in the spreadsheet).
                not the rounded score.
                                                                             3 Highlight the formula below on your screen, key
                (NOTE: if you want to use the rounded score and have         Ctrl C (or right click your mouse and select Copy
                the letter grade match that score you can accomplish         from the drop down menu), go to your spreadsheet,
                this by lowering the threshold for each letter grade by      point to Cell E10 and key Ctrl V (or right click and
                a half a point making an A equal to 89.5% or greater         select Paste from the drop down menu) to paste it
                rather than 90% or greater, a B equal to 79.5% or            into Cell E10 on your spreadsheet (this worked on
                greater rather than 80% or greater, etc.)                    a Google spreadsheet when I tested it - I had pre-
                                                                             viously set up the spreadsheet exactly like the one
                   INSTRUCTIONS CONTINUE AFTER THE                           I have displayed in the example lacking only the
                            PHOTO BELOW                                      formula in cell E10).

                                                                                     =if(D10>=.90, « A»,if(D10>=.82,»
                                                                                  B»,if(D10>=.74, « C»,if(D10>=.65,» D»,»
                                                                             Let me explain how the formula above has been
                                                                             constructed. The IF statement in all spreadsheets is
                                                                             basically logical TRUE/FALSE test in which we have
                                                                             the computer evaluate a statement and provide one
                                                                             answer if the statement matches our definition of
                                                                             True and a different answer if the statement is not
                                                                             True. In the grading case we want the computer to
                                                                             evaluate and select one of five rather than one of
                                                                             two options. We do this by having it first test for an
                                                                             A and return an A if the percent score is equal to
                                                                             or greater than the lowest numeric score for an A
                       Calculating the Letter Grade                          and, if the statement turns out not to be true (i.e.,
                                                                             the numeric score is less than the threshold amount
                - STEP 6 - CALCULATE STUDENT’S LETTER GRADE                  for an A) to come back and reevaluate the number
                IN COLUMN E - (see photo below) Place the mouse              to see if it meets the test for a B and so on until we
                pointer in Cell E10 and click and enter the following        reach the D score where it comes back with a D if
                formula in Cell E10. You have three options for doing        true and an F if not true (i.e., the threshold score is

                this:                                                        less than that needed for a D).

                1 Click on the down arrow on the Sum button on               In doing this it is imperative that we not only have
                the Button Bar above and select the IF statement             the same number of right parenthesis «)» as left
 Printed with

                and build the formula using the layout that comes            parenthesis «(« but also that each one be in the cor-
                with the function.                                           rect position.


                                                                                                                                   Page 4
                                                                                                                         16 months ago

                How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

                Second it is imperative that we separate the end of           Then place the mouse pointer in Cell E10, key Ctrl
                the test, the true statement and the false statement          C to copy the cell, move the pointer to cell E11, click
                each with a coma.                                             and hold the left mouse button and drag the pointer
                                                                              to Cell E19, release the left mouse button and key
                Third if we are using letters or alpha characters in          Ctrl V to paste the formula in each of the cells from
                out test or our answers that we identify them by              E11 to E19. The computer will automatically change
                putting double quotation marks on either side of              the cell reference number where it looks for the
                them. Further, there can be no blank spaces in the            numeric score from D10 in the original formula in
                formula and if we want a blank space (as I have done          Cell E10 to the number of the cell immediately to
                by placing one of them before each letter grade)              the left of each of the cells in column E in which it
                that it be treated as an alpha character and set off          was pasted (thus the formula in Cell E15 will refe-
                with double quotation marks.                                  rence cell D15).

                Finally, we have to use one of the following sign
                combinations for our evaluations:

                = equal to

                > greater than

                < less than

                <> not equal to

                >= greater than or equal to

                <= less than or equal to                                              Finished and Ready for Work
                When using these characters we must be very speci-            The grade book is now complete and should look
                fic. Thus if we write that an A is =90 then only scores       like the sample below except that yours will have
                of 90 will receive an A, any other score, including           your students’ names and scores.
                100 will NOT be an A. If we write that A is >90 then
                every number greater than, but NOT INCLUDING                  You can add scores for additional tests and assign-
                90 is an A (90.1 is an A while 90 is not)                     ments in the columns to the right - just be sure to
                                                                              adjust your formulas in Column C to include cell
                This formula is placed in Cell E10 and looks to the           number of the last column you use (also don’t forget
                number found in the cell immediately to its left. Even        to do the same for the total possible points in Cell C8
                though we have instructed to display the student’s            as well as to add the maximum score for each new

                score in Cell D10 as a percent, the computer will still       test or assignment in row 8 of the column beneath
                read it as a decimal so all of the numbers we write           the test or assignment name).
                in the formula must be written as a decimal - thus
                an A is >=.90, NOT >=90% and NOT >=90.                        Since I teach college courses, I set up my spreadsheet
 Printed with

                                                                              with just one final score for each student in each
                Once you have completed writing and checking the              course I teach. If you teach K-12 you may want to
                formula in Cell E10 hit ENTER to save it in the cell.


                                                                                                                                    Page 5
                                                                                                                        16 months ago

                How Teachers Can Automate their Grade Book Using a Google Docs Spreadsheet

                create a spreadsheet for each subject or class for
                each grading period.

                Because it can take some time to create this spreads-
                heet, I have made a practice of re-using the same
                sheet by copying it. This can be done in one of two
                ways, either take the spreadsheet from the class
                just completed and save it under a new name and
                then deleting the students’ names and scores (but
                leaving the headings and calculations in place) or
                create the sheet and rather than using it save it un-
                der a new name using the new sheet for the current
                class and saving the original as a template to make
                copies from in the future.

                Finally, for back up you can export or download the
                spreadsheet from Google to your home and/or office
                computer. The downloaded copy can be in the form
                of a MS Excel file, an OpenOffice Spreadsheet file,
                a CSV file format or PDF format. When exported in
                an Excel or OpenOffice format you can continue to
                use it off line as a spreadsheet (however, it will not
                automatically update the online copy - however,
                you can always delete the online copy that hasn’t
                been updated and then upload the current file from
                your desktop or laptop to Google Docs and continue
                working online).
 Printed with


                                                                                                                                   Page 6

Description: Rather than tackling a big issue in education, I decided to share a tool that I have used for years in my capacity as an adjunct or part-time economics instructor at a local Community College.