Fri, 29 Aug 2003 21:08:40 -0000

This is a MIME encoded message

--matthew1062191320
Content-Type: text/plain

matthew		Fri Aug 29 17:08:40 2003 EDT

  Added files:                 
    /loncom/html/adm/help/tex	Spreadsheet_Calculation.tex 
                             	Spreadsheet_Calculation_Time.tex 
                             	Spreadsheet_Cell_Editing.tex 
                             	Spreadsheet_Editing.tex 
                             	Spreadsheet_Files.tex 
                             	Spreadsheet_Hierarchy.tex 
                             	Spreadsheet_HowTo.tex 
                             	Spreadsheet_HowTo_Assessments.tex 
                             	Spreadsheet_Layout.tex 
                             	Spreadsheet_Referencing_Cells.tex 
                             	Spreadsheet_Referencing_Parameters.tex 
                             	Spreadsheet_Row_Numbers.tex 
                             	Spreadsheet_Student_View.tex 

  Removed files:               
    /loncom/html/adm/help/tex	Spreadsheet_Alternative_View.tex 
                             	Spreadsheet_Changes_To_Grading.tex 
                             	Spreadsheet_Col_Sum.tex 
                             	Spreadsheet_Completely_Recalc.tex 
                             	Spreadsheet_Empty_Rows.tex 
                             	Spreadsheet_Import_Values.tex 
                             	Spreadsheet_Lists.tex 
                             	Spreadsheet_OOO_Rows.tex 
                             	Spreadsheet_Op_On_All_Problem_Parts.tex 
                             	Spreadsheet_Ranges.tex 

  Modified files:              
    /loncom/html/adm/help/tex	Spreadsheet_About.tex 
                             	Spreadsheet_Export_Row.tex 
                             	Spreadsheet_Functions.tex 
                             	Spreadsheet_Template_Row.tex 
  Log:
  New spreadsheet help files.
  
  
--matthew1062191320
Content-Type: text/plain
Content-Disposition: attachment; filename="matthew-20030829170840.txt"

Index: loncom/html/adm/help/tex/Spreadsheet_About.tex
diff -u loncom/html/adm/help/tex/Spreadsheet_About.tex:1.1 loncom/html/adm/help/tex/Spreadsheet_About.tex:1.2

--- loncom/html/adm/help/tex/Spreadsheet_About.tex:1.1	Fri Jul 26 15:53:59 2002
+++ loncom/html/adm/help/tex/Spreadsheet_About.tex	Fri Aug 29 17:08:40 2003
@@ -1,12 +1,18 @@
 \label{Spreadsheet_About}
-The \textbf{Spreadsheet} is used to tell LON-CAPA how to determine how
-many points a student gets for the LON-CAPA portion of the course. 
 
-Spreadsheets are extremely flexible and powerful and can draw upon
-nearly all aspects of the data LON-CAPA stores about a
-student. Spreadsheets can be simple things that simply give one point
-per completed problem to a student, or powerful things that give bonus
-points for completing problems early or penalties for late
-completion,, completing problems on the first try, differing values to
-different problems or problem parts, or just about anything else you
-could desire.
\ No newline at end of file
+The \textbf{Spreadsheet} is used to implement complex grading policies in
+your course.  It has the flexability to implement most grading schemes and
+it provides access to all the parameters associated with the homework and
+exams in your course.  
+
+For basic information about the structure and function of the spreadsheet,
+see \textbf{Spreadsheet Hierarchy} (\ref{Spreadsheet_Hierarchy}) 
+and \textbf{Spreadsheet Layout} (\ref{Spreadsheet_Layout}).
+
+For information about what a student will see when they view the spreadsheet,
+see \textbf{Spreadsheet Student View} (\ref{Spreadsheet_Student_View}).
+
+For help creating and editing spreadsheets, see 
+\textbf{Spreadsheet Files} (\ref{Spreadsheet_Files}) and 
+\textbf{Spreadsheet Editing} (\ref{Spreadsheet_Editing}).
+
Index: loncom/html/adm/help/tex/Spreadsheet_Export_Row.tex
diff -u loncom/html/adm/help/tex/Spreadsheet_Export_Row.tex:1.4 loncom/html/adm/help/tex/Spreadsheet_Export_Row.tex:1.5
--- loncom/html/adm/help/tex/Spreadsheet_Export_Row.tex:1.4	Wed Dec 11 14:04:47 2002
+++ loncom/html/adm/help/tex/Spreadsheet_Export_Row.tex	Fri Aug 29 17:08:40 2003
@@ -1,14 +1,9 @@
 \label{Spreadsheet_Export_Row}
-In order to get data from the lower level spreadsheets to the higher
-level spreadsheets (see \ref{Spreadsheet_Levels}), the data must be
-exported from the lower-level spreadsheet.
 
-The export row of assessment-level spreadsheets is what is exported to
-the student-level sheet.\index{export row}
-
-The export row of student-level spreadsheets is what is exported to
-the course level sheet.
-
-Only the columns labeled with uppercase letters will be exported. The
-lowercase letters are available for additional calculations used only
-on the current level of the spreadsheet.
+The export row determines what information is passed to the next higher
+spreadsheet in the \textbf{spreadsheet hierarchy} 
+(\ref{Spreadsheet_Hierarchy}).  
+The first 26 columns of the row, labeled ``A-Z'', are
+exported.  The remaining 26 columns, labeled ``a-z'', 
+are not and may be used for supporting computations.  
+The export row is row number 0.
Index: loncom/html/adm/help/tex/Spreadsheet_Functions.tex
diff -u loncom/html/adm/help/tex/Spreadsheet_Functions.tex:1.3 loncom/html/adm/help/tex/Spreadsheet_Functions.tex:1.4
--- loncom/html/adm/help/tex/Spreadsheet_Functions.tex:1.3	Mon Jul 29 10:51:43 2002
+++ loncom/html/adm/help/tex/Spreadsheet_Functions.tex	Fri Aug 29 17:08:40 2003
@@ -1,5 +1,7 @@
 \label{Spreadsheet_Functions}
-The following functions are available in the spreadsheet:
+The following special functions are available in the spreadsheet.
+Please see \textbf{Referencing Cells} (\ref{Spreadsheet_Referencing_Cells})
+for information on specifying cells and ranges.
 
 \begin{itemize}
 
@@ -43,6 +45,4 @@
 
 \end{itemize}
 
-In addition, non-IO Perl functions work in cells, which is internally
-evaluated within a safe space. Field names and Column-Row combinations
-can be used as variables.
\ No newline at end of file
+In addition, most non-IO Perl functions work in cells.
Index: loncom/html/adm/help/tex/Spreadsheet_Template_Row.tex
diff -u loncom/html/adm/help/tex/Spreadsheet_Template_Row.tex:1.4 loncom/html/adm/help/tex/Spreadsheet_Template_Row.tex:1.5
--- loncom/html/adm/help/tex/Spreadsheet_Template_Row.tex:1.4	Mon Jul 29 13:27:43 2002
+++ loncom/html/adm/help/tex/Spreadsheet_Template_Row.tex	Fri Aug 29 17:08:40 2003
@@ -1,4 +1,11 @@
 \label{Spreadsheet_Template_Row}
-The template row\index{template row} is a way to use the same formula
-in every row of a sheet. The actual row number is replaced by a
-``\#''. For example, ``A\#*b\#'' would be ``A5*b5'' in row 5.
\ No newline at end of file
+
+The template row allows you to quickly fill in columns of your spreadsheet.
+If a cell in the template row has a value or formula, every cell in
+that column of the main body of the spreadsheet will be given the same value 
+or formula.  
+If a cell in the spreadsheet already has a value, that value will be preserved.
+
+Using the ``#'' notation to specify rows is highly suggested.  See 
+\textbf{Referencing Spreadsheet Cells} (\ref{Spreadsheet_Referencing_Cells}) 
+for more information.

Index: loncom/html/adm/help/tex/Spreadsheet_Calculation.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Calculation.tex
\label{Spreadsheet_Completely_Recalc}
The total number of sheets in course can be tremendous, since it is
the number of students times the number of assessments. LON-CAPA
caches these sheets and only selectively invalidates those cache
copies if potentially relevant data changes. \textbf{Completely
Recalc} forces LON-CAPA to invalidate all cache copies.
\index{Completely Recalc}

For instance, this is necessary to get the most up-to-date
calculations if the sheet itself contains direct access to the system
clock (making it ``out-of-date'' the moment it is calculated), or if
an assessment is edited in a way that would retroactively change
grading. The automatic spreadsheet devalidation catches student
submissions, \textbf{PAR}a\textbf{M}eter changes, and spreadsheet
changes, but not re-publication of a problem resource.  \label{Spreadsheet_Max_Depth_Exceeded}
The ``Maximum Calculation Depth Exceeded''\index{Maximum Calculation
Depth Exceeded error} error can occur when you reference other cells in
calculations. For example, if you have: 

\texttt{G0 = Some\_complicated\_expression\\
 X0 = G0>2?1:0}

Try replacing \texttt{X0}'s contents with \texttt{[
(Some\_complicated\_expression) > 2]?1:0}. In other words, replace the
reference to \texttt{G0} with the actual contents of \texttt{G0}. That
might fix your error by removing one level of indirection that
LON-CAPA must process in order to compute the result.  

Index: loncom/html/adm/help/tex/Spreadsheet_Calculation_Time.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Calculation_Time.tex
\label{Spreadsheet_Completely_Recalc}
The total number of spreadsheets in course can be tremendous, since it is
the number of students times the number of assessments. LON-CAPA
caches these sheets and only selectively invalidates those cache
copies if potentially relevant data changes. 

Index: loncom/html/adm/help/tex/Spreadsheet_Cell_Editing.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Cell_Editing.tex
\label{Spreadsheet_Cell_Editing}

To edit a cell in the spreadsheet, move you mouse pointer over it until you 
are able to follow a link.  Click on the link to open up an editing window.  
The current contents of the cell will be shown and can be modified.  
Pressing the ``Accept'' button will cause the spreadsheet to be updated with 
the new formula.  
You will then be working with an unsaved modified copy of a spreadsheet.  
See \textbf{Spreadsheet Files} \ref{Spreadsheet_Files} for information on 
loading and saving spreadsheets.
Pressing the ``Discard Changes'' button will discard any changes you have
made to the contents of the cell.

\textbf{What goes in a cell}

The cells of the spreadsheet contain a mixture of Perl commands and LON-CAPA
specific functions (\ref{Spreadsheet_Functions}).  
Most non-IO Perl functions work in cells.

Index: loncom/html/adm/help/tex/Spreadsheet_Editing.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Editing.tex
\label{Spreadsheet_Editing}


The strength of the spreadsheet is its ability to be modified to reflect 
just about any grading method used in a course.  The following topics
should prove helpful in getting you started editing spreadsheets.

\begin{itemize}

\item \textbf{Cell Editing} (\ref{Spreadsheet_Cell_Editing})

\item \textbf{Referencing Cells} (\ref{Spreadsheet_Referencing_Cells})

\item \textbf{Referenceing Parameters} (\ref{Spreadsheet_Referencing_Parameters})

\item \textbf{Special Functions} (\ref{Spreadsheet_Functions})

\item \textbf{Examples and Comments} (\ref{Spreadsheet_HowTo})

\item \textbf{Examples and Comments on Assessment spreadsheets} 
(\ref{Spreadsheet_HowTo_Assessments})

\end{itemize}

Index: loncom/html/adm/help/tex/Spreadsheet_Files.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Files.tex
\label{Spreadsheet_Files}

If you have permission to edit a spreadsheet you will also have permission to
save and load spreadsheet definition files.  

\textbf{Default Spreadsheets}

There are three default spreadsheets, one for each level.  These are called
\texttt{default_classcalc}, \texttt{default_studentcalc}, and 
\texttt{default_assesscalc}.
These are the starting spreadsheets for every class. 

\textbf{Modified Spreadsheets}

When you modify a spreadsheet you begin working with an temporary copy.  
You may save the spreadsheet using the ``Save as'' 
button on the spreadsheet.  Fill in a name and click on the ``Save as'' button.
You may also use the ``Save as \& Make This Sheet the Default'' button
to set the current spreadsheet definition as the default for this level.

\textbf{Loading a Spreadsheet Definition from a Published File}

Using the ``Select Spreadsheet File'' link opens the LON-CAPA resource browser.
Only files with the extension \texttt{spreadsheet} can be selected.  The
spreadsheet definition file must be valid XML.  Please contact the LON-CAPA
development team for instructions and examples on creating these files.

\textbf{Setting the Assessment Level Spreadsheets used to Calculate Grades}

In the student level spreadsheet every assessment in the course has a 
spreadsheet definition file associated with it.  
The spreadsheet definition used for each assessment
is selected using a drop-down dialog in the assessments row.
Initially this spreadsheet is ``Default'', meaning the current default 
assessment spreadsheet will be used to compute the students grade.

Changing the spreadsheet used for an assessment modifies the student level 
spreadsheet, so you must use the ``Save as'' button to save the current
student spreadsheet.


Index: loncom/html/adm/help/tex/Spreadsheet_Hierarchy.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Hierarchy.tex
\label{Spreadsheet_Hierarchy}

There are three different types of spreadsheets inside LON-CAPA.

\textbf{The Assessment Spreadsheets}

An assessment level spreadsheet provides access to all of the parameters
associated with a students performance on a particular homework, quiz, 
or exam.
Each student has an assessment spreadsheet for every assessment in the course.
The assessment spreadsheets provide data to their parent, the student level
spreadsheet, via the \textbf{export row} (\ref{Spreadsheet_Export_Row}).

\textbf{The Student Spreadsheet}

The student level spreadsheet presents data to the students on their 
performance in the course.  
There is only one possible student level spreadsheet definition, 
but the data varies for each student.
The data present in the student spreadsheet is imported from the 
\textbf{export row} (\ref{Spreadsheet_Export_Row})
of each assessment spreadsheet for all of the the assessments currently in 
the course.  
The student spreadsheets pass summary data to the course level spreadsheet
via the \textbf{export row} (\ref{Spreadsheet_Export_Row}).

\textbf{The Course Spreadsheet}

The course spreadsheet contains all of the summary data for the students in
the course.   There is only one course spreadsheet.

Index: loncom/html/adm/help/tex/Spreadsheet_HowTo.tex
+++ loncom/html/adm/help/tex/Spreadsheet_HowTo.tex
\label{Spreadsheet_HowTo}

\textbf{Summing up columns}

To take the sum of column ``M'', for example, use \texttt{\&SUM(``M*'')}\index{SUM}

\textbf{Spreadsheet_Lists}
When you have a list of numbers in a cell, how it is displayed depends
on how you separate the numbers and how Perl interprets the results.

\textbf{35 45 12} will not generate any result because this is an
invalid perl statement.

\textbf{35,45,12} will have a result of 12 because Perl has a comma
operator similar to the comma operator\index{comma operator} in C.

The comma operator is binary and returns the value on its right. Thus
\texttt{\$variable = 15, 26;} assigns \texttt{\$variable} the value
26.  If you need the values to all be displayed, enclose the entire
cell contents in quotes.  

Index: loncom/html/adm/help/tex/Spreadsheet_HowTo_Assessments.tex
+++ loncom/html/adm/help/tex/Spreadsheet_HowTo_Assessments.tex
\label{Spreadsheet_HowTo_Assessments}

\textbf{How to deal with multi-part problems}

Often, there are several parts in a specific problem. For example, a
problem with three parts would have parts 0, 11, 12, and 13. For a
general spreadsheet, it is not often desirable to sum up all of these
parts, while not knowing how many parts there are as the spreadsheet
is written.

The spreadsheet has a preprocessor which an expand a symbolic
expression over all symbolic names that fit. The general syntax is
\index{EXPANDSUM} \texttt{[\&EXPANDSUM(VARNAME;expression)]}. 

For example, for the above assessment with three parts,

\texttt{\&EXPANDSUM(}\texttt{\textbf{PART}}\texttt{;parameter\_}\texttt{\textbf{PART}}\texttt{\_weight{*}stores\_}\texttt{\textbf{PART}}\texttt{\_awarded)}

would become

\texttt{parameter\_0\_weight{*}stores\_0\_awarded +}~\\
\texttt{parameter\_11\_weight{*}stores\_11\_awarded +}~\\
\texttt{parameter\_12\_weight{*}stores\_12\_awarded +}~\\
\texttt{parameter\_13\_weight{*}stores\_13\_awarded +}~\\

where \textbf{bolded text} is used to highlight what the
\texttt{\&EXPANDSUM} function is doing.

\textbf{What 'tries' means}

In multi-part questions, the exported value for "tries"\index{tries} is now 
the average number of tries to get the parts right. 
The full data for each part is still stored by the system. 

Index: loncom/html/adm/help/tex/Spreadsheet_Layout.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Layout.tex
\label{Spreadsheet_Layout}

This section describes the layout of each level of spreadsheet.  See the
section on the \textbf{Spreadsheet Hierarchy} (\ref{Spreadsheet_Hierarchy}) 
for a description of the types of spreadsheets and their data-flow 
relationships.


\textbf{General Characteristics} 

Each spreadsheet has 52 columns and a variable number of rows.  
The columns of the spreadsheets are labeled 'A-z'.
The rows of the spreadsheets contain data appropriate to the spreadsheet as 
described below.  The section \textbf{Spreadsheet Row Numbers} 
(\ref{Spreadsheet_Row_Numbers}) describes how row numbers are assigned.

\textbf{Assessment Spreadsheet Layout}

There are two special rows on the top of the assessment spreadsheet.
The first is the \textbf{template row} (\ref{Spreadsheet_Template_Row}).
The second row is the \textbf{export row} (\ref{Spreadsheet_Export_Row}).

The main body of the assessment spreadsheet contains the parameters associated
with the assessment.  The parameter values appear in the ``A'' column of the
spreadsheet and cannot be edited.  The remainder of the spreadsheet columns,
labeled ``B-z'', can be edited and used for computations.

See \textbf{Referencing Spreadsheet Parameters} 
(\ref{Spreadsheet_Referencing_Parameters}) for information
on how to reference a parameter value in the spreadsheet.

\textbf{Student Spreadsheet Layout}

The student spreadsheet has both a 
\textbf{template row} (\ref{Spreadsheet_Template_Row}) and an 
\textbf{export row} (\ref{Spreadsheet_Export_Row}).

The main body of the student spreadsheet contains the export rows of the
assessments in the course.  Each assessment appears beneath the title of
the folder or sequence which it belongs to.  The first 26 columns of the
main body of the spreadsheet, ``A-Z'', are the export data from the assessments
and cannot be edited.  The remaining columns, ``a-z'', can be edited.

\textbf{Course Spreadsheet Layout}

The course spreadsheet contains the data exported from each student level
spreadsheet. 

The two topmost rows are the \textbf{template row} 
(\ref{Spreadsheet_Template_Row}) and a summary row.  
The remainder of the spreadsheet it taken up with student data.
Each student spreadsheet exports one row to the course spreadsheet.  The
first 26 columns, ``A-Z'', contain the student data and cannot be edited.  
The remaining 26 columns ``a-z'', can be edited.

Index: loncom/html/adm/help/tex/Spreadsheet_Referencing_Cells.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Referencing_Cells.tex
\label{Spreadsheet_Referencing_Cells}

\textbf{Cells}

Cells may be specified by simply writing their name.  \texttt{A17} will be
replaced with the value in row A column 17 before the perl is evaluated.

\textbf{Ranges}

Ranges \index{ranges, spreadsheet} specify rectangles of various shapes
in the spreadsheet, just as ranges do in traditional spreadsheets. 
Ranges must be enclosed in quotes.  Examples of legitimate ranges: 

\begin{itemize}

\item \textbf{``*''} - all rows, all columns 
\item \textbf{``B*''} - all rows in column B 
\item \textbf{``*5''} - all columns in row 5 
\item \textbf{``C5..F25''} - all cells in the rectangle between C5 and F25

\end{itemize}

Many functions accept ranges. For example, \&SUM(``d*'') will add 
up all cells in column d.

\textbf{Special References}

There is an additional means of referencing cells most used in the 
\textbf{template row} (\ref{Spreadsheet_Template_Row}).  
By using a ``#'' in the place of a row number
the spreadsheet will replace the # with the current row number.  For example
\texttt{B#} will be replaced with the contents of cell B7 in the row 7 and
the contents of B8 in row 8.

Index: loncom/html/adm/help/tex/Spreadsheet_Referencing_Parameters.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Referencing_Parameters.tex
\label{Spreadsheet_Referencing_Parameters}

In the assessment level spreadsheet the parameters are available for
calculation in two ways.

1. Reference the cell containing the parameter: \texttt{A7}

2. Reference the parameter by name: \texttt{[parameter_0_weight]}

In each case the value of the parameter will replace the reference before the
perl code in the cell is executed.

Index: loncom/html/adm/help/tex/Spreadsheet_Row_Numbers.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Row_Numbers.tex
\label{Spreadsheet_Row_Numbers}

LON-CAPA ensures every row in a spreadsheet is uniquely and persistently
assigned a row number.  Once a row number is used, it cannot be reused.
The following sections describe the consequences
of this and answer ``why are the rows of the spreadsheet out of order?''.

\textbf{Assessment Spreadsheet}

Every parameter associated with an assessment has a unique row in the
assessment level spreadsheet.  As new parameters are encountered they
are assigned the next available row number.  Adding a parameter to an
assessment thus results in the new parameter appearing at the bottom of
the spreadsheet.  Since the default spreadsheet definition must cover all
of the assessments in the course, there are likely to be a large number of
parameters available.

\textbf{Student Spreadsheet}

The student level spreadsheet displays information about each assessment
in the course.  The row numbers are initially assigned based on the order 
in which assessments appear in the course.  If a new assessment is 
added at a later time, it will be assigned the next higher row number.
Regardless of the row number, the assessment will appear in the proper place
in the course structure.

\textbf{Course Spreadsheet}

The rows of the course spreadsheet are assigned to students as they are
encountered in the class list.  Each student is 
assigned a row number the first time they are present in the course.
The rows are listed alphabetized by the students full name and the row
numbers are assigned in this order.  If a student is later added, they
will appear in the proper place alphabetically in the spreadsheet but
their row number will not be in sequence.

Index: loncom/html/adm/help/tex/Spreadsheet_Student_View.tex
+++ loncom/html/adm/help/tex/Spreadsheet_Student_View.tex
\label{Spreadsheet_Student_View}

The student view of the spreadsheet is restricted to the student level
spreadsheet.  Students are not able to view assessment level spreadsheets.
Students cannot modify the spreadsheet in any way and cannot load or save
spreadsheets.  

If the course contains assessments which indicate the student should not 
be able to view the results, as is the default for an exam, the row will appear
blacked out and the data will not be present.  Once the answer date has passed
for the resource, the row will be shown.

--matthew1062191320--


