lira lira at source.lon-capa.org
Fri Dec 25 23:36:54 EST 2015

lira		Sat Dec 26 04:36:54 2015 EDT

Log:
Adding description of setting up category grades in the spreadsheet.

\index{grade categories}\index{categories}The spreadsheet can be configured with reasonable ease to accomodate grade categories. The
strategy involves configuring assessment level spreadsheets for each category, the student level spreadsheet, and the course spreadsheet.

\noindent
\textbf{Configuring the Assessment Level Spreadsheets}

Because columns N-V are not used in the default assessment spreadsheet, pairs of columns from within
that set can be customized for each category to replicate the Available Points' (by default
in column X) and Awarded
Points' (by default in column Z). For example, columns N and O can replicate these columns for
a homework category, whereas columns P and Q can replicate these columns for a quiz category, etc.

To create a homework category assessment spreadsheet with this strategy, navigate within
the spreadsheet interface to any assessment. Modify the formula in \texttt{N0} to be \texttt{X0} (no quotes), and \texttt{O0} to be \texttt{Z0} (no quotes) and save the spreadsheet with a name such as homework\_assesscalc. See Spreadsheet Editing (\ref{Spreadsheet_Editing}). Repeat the procedure for each grade category, using different pairs of columns for each category and a different assessment spreadsheet name for each category.

\noindent
\textbf{Assigning the Assessment Level Spreadsheets and Calculating Student Grades}

The assessment level spreadsheets are assigned to specific assessments at the student level spreadsheet. Navigate to the student level sheet, and browse to find an assessment that you wish to assign to an assessment
category. In the assessment row, find in the third column a dropdown titled Assessment. Select
the desired assessment category that was created above. The formulas from the assessment spreadsheet
will be automatically applied to that row.

Notice also on the student spreadsheet, under each folder title is a dropdown that provides an option to set the default assessment spreadsheet for the entire folder. This will apply the selected spreadsheet to all existing assessments, as well as any new assessments added to the folder in the future.

Next, edit row 0 to program the summations for the pairs of columns for each grade category. Remember that row 0 columns A-Z and a-z will be
available for student viewing, so add appropriate text labels such that the calculations are clear to the
students. Remember that the cells are much more powerful that traditional spreadsheets, and the cells can contain multiple lines of Perl script. For example, to calculate homework scores and drop a certain number of points, if columns N and O contain the available and awarded points, each column can be totaled using \texttt{\&SUM(N*')} and \texttt{\&SUM(O*')} as well as logic statements and algebraic statements to apply the calculations.

Columns A-Z should contain the totals for each category and the calculated grade because only these columns will be exported to the course level sheet.

Before you leave the student spreadsheet, \textbf{be sure to save the spreadsheet or you will
loose all your customized work!} See Spreadsheet Editing (\ref{Spreadsheet_Editing}). You will want to save this spreadsheet as the default to apply the spreadsheet to all students.

After the default student spreadsheet has been created, when new assessment items are added to the course, they may be assigned to existing assessment spreadsheets by repeating this procedure, and the default student spreadsheet can be resaved.

\noindent