Exambook.xla is a set of macros designed to simplify the task of maintaining a set of marks. The underlying design philosophy of Exambook has been to "add value" to Excel without, in any way, limiting its powerful features. Exambook provides the following:
Notice (from the bottom of your display) that the workbook is organized into worksheets with the names: "Course Parameters" and "All Tests". You can navigate between these sheets by pressing these tabs.
Select the "Course Parameters" sheet and notice the version number of "Exambook for Excel". Appendix A provides a description of the version numbers and the changes made in each version of the software.
Specify "Identifiers" for each of your course marks. Specify what each result is marked out of and its percent of the final grade.
When you are finished defining the course parameters, from the "Course Setup" menu, select "Initialize/Update Course Workbook". This will create columns for each of the marks of your course.
Note that you must not be in "edit" mode to select any of the Exambook functions from the Toolbar (i.e. remember to press Enter after specifying the course parameters).
You may make changes to the Test Parameters at any time and again "Initialize/Update Course Workbook". This will not overwrite any of your own changes.
If your class list in contained in an open workbook, specify the workbook name. This would be the case if you used the Quest course roster facility to obtain your class list. This can be done by simply selecting the lines containing the student registrations, copying and pasting them into an empty workbook. Student IDs will appear in column 1, names in column 2. Note that the program will be in column 6 so you'll need to adjust the parameters as follows:
If you are using a class list obtained from the UWDir Class list facility (generally accessible by departmental secretaries or consulting offices), use the default parameters.
If you are using a class list obtained from UW-ACE, fields will be in the following order: Student ID column 4, Surname column 1, Given Names column 2, Userid column 3.
Select "Import a Class List" from the "Import/Export" menu of the toolbar. If you have not specified a workbook name, this will open a file selector panel to allow you to identify a file containing student names.
The first time you import a class list, the "Status" column will identify all of the students as 'add'. You may update your class list at any time. Newly registered students will be added to the list and will be identified as 'add'. Students who are no longer on the current list will be identified as 'drop' and students who remain on the lists will be identified as 'current'.
To work throught the examples illustrated in the document, go to the examproc sample data directory and retrieve the files: SampleNames1.txt and SampleNames2.txt. These data files comply with the default import format. Import SampleNames1.txt and then SampleNames2.txt into your spreadsheet. Notice the changes to the status column.
The above format matches results exported from examomr. For a marks file from UW-ACE, specify: Surname column 3, Mark column 2 and Userid column 4.
Back to the "All Tests" worksheet and select any cell or the column into which the marks are to be loaded and, from the "Import/Export" menu, select "Import a set of Marks". Identify the file to be imported.
To work throught the examples illustrated in the document, go to the examproc sample data directory and retrieve the files: Assign1.txt, Assign2.txt, Midterm.txt and FinalExam.txt.
Note that any students who appeared in the imported marks file who where not previously in your workbook will be added and their status will be flagged as 'mark'. The surname and given names for these students will be picked up from the test score file, but you will need to update their course, lecture, lab and divisional suffix manually.
To export specific columns, select the columns you want to export (left mouse button to select first column, hold down Control key and press left button to select additional columns) and then select "Export Marks" as above.
Note: If you are exporting marks so they can be re-imported into Exambook, select the columns: Student ID, Surname, Given names and a single column of marks. This will create a file in the standard input format supported by Exambook.
Results may also be exported for controlled display on the Web. Students are authenticated using their WatIAM userid and password and have access to only their own marks.
at the top right corner of the screen and drag down to expose column headers
in the upper section of the window.
Columns such as surname and given names may be hidden in order to print a list for posting.
To sort by name or ID within section, select either "Sort by Lecture Section" or "Sort by Lab Section" from the "Sort" menu. Select either of these items again to turn off sorting by section.
Use the "Group by" functions to view either the summary or details of a section.
Use the numbers at the top of the display (1 2 3) to display overall counts (level 1), counts by section (level 3) or a detail display of all records (level 3). Alternatively, click the top of the vertical bar to hide the records of a particular section and the "+" symbol to display the individual records.
Grouping also provides printouts with sections separated by page breaks. Use Excel's "Page Setup" menu and the scaling parameters to adjust the size of the printout if necessary.
The "Section" menu also allows you to select a particular lection or lab section for display. Clear the section field to return to a full display of all records.
As will be described later in this document, a "Final Grade" may be calculated based on the results of each mark. This mechanism may also be used to calculate interum marks. To exclude a mark from the calculation, simply set the "% of Final Grade" to 0 (in the "Course Parameters" worksheet). A result will be calculate based on the selected marks. Rename the column header from "Final Grade" to its appropriate title (e.g. Term Work).
In addition to the functions provided by Excel, a set of special purpose functions are available from Exambook:
Note that to copy the formula through the rest of the "Best Assign" column, simply grab (left mouse button) the small square at the bottom right of the active cell and drag it to the bottom of the column.
In addition to the 'Comment" column (rightmost column of the worksheet), Excel provides a very convenient mention of inserting a column into a particular cell. Select the cell and, for the 'Insert' menu, select 'Comment". Commented cells are identified using a red triangle in the upper right corner of the cell.
From the 'Stats' menu, select 'Averages, Max, Min'. This will display a series of lines containing: a count of the number of students, the averages for each test, what each test was marked out of, the highest and lowest mark (as illustrated below).
Note that these values may display as "####" after you resort the records of your spreadsheet. When this happens, select the the "Recalculate" button from the "Stats" menu.
To sequence the report by rank (as illustrated below), select
a cell in the rank column and press the sort button
.
Identical marks are given the same rank. For example, the marks 95, 95, 80 would be ranked as 1, 1, 3.
The final grade calculation is based on each student's marks and the assigned weights of all recorded marks as defined by the Parameters worksheet. Results are rounded to an integer value. Marks with a "% of Final Grade" set to 0 are excluded from the calculation.
Notice that changes may still be made to individual results with these changes reflected immediately in the student's final grade.
Handling Approved Exemptions
The calculation of final grade permits the use of the code 'aeg' (aegrotat) to designate an approved exemption from a mark (see use of this in example above). The student's final grade is then based on the remaining results without penalty for the missing entry.
Since grades must be submitted by course, division and lecture section, these three parameters are used to separate recorded results into the required worksheets.
As illustrated below, edit the report header to include the professor's name for each report.
Results are sorted into sequence by Surname, Initials and Student ID as required by the registrar's office.
The following is a list of defined names and their expected definition:
Information Systems & Technology
University of Waterloo
200 University Avenue West
Waterloo, Ontario, Canada N2L 3G1
519 888 4567 x37070
Contact Us |
Give Us Feedback |
Privacy Statement