This section reviews the features available in
Spartan's spreadsheet for handling data from a series of calculations. These include plot construction and
data fitting to a variety of simple functional forms.
Spartan allows grouping of molecules into lists. Some of these lists are
very closely related and are automatically formed by
Spartan. Examples include lists which result from conformation searching (see
Section 7.2), as well as sequences of molecules which follow a particular vibrational mode (see
Section 7.4) or, more generally, any userdefined set of coordinates (see
Section 7.5). Lists may also result from single or multiple substitutions on a "parent" molecule
(see Section 7.6), or simply follow from grouping molecules as convenient (see
Group As, Section 4.7). In all of these situations, lists may be treated either as
single molecules or as an entire group insofar as job setup (specification of kind
and level of calculation and request for properties and/or graphics), and need to
be treated as a group insofar as job submission.
Creation (or selection) of a list leads to display of a spreadsheet dialog (or
simply spreadsheet).
Initially, the first member of the list will be selected, and only its structure
model will be displayed on screen. A different member of the list may be
selected by clicking on the box to the left of the name of the member (far left in
the spreadsheet).
Controls at the bottom of the spreadsheet provide a variety of display options:
 Multiple
Controls whether a single member of the list, or several members
of the list, are simultaneously displayed on screen.
Multiple "off" indicates only a single member (that selected in the spreadsheet) is to be
displayed, while Multiple "on" indicates that all selected members of the list
are to be displayed.
The result of selecting individual members of the list depends
whether Multiple is "on" or "off". With
Multiple "off", selecting a new
member deselects the previously selected member. With
Multiple "on", selecting a new member leaves the original member(s) selected, while
selecting the "selected" entry deselects it. The only exception is that the
"last" member cannot be "deselected".
 Animate
Animates (rapid stepping through) the molecules in a list. It is
particularly useful for visualizing the results of a normalmode analysis
(see descriptions in Sections 7.4 and
9.6), or the driving of coordinates from one structure to another (see description in
Section 7.5). Note that animation is not restricted to structure; any graphic associated with
a particular molecule may also be animated. Animation may only be
turned "on" if Multiple is "off".
 keys
Manually steps forward ( ) or backward
( ) through the list of molecules. Stepping is allowed only if
Multiple is "off".
 Expert
Extends the spreadsheet.
An extended text field appears at the top of the dialog, the contents
of which indicate the "generating formula" of the selected header for
a column or of an individual cell in the spreadsheet. Information, in
the form of a header name or a formula, entered into this text field will
also be entered into the header (to be propagated throughout the column)
or into an individual cell.
Additional rows appear at the bottom of the spreadsheet. These
display information relating to the entire set of column entries, for example,
the sum of the entries or their average. Further information is provided
under Spreadsheet Operations.
Section 11.1: Spreadsheet Operations
Molecules contained within a spreadsheet may either be acted on as
individuals or be treated as part of a group. As individuals, each member has access to
the full functionality of Spartan's graphical user
interface, except for job submission, which needs to be handled for the entire group. Optionally, job
setup (selection of level of calculation, request for properties, graphical surfaces,
etc.) may either be done on an individual basis or for the collection as a whole.
Spartan's spreadsheet operations are typical of those of general
purpose spreadsheets, although they are more limited.
 Numerical Data
Numerical data may be directly entered into the spreadsheet. First, it
is necessary to define a column header, by
clicking above the next available column, entering a name and pressing Enter. Data are entered into
the individual cells by clicking inside the appropriate cell, entering
a numerical value and pressing Enter. Existing data (in any
spreadsheet column) may be edited by
clicking inside the appropriate cell, altering
or replacing the existing number and pressing
Enter.
 Expressions
Column headers may involve algebraic expressions: name =
expression. Expressions may contain numerical data, previously defined
column headers, the following mathematical and support functions,
ABS(x) 
absolute value 
LN(x) 
natural logarithm 
ACOS(x) 
inverse cosine 
LOG(x) 
log (base 10) 
ASIN(x) 
inverse sine 
SIN(x) 
sine 
ATAN(x) 
inverse tangent 
SQRT(x) 
square root 
COS(x) 
cosine 
TAN(x) 
tangent 
EXP(x) 
exponential 



AVG(x) 
average 
NUM(x) 
number of entries 
MAX(x) 
maximum 
STDEV(x) 
standard deviation 
MIN(x) 
minimum 
SUM(x) 
sum 
the following quantities derived from molecular mechanics and
quantum chemical calculations,
AM1AQ  AM1aq aqueous solvation energy (kcal/mol) 
AM1HD  AM1hd hexadecane solvation energy (kcal/mol) 
AM1OCT  AM1oct octanol solvation energy (kcal/mol) 
AM1SM2  AM1SM2 aqueous solvation energy (kcal/mol) 
ANGLE(i,j,k)  angle involving atoms i, j, k (degrees) 
CPKAREA  surface area of a spacefilling model(Å2) 
CPKVOLUME  volume of a spacefilling model (Å3) 
DIHEDRAL(i,j,k,l)  dihedral angle involving
atoms i, j, k, l (degrees) 
DIPOLE  dipole moment (debyes) 
DISTANCE(i,j)  distance involving atoms i, j (Å) 
ELECTROSTATIC(i)  electrostatic charge on atom i (electrons) 
ENERGY  energy (hartrees) 
ENERGYAM1AQ  energy including AM1aq aqueous
solvent correction (hartrees) 
ENERGYAM1HD  energy including AM1hd
hexadecane solvent correction (hartrees) 
ENERGYAM1SM2  energy including AM1SM2 aqueous solvent
correction (hartrees) 
ENTROPY  entropy (cal/mol.degree) 
FREEENERGY  free energy (kcal/mol) 
HOMO(N)  HOMO energy (hartrees) 
LOGPC  LogP according to Crippen model 
LOGPH  LogP according to DixonHehre model 
LOGPV  LogP according to Villar model 
LUMO(+N)  LUMO energy (hartrees) 
MULLIKEN(i)  Mulliken charge on atom i (electrons) 
NATURAL(i)  natural charge on atom i (electrons) 
ZEROPOINT  zeropoint energy (kcal/mol) 
and/or the following specialty functions.
ROW  the number of the row in the spreadsheet 
ROW(molecule name)  the number of the row for molecule 
REF(i, x)  the value of the x referenced to row i 
Mathematical, "Spartan", and specialty functions need to be
preceeded by "@". For example, @DIPOLE accesses the dipole moment.
Arguments for mathematical functions may either be numbers, previously
defined header labels, or other mathematical or
"Spartan" functions (preceeded by an "@" sign), while arguments (i) for
"Spartan" functions are atom labels, e.g., @DISTANCE (C1,C2) gives the distance between atom
"C1" and atom "C2". Arguments (N) for HOMO and LUMO are
decrement and increment values, respectively, and are optional.
The usual arithmetic operations are supported.
+  addition 
  subtraction 
*  multiplication 
/  division 
^  raise to a power 
It is necessary to press Enter following entry of the expression into
the column header.
Examples of expressions include:
energy.area = @Energy/@AREA 
energy divided by surface area 
rel.energy = @Energy@REF(6,@ENERGY) 
energy relative to energy of molecule in row 6 
equil = @EXP (@Energy/592.1) 
equilibrium constant at room temperature 
Data filtering (see Section 11.2.4) requires that Boolean operations
be carried out inside the spreadsheet. These are of the form: name
= expression, where the expression may now include Boolean
operators from among the following.
> 
greater than 
>= 
greater than or equal to 
< 
less than 
<= 
less than or equal to 
== 
equal to 
!= 
not equal to 
 
or 
& 
and 
Examples of expressions incorporating Boolean operators include:
energyfilter = @Energy < 99.43 
"true" (0) for all energies < 99.43 
rowfilter = @ROW> 10 
"true" (0) all entries past row 10 
The extended form of the spreadsheet
(Expert "on") permits calculation and display of quantities which relate to the entire collection of
molecules (rows in the spreadsheet). The most important are sum of the rows
(SUM), the average (AVG), the standard deviation (STDEV) and the
minimum and maximum values (MIN and MAX, respectively).
Expressions involving these functions and other quantities are constructed in the
same way as previously described, and entered into the header (left
most) column at the bottom of the (extended) spreadsheet. For example,
the expression "average = @AVG" provides the average value of the
rows for each and every column of the spreadsheet.
Section 11.2: Menus in the Spreadsheet
Other editing/display functions associated with
Spartan's spreadsheet are accessed by way of menus, from a menu bar.
Section 11.2.1: Sheet
Selection results in display of the following menu:

Calculator 
Export 
Print 
Show Formulas 
Calculator
Opens a "calculator", the workings of which have already been described
in Section 3.7. The calculator and open spreadsheet are linked insofar that
clicking inside a cell of the spreadsheet enters the numerical value into the
calculator. This allows reaction energies to be computed directly from the tabulated data.
Export
Reformats the entire contents of the spreadsheet as "tab delimited" for export
to Microsoft Excel or other general purpose spreadsheet programs. The user
is presented with a file browser and must supply a name. An attempt to
overwrite an existing file will be preceded by a warning message.
Clicking on Export writes the spreadsheet to the file.
Print
Prints the spreadsheet (according to the specifications made in the last access
to Print Setup; see Section 3.4).
Show Formulas/Show Values
Replaces the numerical values in the spreadsheet cells by their
generating formulas. Upon selection, the menu entry is changed to
Show Values, selection of which replaces the formulas by numerical values.
Section 11.2.2: Molecule
Selection results in the following menu:

Insert 
Extract As 
Delete 
Delete to End 

Decouple Coordinates 
Align Coordinates 
Insert
Inserts a molecule or list of molecules into the spreadsheet. Selection leads
to the usual file browser. Double clicking on a name, or
clicking on a name followed by
clicking on Insert, inserts the molecule or list of molecules following the
last entry of the spreadsheet. No sorting is done on the entries in the spreadsheet.
Extract As
Extracts one or more members from a list and saves them.
Extract As functions with Multiple either "on" or "off". With
Multiple "off", Extract As saves
the selected member as a single molecule. With Multiple
"on", Extract As saves all selected members of the list as a new list, unless only one member is
selected, in which case this member is saved as a single molecule. Selection of
Extract As results in a file browser, and request for a name. If the name supplied
already appears in the working directory, this leads to a warning that
preexisting information is to be overwritten. Once a file name has been supplied,
clicking on Extract saves the information and exits the dialog. The original spreadsheet
is closed, but all molecules in the spreadsheet which were selected prior to
Extract
As remain on screen and constitute a new spreadsheet. If only a single
molecule was extracted, it remains on screen but the spreadsheet disappears.
Clicking on Cancel exits the dialog with no further action.
Delete
Eliminates from the spreadsheet whatever member is presently selected.
A warning that the actions to be taken will result in deletion of all files
associated with the particular molecule appears on screen.
Assuming that this is what is intended, deletion occurs by
clicking on Delete. Clicking on
Cancel removes the dialog but does not result in molecule
deletion. Delete is not available with Multiple
"on".
Delete to End
Eliminates from the spreadsheet whatever member is presently selected, and
all members following it in the spreadsheet. A warning that the actions to be
taken will result in deletion of all files associated with the particular molecule
or molecules appears on screen.
Assuming that this is what is intended, deletion occurs by
clicking on Delete. Clicking on
Cancel removes the dialog but does not result in molecule
deletion. Delete to End is not available with
Multiple "on".
Decouple Coordinates
Controls whether or not the motions of molecules in the spreadsheet which
are simultaneously displayed (see discussion under
Multiple) are coupled. It is important to couple the motions for visual examination and construction
and display of difference plots for molecules which have previously been
aligned (see Align Coordinates below). The initial setting is for molecular motions
to be coupled. Selection of Decouple Coordinates
allows them to be moved (translated and rotated) independently, and changes the menu label to
Couple Coordinates. Selection of Couple Coordinates
again locks the motions in step and returns the menu entry to
Decouple Coordinates. Decouple
Coordinates is accessible only if
Multiple is "on".
Align Coordinates
Aligns all molecules in the spreadsheet based on highest coincidence of
designated centers. The selected molecule acts as the template for
alignment. Common substructures are automatically identified wherever possible.
Relabeling is possible and will override the automatic
assignments. Operation is as follows: First, select a "parent molecule" (the
molecule to which other members of the list are to be aligned), then
select Align coordinates from the molecule menu in the spreadsheet.
Selection of Align results in a message in the menu bar.
Align: Select three or more atoms to align.
Atoms may be selected for alignment by clicking on them in turn,
and may be deselected by clicking again. Selected atoms will be
visibly marked as such. Note that at least three (noncolinear) atoms
need to be selected for alignment to be meaningful, and it is recommended
that no more than ten atoms be selected. It is up to the user to ensure
that atoms are labelled in a consistent manner for different members in the
list. Relabeling, if it is necessary, can be done using Edit Labels
under the Model menu (see
Section 5.9). Following selection
of the appropriate atoms, clicking on Align, aligns the
molecules in the list. Clicking instead on Cancel exits
without alignment being performed.
Section 11.2.3: Column
Selection results in the following menu:

Add Energy 
Add Rel. Energy 
Add 

Delete 
Sort 

Copy 
Paste 
Add Energy
Selection leads to display of a submenu:
Energy in hartrees
 Energy in kcal/mol
 Energy in kJ/mol

Enters the total energy (or heat of formation for a
semiempirical calculation or strain energy for a molecular mechanics calculation)
in hartrees, kcal/mol or kJ/mol, depending on the selection, for all
members of the list into the next available spreadsheet column. For
calculation methods which produce multiple energies, e.g., MP2, only the
final energy, e.g., the MP2 energy, is displayed. Other energies, e.g.,
the HartreeFock energy in the case of an MP2 calculation, needs to be
entered by way of Post in the Energy dialog (see
Sections 9.2.1, 9.2.2 and
9.2.3).
Add Rel. Energy
Selection leads to display of a submenu:
Energy in hartrees
 Energy in kcal/mol
 Energy in kJ/mol

Enters the total energy (or heat of formation for a semiempirical
calculation or strain energy for a molecular mechanics calculation) in hartrees,
kcal/mol or kJ/mol, depending on the selection, for all members of the
list, relative to the corresponding total energy (heat of formation, strain
energy) of the selected molecule, into the next available spreadsheet column.
Add
Selection results in display of a submenu:
Dipole
 E(HOMO)
 E(LOMO)
 Molecular Weight
 Surface Area
 Volume

Dipole
Enters the dipole moment (in debyes) for an
ab initio, density functional or semiempirical calculation, or the dipole moment resulting
from calculated atomic charges in a MMFF molecular mechanics
calculation for all members of the list, into the next available spreadsheet column.
E(HOMO)
Enters the energy of the highestoccupied molecular orbital (in
hartrees for an ab initio or density functional calculation and in eV for a
semiempirical calculation) for all members of the list into the next
available spreadsheet column.
E(LUMO)
Enters the energy of the lowestunoccupied molecular orbital (in
hartrees for an ab initio or density functional calculation and in eV for a
semiempirical calculation) for all members of the list into the next
available spreadsheet column.
Molecular Weight
Enters the molecular weight (in atomic mass units) for all members
of the list into the next available spreadsheet column.
Surface Area
Enters the surface area of a spacefilling model (in
Å2) for all members of the list into the next available spreadsheet column.
Volume
Enters the volume of a spacefilling model (in
Å3) for all members of the list into the next available spreadsheet column.
Delete
Deletes all entries from the selected column. No warnings are provided.
Sort
Sorts the entries in the selected column from smallest to largest values.
Copy
Copies all entries in the selected column to the clipboard.
Paste
Pastes the contents of the clipboard into the next available spreadsheet
column. If there are fewer entries than rows in the spreadsheet, entries for the extra
rows are left blank. If there are more entries than rows in the spreadsheet, extra
entries are not pasted.
Section 11.2.4: Plot
Selection results in a submenu:

Create 
Edit 
Delete 
Create
Selection leads to an onscreen dialog.
This contains two (identical) lists of the columns in the spreadsheet (labeled
"X Axis" and "Y Axis", respectively). The first entry in both lists, "Molecule",
is simply the number of the molecule in the list, and the other entries are
named according to the column headers. One or more plots are constructed by
selecting (clicking on) one entry from X
Axis and one or more entries from Y
Axis, followed by clicking on
Create. In response, the dialog is closed and a plot appears.
The plot behaves as any other graphical object insofar as it
can be translated or scaled (but not rotated). It first needs to be selected
by clicking on any portion of the plot, i.e., on any of the individual
graphs or on an axes. A yellow frame appears, indicating the plot as the selected. A molecule may be selected
(and the plot deselected) by clicking on it.
Colors for the plot axes and/or for the individual graphs which make up the
overall plot may be adjusted using Colors from the
Logo menu (see Section 3.2).
The initial data representation is in terms of points connected by a
socalled cubic spline. This is a smooth function which actually passes through all
the data points. The data representation may be altered using functions under
the Edit menu (described below).
With Multiple "off" (see previous discussion), the selected molecule
(designated by a gold sphere on each of the individual graphs in the plot) may be
stepped using the and keys or animated using
Animate. The molecule changes in response. Step and animate functions are not available with
Multiple "on", although in this mode several different molecules may be displayed at
once. (Note that the molecules may either be moved together or as individuals;
see Decouple Coordinates, below.) Even with
Multiple "on", only the molecule actually selected is designated by a gold sphere on the graph (or graphs).
Selection of Expert (in the top left hand corner) extends the
Create dialog to include a third list
"Filters".
These act in a Boolean manner to "filter" (include or exclude) specific
data points (rows) in the plot. A "0" entry in the filter column designates that the
data point is to be excluded while a nonzero entry designates that it is to be included.
Formation of Boolean data to use in filtering has already been described
under Spreadsheet Operations (Section
11.1).
Edit
This allows either individual graphs or the X axis of the plot to be altered
from their initial values (following Create). Selection requires that one of the
graphs on the overall plot or the plot axes be the "active" graphical object (by
clicking on them). Clicking on one of the graphs, followed by selection of
Edit leads to an onscreen dialog.
The name of the quantity being graphed (the Y axis) is provided at the top of
the dialog. Controls are available for setting up various aspects of the overall display.
 Fit
Lists the available display/fit options:
cubic spline
skyline
pointtopoint
linear least squares
quadratic least squares
cubic least squares
Fourier least squares

The first three do not involve data fitting but simply representation of
the data. "Cubic spline" (previously described) provides a smooth line
which passes through all the points, "skyline" presents the data in terms
of histogram, and "pointtopoint" connects the individual data points
by straight lines. The remaining selections involve data fitting in a
leastsquares sense, and the resulting fits are provided on the plot. "Linear",
"quadratic" and "cubic least squares" fit the data to simple linear
(twoparameter), quadratic (threeparameter) and cubic (fourparameter) forms, respectively.
Y = a + bX linear
Y = a + bX + cX2 quadratic
Y = a + bX +cX2 + dX3 cubic
"Fourier least squares" fits to a truncated Fourier series involving
seven independent parameters.
Y = a(1cosX) + b(1cos2X) + c(1cos3X) +
d sinX + e sin2X + f sin3X + g
 Range
Sets the lower (From) and upper
(To) values for the Y axis. The default settings (the minimum and maximum values of the data,
respectively) may be changed, and can be restored by
clicking on Reset.
 Axis Labels
Turns labels for the Y axis "on" and "off".
 Markers
Turns data point markers "on" and "off", Type of marker is
selected from Marker Type below.
 Marker Labels
Turns labels (molecule names) associated with the markers on the
data points "on" and "off".
 Sphere
Turns the gold sphere demarking of the selected molecule "on" and "off".
 Line Width
Selections in the menu control the width of the graph lines. 1 is
thinnest, 5 is thickest.
 Marker Type
Selections in the menu designates the type of marker: square,
circle, diamond, triangle and cross.
Clicking on Done exits the dialog with all edits;
clicking on Cancel exits the dialog but any edits are lost.
Clicking on the plot axes (instead of on an individual graph) prior to
selecting Edit leads to a different dialog.
This allows setting the lower (From) and upper
(To) limits of the X axis. The default settings (the minimum and maximum values of the data,
respectively) may be changed, and can be restored by
clicking on Reset.
Clicking on Done exits the dialog with all edits;
clicking on Cancel exits the dialog but edits are lost.
Selection of Expert (in the top left hand corner) extends the
Edit dialog to address error handling.
Selection of an entry under Error uses the data in the associated column
to assign error bars to the plot.
Error data is also used to construct least squares fits (linear, quadratic,
cubic and Fourier).
Conventional least squares in one dimension involves minimizing the square
of the difference between actual data values, y(xi), and those approximated by
some fitting function, f(xi, cj), where the cj are adjustable parameters.
This assumes equal weighting of the individual data points, that is to say,
that the uncertainties (errors) in the data are uniform. If, on the other hand, the
errors are not uniform, a different expression to that given above may be preferrable.
Here, s(xi) are errors associated with data values
y(xi) (Note: Error values are normalized to the smallest error such that
zero errors will not result in indefinite values). This expression is
used by Spartan's spreadsheet.
Delete
Selection of a plot, followed by selection of
Delete removes the plot.
Section 11.2.5: Help
Selection results in the following menu:

Spreadsheet Overview 
Sheet 
Molecule 
Column 
Plot 
Expert 
Overview
Provides an overview of spreadsheet operations.
Sheet
Describes functions available under the Sheet menu.
Molecule
Describes functions available under the Molecule menu.
Column
Describes functions available under the Column menu.
Plot
Describes functions available under the Plot menu.
Expert
Describes spreadsheet functions available in expert mode.
