lira lira at source.lon-capa.org
Sun Dec 27 23:07:36 EST 2015

lira		Mon Dec 28 04:07:36 2015 EDT

Modified files:
Log:
Add example formulas for two categories.

@@ -13,7 +13,7 @@
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.
+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 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
@@ -27,7 +27,34 @@

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.
+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.
+
+\begin{verbatim}
+<field col=a row=0>
+(b)Raw Homework Points:
+(c)Raw Homework Possible:
+(d)Scaled Homework Percentage:'
+</field>
+<field col=b row=0>&SUM('N*')</field>
+<field col=c row=0>&SUM('O*')</field>
+<field col=d row=0>
+$possible = c0; +if($possible > 50) {
+  $possible=c0-50; +} +$possible > 0 ? ($percent=b0/$possible*100) : ($percent=0); +$percent >= 100 ? 100 : \$percent;
+</field>
+<field col=e row=0>
+(f)Raw Quiz Points:
+(g)Raw Quiz Possible:
+(h)Quiz Percentage:'</field>
+<field col=f row=0>&SUM('P*')</field>
+<field col=g row=0>&SUM('Q*')</field>
+<field col=h row=0>
+g0 > 0 ? (f0/g0*100) : 0;
+</field>
+\end{verbatim}

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.

@@ -39,5 +66,5 @@
\noindent
`