home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
ARM Club 3
/
TheARMClub_PDCD3.iso
/
hensa
/
spreadsheets
/
a180_1
/
!SSS
/
!Help
< prev
next >
Wrap
Text File
|
1991-07-30
|
15KB
|
310 lines
SSS - A desktop Spreadsheet
VERSION 0.1
INTRODUCTION
SSS is a simple spreadsheet for the Archimedes.
It can manipulate a grid of cells containing strings or formulae.
It can load or save these grids in its own format or as text.
It can produce draw file graphics from the grids.
The name SSS was originally intended to stand for statistical
spread sheet, however the statistical functions have not yet been
implemented. The name could stand for simple spreadsheet or slow spreadsheet!
SSS is started from the RISC-OS desktop in the usual way by double
clicking on its icon. It installs on the iconbar, a blank sheet can be opened
by clicking on this icon.
SSS needs to have seen the !System directory, and to find version 3.5 or
later of the shared C library, and the floating point emulator in
!System.modules .
SSS is controlled by two menus, one one the iconbar and one on the
spreadsheet window, and by a edit window and some key presses.
The key presses only operate when the caret is in the edit window.
Menu options are denoted as follows in this file:-
<Window->Graph->Bars> means the bars option from the save option
of the window menu.
CELLS and BOXES
The rows and columns of the sheet are numbered starting from 0 at the
top left. A cell is denoted [x,y] where x is the column number and y the
row number. So [0,0] is the top left cell.
A rectangular range of cells (box) is denoted by its top left and bottom
right cells. The initial sheet has 20 rows and columns, so a box containing
the whole sheet is denoted [0,0][19,19].
Regions of the spreadsheet are denoted by two boxes.
The source box is displayed as an unfilled box surrounded by a thick
border.
The target box is displayed as a filled box.
In general the target box is used to denote a region to be changed
by an operation, and the source box gives values to be read.
The source box can be altered by clicking select on the sheet. It can
be resized by clicking alter.
The source box can be set to the full width of the sheet by presssing F8,
and to the full height by F9.
The home key sets the source box to [0,0] and <shift> home sets it to the
bottom right cell of the sheet. These both may scroll the sheet.
The target box is set equal to the source box by selecting <Window->Edit>
or clicking on the Edit icon in the edit window or pressing F2.
A single cell box will be refered to as a cell, and a box of width or
height one as a row or column.
EDITING A SHEET
When a spreadsheet is opened a second window, the edit window is also
opened. If closed it can be reopened by selecting <Window->Edit>.
When the edit window is opened the target box is made equal to the source
box. The coordinates of this box are set in the edit window title bar and
the top left entry of the box is read into the edit window.
The entry in the edit window can be edited in the usual manner. Clicking
on the OK icon or pressing F1 or <return> enters it into all the cells
of the target box.
After clicking OK the target box moves on one cell. The direction of
movement is determined by the arrow icons in the edit window. The box does
not move if it is more than one cell wide in the direction of motion.
The next icon (or F4) moves the box without altering the cells.
The cancel box (or F5) restores the original entry to the edit window
( before OK is selected!).
The entries in the cells can be either strings, that are displayed verbatim,
or formulae (including numbers) that are evaluated before displaying.
These are selected by clicking on one of the top row of icons in the edit
window before clicking OK.
Strings can be displayed in two formats
String - truncated to the column width -
or Long - not truncated *** not yet implemented **
Formulae can be displayed in four formats
Int -whole numbers 2dp 4dp or Exp - exponential format.
A blank cell is initially given the format of the cell above, or INT
if this is blank or does not exist.
To change the display format of all the formulae in the target box without
changing the values -select the required format and click on Format.
FORMULAE
Formulae can be constructed using the following terms
arithmetical operations + - * /
powers ^ e.g. 3^2 = 9
brackets ( )
numbers e.g 1, 57, 0.371
special numbers pi,e
x - column number
y - row number
cells [,] e.g. [3,5] , [x-7,x+2*y]
functions sin,cos,tan,exp,log,sqrt,sq,abs
ceil,floor,round e.g floor(3.6)=3
asin,acos,atan
atan2 atan2(x,y)= angle between (x,y),(0,0),(1,0)
sinh,cosh,tanh
range functions sum,count,mean,ssd,psd,max,min
e.g. ssd[0,0][3,5] = the sample standard
deviation of any formulae in the 24 cells in
the range.
psd = population standard deviation.
The expressions in a range or in cell parameter are restricted to
combinations of +-*/ () x,y and whole numbers. e.g. [x+3.5,y] or [x,[x,1]]
are not allowed.
A formula that is incorrectly formed, such as 3*(x+1 (missing bracket)
is displayed as a parse error ?P???????? .
A formula that cannot be evaluated such as sqrt(-1) or [0,0] if cell [0,0]
contains a string or error is displayed as an arithmetical error ?A???????
When entering formulae the source box can be entered by clicking on the
Relative or Absolute icons. If the source box is a cell that cell is entered.
Otherwise a range is entered. The Absolute icon just gives the coordinates
as numbers. The Relative gives them relative to the top left hand cell of
the target box.
E.g. if the source box is [0,0] and the edit box is the column [1,0][1,5]
Absolute will set each entry of the column to [0,0] giving the same value
to each. Relative will set each entry to [x-1,y] giving each entry in the
edit box the same value as the cell to its left.
COLUMN WIDTHS
These are set using the <Window->Width> options which sets the display
widths of the whole of all the columns in the source box. Display items
will be truncated to fit these columns.
The Fit option is not yet implemented.
Column widths of zero can be used to indicate that certain operations do
not apply to some columns in a box. In particular they are not affected
by edit operations.
The home and <shift> home keys or the Next icon may be needed to select
a column of width zero to increase its width.
LOADING AND SAVING
The <Window->Save> menu leads to three options.
<Window->Save->Sheet> saves the whole sheet in a form suitable for
reloading into SSS. Sheets are saved as file type 0x0E3 SSSheet.
The file includes the column widths and the size of the sheet.
<Window->Save->Part> saves the source box as a SSSheet file.
Columns of width zero are not saved.
<Window->Save->Text> saves the source box as a text file.
The format of this file is determined by items in the <Iconbar->PrSetup>
menu. The column width determines if the sheet is split to fit on a page.
The separator is up to 3 symbols (including spaces) that can be printed
between columns. If the repeat first option is selected and the sheet is
split to fit on the page then the first column will be repeated at the
start of each part.
Columns of width zero are not saved.
This file can be printed by dragging to a printer, or by pressing the Print
key.
Sheets can be loaded by double clicking on a SSSheet file, or by draging a
SSSheet or Text file to the iconbar icon or the sheet window.
SSS can only deal with 1 file at a time, loading a new sheet by double
clicking or dragging to the iconbar will lose any sheet loaded at the time!!!
Draging to the sheet window merges the new sheet with the current one.
The new sheet is merged with its top left corner at the top left corner of
the target box. If it is too big to fit in the current sheet it is truncated.
Text files cannot be merged. Column widths are increased if necessary.
When loading text files entries are split at spaces, commas or tabs. A new
column is started for a newline. A guess is made as to the correct format
for the entry.
UPDATING
Entries that depend on other cells can be updated by selecting
<Window->Update> . If <Window->Auto> is selected updating takes place after
any editing operation repeatedly until no changes are made.
RESIZING THE SHEET
<Window->Resize> contains five options
New Col - Insert a new column to the left of the target box.
New Row - Insert a new row above the target box.
Delete Cols - Delete the columns in the target box.
Delete Rows - Delete the rows in the target box.
Extend - Add rows and/or cols at the right and bottom.
A maximum of fifty columns are allowed. The maximum number or rows is only
determined by the avaliable memory.
MOVING THE ENTRIES
<Window->Copy> copies entries from the source box to the target box.
If the target box is a cell the source box is copied preserving its shape
with the target box as top left corner, otherwise the source box is copied
into the target box one entry at a time going across rows and then down to
the next row. This allows copying from a row to a column or vv.
Sorting of entries can be done either on a column or a row. The indexing
entries are given by the source box, which must be either a row or a column.
The entries to be moved are given by the target box. This would normally
include the source box but may not. If the source box is a row it must have
the same horizontal extent as the target box. If it is a column it must have
the same vertical extent. The source box must be either all strings, when
the sorting is into ascii order, or all numerical when the sorting is into
increasing order.
GRAPHICAL OUTPUT
Grapical output is in the form of Draw files. The pagesize should be set from
the <Iconbar->PrSetup> menu. The options are
A4p - A4 portrait
A4l - A4 landscape
A5l - A5 landscape
Read- Read pagesize from a printer driver.
<Iconbar->PrSetup> also includes an option for monochrome output.
The graphics options all take data as columns. y values come from the target
box and x values from the source box.Columns of width zero are omitted.
The graphics options all use the Trinity.Medium font, which shold be
avaliable. The point size is chosen using the width of the first column of
the Edit box to fit the avaliable space. Reducing this width will increase
the point size.
<Window->Graph->Bars> produces a bar chart. The data is taken from columns of
the target box. Columns of width zero are not displayed.
If the source box is a column of the same vertical extent as the target box
it is used as labels for the x-axis.
<Window->Graph->Line> produces a line graph. The source box must be a column
of the same vertical extent as the target box, and must consist of valid
numerical data. The source box data should be ordered.
HINTS AND EXAMPLES
To clear a sheet press F8 and F9 to select the whole sheet,
press F2 to edit, <shift> Copy to clear the line and F1 for OK.
Clearing a cell releases memory used by the cell, and forgets the format.
Clearing a large sheet is very slow. If a big sheet is loaded and
you want to load a new one it will be much quicker to quit and start again.
Care should be taken using absolute cell references. If part of the
sheet is saved, or cells added or removed the numbering will change.
If cell and row numbers are required in the margins press home and F8
to select the first row, press F2 to Edit and put x in the first row.
Then use home and F9 and put y in the first column.
An example of a graph of two functions.
First double click on the !Fonts directory to ensure Trinity.Medium is
avaliable.
Start with a blank sheet. Put y in the first column as above.
Put sin(y/3) in the second column and 2+cos(y/3) in the third.Set the
formats of these columns to 2dp so the results are visible.Select the
second two columns e.g. click select on [1,0],alter on [2,0] press F9.
Click Edit.Set the width of these columns to 4 to set the font size used.
Select the first column e.g. Home F9. Drag a Draw file from
<Window->Graph->Line> to a suitable directory.
Double click on this saved file to see the result. You can also drag these
files directly into Draw.
The example sheet Class provided gives the continuous assesment and
exam marks for a class of students. The overall mark is calculated using
a weighting of 40:60 CA:Exam.
The right end of the sheet does a regression calculation to calculate
a straight line estimate for the Exam percentage as a function of the CA
percentage. The estimated exam marks are given for each student.
Note:-The CA total is given by sum[1,y][x-2,y] the range references are mixed
absolute and relative. This means it is possible to add a new CA column
without having to alter the formulae. The same holds for the other column
formulae. Similarly you can add a new row for another student. Unfortunately
The formulae in the prediction column depend on the calculated slope and
intercept. The y position of these cannot be specified in a way that allows
adding a new student, and so the formulae must be altered by hand.
Exercises i) Set Auto updating and alter the CA3 mark for B.Smith to 15.
ii) Add a new CA column. Insert a column. Copy a CA column into
it and adjust the marks.
iii) Add a new student. Correct the formulae in the Prediction
column.
iv) Sort the students according to overall mark.(Only move the
names and marks,not formulae!)
v) Get a scatter diagram of exam % and predicted marks against
CA%.(Dont plot the overall mark!)
vi) Plot a line graph as in v) (Sort suitably).
TO DO
There are a vast number of improvements or additions that could
be considered. They will probably have to wait until either I decide
that I can't do without them or there is a concerted clamour from other
users. Some possibilities include
Implement the LONG string option
Implement the FIT width option
Checks to prevent accidental overwriting or deleting of the sheet
Checks on validity of input files
Checks on the validity of draw file output (too many missing values
could produce invalid output files)
Statistical functions (Regression, Chisquared ,Anova?)
Random numbers from statistical distributions
Speed up clearing
Speed up updating
Speed up redraw
COMMENTS PLEASE
I would welcome bug reports, criticism (constructive or otherwise),
modifications or suggestions for modifications. Further versions are
not likely to appear unless I obtain sufficient feedback from users.
Chris Stretch
Archive and Arcade BBS No.62
CBTP13@CBS%UK.AC.ULSTER.UCVAX