Return to Spreadsheet_Category_Grades.tex CVS log

Up to [LON-CAPA] / loncom / html / adm / help / tex

Annotation of loncom/html/adm/help/tex/Spreadsheet_Category_Grades.tex, revision 1.6

1.1       lira        1: \label{Spreadsheet_Category_Grades}
                      2: 
1.4       raeburn     3: \index{grade categories}\index{categories}The spreadsheet can be configured with reasonable ease to accommodate grade categories. The 
1.6     ! lira        4: strategy involves configuring assessment level spreadsheets for each category, configuring the student level spreadsheet to assign the activities to categories and calculate the overall grade, and configuring the course spreadsheet to calculate overall category averages, etc.\\
1.1       lira        5: 
                      6: \noindent
                      7: \textbf{Configuring the Assessment Level Spreadsheets}
                      8: 
                      9: Because columns N-V are not used in the default assessment spreadsheet, pairs of columns from within 
                     10: that set can be customized for each category to replicate the `Available Points' (by default
                     11: in column X) and `Awarded
                     12: Points' (by default in column Z). For example, columns N and O can replicate these columns for
1.3       lira       13: a homework category, whereas columns P and Q can replicate these columns for a quiz category, etc.
                     14: The appropriate columns can be summed on the student level spreadsheet. 
1.1       lira       15: 
                     16: To create a homework category assessment spreadsheet with this strategy, navigate within
1.3       lira       17: the spreadsheet interface to any assessment. Modify the formula in \texttt{N0} to be \texttt{Z0} (no quotes) for awarded points, and \texttt{O0} to be \texttt{X0} (no quotes) for possible points and then save the spreadsheet with a name such as homework\_assesscalc. See \textbf{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.\\
1.1       lira       18: 
                     19: \noindent
                     20: \textbf{Assigning the Assessment Level Spreadsheets and Calculating Student Grades}
                     21: 
                     22: 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
1.3       lira       23: category. In the assessment row, find in the third column a dropdown titled `Assessment.' Select
1.1       lira       24: the desired assessment category that was created above. The formulas from the assessment spreadsheet
                     25: will be automatically applied to that row.
                     26: 
                     27: 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.
                     28: 
                     29: 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
                     30: available for student viewing, so add appropriate text labels such that the calculations are clear to the
1.2       lira       31: 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 awarded and available points respectively, 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. Note below how the script for homework score in column d gives students 50 `grace' points, but also adjusts for a maximum of 100\%. The quiz calculations in column h avoid an error of division by zero if no quiz scores are present.
                     32: 
                     33: \begin{verbatim}
                     34: <field col=a row=0>
                     35: `(b)Raw Homework Points:
                     36: (c)Raw Homework Possible:
                     37: (d)Scaled Homework Percentage:'
                     38: </field>
1.3       lira       39: <field col=b row=0>&SUM(`N*')</field>
                     40: <field col=c row=0>&SUM(`O*')</field>
1.2       lira       41: <field col=d row=0>
                     42: $possible = c0;
                     43: if($possible > 50) { 
                     44:   $possible=c0-50;
                     45: }
                     46: $possible > 0 ? ($percent=b0/$possible*100) : ($percent=0);
                     47: $percent >= 100 ? 100 : $percent;
                     48: </field>
                     49: <field col=e row=0>
                     50: `(f)Raw Quiz Points:
                     51: (g)Raw Quiz Possible:
                     52: (h)Quiz Percentage:'</field>
1.3       lira       53: <field col=f row=0>&SUM(`P*')</field>
                     54: <field col=g row=0>&SUM(`Q*')</field>
1.2       lira       55: <field col=h row=0>
                     56: g0 > 0 ? (f0/g0*100) : 0;
                     57: </field>
                     58: \end{verbatim}
1.1       lira       59: 
                     60: 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. 
                     61: 
                     62: Before you leave the student spreadsheet, \textbf{be sure to save the spreadsheet or you will
1.3       lira       63: loose all your customized work!} See \textbf{Modifying the Spreadsheet} (\ref{Spreadsheet_Editing}). You will want to save this spreadsheet as the default to apply the spreadsheet to all students.
1.1       lira       64: 
1.3       lira       65: 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 using the assessment selector dropdown menu in the third colum, and the default student spreadsheet can be resaved.\\
1.1       lira       66: 
                     67: \noindent
                     68: \textbf{Course Level Spreadsheet}
                     69: 
1.4       raeburn    70: After the student level sheet is created, the customized student columns A-Z will appear on the course level sheet. For that sheet, row 0 of the course level spreadsheet can be modified to perform any statistics that you would like to see as the instructor. For example, the row can be programmed to calculate the mean and standard deviation for a category. \textbf{Be sure to save any customized work as the default unless you need multiple course spreadsheets.}
1.1       lira       71: