Why Use Basic Formulas Although Excel has the ability to carry out numerous complex mathematical actions, this section will only deal with some basic features of Excel that a teacher can easily use to develop specific and accurate systems of record keeping to meet their needs. A teacher can develop specific Excel worksheets that can save them much time in keeping student/class records. A teacher can maintain an up-to-date, accurate, and 'running record system' of information specific to their class needs. Teachers can use Excel to automatically calculate sums and/or averages for student marks, class marks, and test results. Teachers can easily design worksheets specific to their needs using only the basics of Excel. Excel can save a teacher much time in keeping track of their students progress and/or other class record keeping requirements.

Adding Column with Auto Sum Feature The sum for a list of figures in a column can easily be done. put in all figures into any column highlight the list of figures for which a total is required highlight starts from the location where the total is to be displayed when the list is highlighted, the 'non-highlighted' cell is where the total will display click on the Auto Sum icon list total will display automatically NOTE: if any figure is changed in the list, that change will automatically be reflected in the Total Using the SUM formula to put a total of a list into any cell within the worksheet The Total of any list of figures can be displayed, or shown in any cell within a worksheet. Totals do not have to be shown only at the bottom of lists. You can control which cell Totals are shown in. Totals can be displayed in any cell location in a worksheet by using the SUM formula. Using the SUM formula controls where the total figures can be assigned in the worksheet. Controlling where totals are shown allows a teacher to create additional lists of data. Getting an AVERAGE for a series of marks For any series of figures, an average can be determined by dividing the series of numbers by how many numbers there are. Example: to get an average for the series of numbers shown in the above diagram: see diagram below (same figures used as in above diagram) in the Formula divide the total number by how many marks there are in the diagram there are 6 figures to get the average, divide the total by 6 to divide use the "/" symbol NOTE: in Excel, all operations are done from left to right in the Formula, all figures within parenthesis are added first using the "/" after the parenthesis means to divide the total using '6' indicates there are 6 figures used to get the total the average will be displayed in the cell 'G7' Average of a series of marks in a row can be shown in any cell by writing the formula =SUM(D1:D6)/6 to any selected cell in the worksheet.

Average - Using the Paste Function

Past Function feature insert numbers into any column (see diagram below) select any cell where Average is to be displayed click on 'Paste Function' icon and Paste Function box will appear in Paste Function box select AVERAGE the Average box will display on screen in the top Number 1 input box insert the cells to be used (e.g. D1:D6) NOTE: individual cells, if not in order or in one column can be included (example: A2:B2:C2:D2 to get an average for a series of cells in a row) (example: A4:B5:C8:K9 to get an average of a selected group of cells in a worksheet) the average, or 'Formula Display' will be indicated at the bottom left of the Average box click OK and figure will be displayed in the selected cell chosen to display the average decimal points to the average figure can be changed by clicking on Increase Decimal, or Decrease Decimal icons

Keeping Class Records: An Example Excel is a powerful tool to help teachers keep class records. Once a worksheet is set by the teacher to meet their needs, entering marks can easily be done and student averages can be automatically maintained as new marks are entered throughout the year. The worksheet below is an example of how a teacher can keep records using Excel. In the example below: all student names are in alphabetical order (use Sort feature) headings are indicated (use of different font color, style, size) grade and class column and row are highlighted with color (use Fill feature) NOTE THE FOLLOWING: Grade Average column: A teacher can assign as many columns for tests as required. To maintain a 'running average' for each student the Average function is applied to each student row. example: for Jill Banks, the formula for cell J3 is =Average(B3:C3:D3:E3:F3:G3:H3:I3) example: for Lee Smith, the formula for cell J6 is =Average(B6:C6:D6:E6:F6:G6:H6:I6) example: Class Average formula applied to cell J10 =Average(J3:J8) example: Class Average formula applied to cell B10 =Average(B3:B8) IMPORTANT TO NOTE: It does not matter when marks are put in. It does not matter how often marks are put in. It does not matter how many tests are included. It does not matter how many tests any student has completed to get an average. It does not matter how many changes are applied to the figures by the teacher. Excel will automatically adjust the average both to the individual student Grade Average and also to the Class Average marks as marks are entered. The teacher does not have to worry about changing any averages. Excel automatically does that for the teacher.

Creating a Rubric: An Example Rubrics are developed to guide and assist a teacher in evaluating tasks or activities. Excel can be used in the development of simple or complex Rubrics. Some advantages of using Excel for the development of Rubrics: once a Rubric template is designed, it can be re-used for other tasks and activities by changing the content various results such as total marks and/or averages are easily determined to save teacher time electronic forms of Rubrics can easily be stored, retrieved, modified, copied, or sent to other staff Sample Rubric: "English 10 - Oral Presentation - "Tragedy of Hamlet" The sample Rubric below is an example of how a Rubric can be developed and used with Excel. The sample Rubric evaluates four aspects of an oral presentation: content, timing, sources, and delivery. Values are assigned to each of the various evaluations. The marking scheme for each of the individual Rubric components can be based on a single value (e.g.; 1, 2, 3, 4, 5), or on a weighted value (e.g.; 0-5, 6-10, etc) as in this example. The list of students evaluated is included on the same Excel worksheet. As the students do their presentations, the teacher assignes a mark to each Rubric component. Student marks are put into the Excel sheet. The total mark for each student and the average marks are automatically worked out on the spreadsheet. If changes to any marks are done at a latter date, Excel will revise any changes in all the required 'averages' and 'marks' sections without the teacher having to re-do any marks. The teacher can design the student section as he/she needs. In this example: the formulas for the 'Marks' and 'Averages' have been included in the appropriate cells the 'Mark/' is out of 100 the averages are based on a mark out of 25 the list of students can be as long as needed the number of Rubric columns can vary depending upon the needs of the teacher the Rubric columns can be substituted with 'Test' columns, or any other evaluation tool being used the content of each Rubric component can be changed or substituted with other information if a new Rubric is developed the number of Rubric components can easily vary depending upon teacher needs