Psion Epoc Spreadsheets

Spreadsheet with graphics, already built into every Psion.

The Sheet spreadsheet built into the Psion can be used for many small business and personal calculations. A spreadsheet calculates results from data held in a grid of boxes called cells. Once created, you can vary the data and easily redo the calculations. You can display information in a table, and include the table in a Word document. You can also graph the figures.

The columns of cells are labelled with A, B, etc., up to IV, or 256 columns. The rows of cells are numbered from 1 up to 16384. The active cell you are working on is shown in outline, and you can move around Sheet using the arrow keys. Data is entered in a window at the top left of Sheet, and the cell to be used is listed by a letter and number location. You can refer to cell locations by absolute addresses (like A1) or relative to some other cell.

You can enter formulas using general mathematical, logical, financial, calendar and statistical terms, although really large and complicated spreadsheets are best done using computers with more memory, and a larger display.

You can graph the results of your Sheet, and include the graph in Word documents. Graphs can have the axis visible or not. You can add grid lines, and major and minor tick marks to the graph. These are all in the Format Axis, Other Menu.

Each cell can store numerical (or text or calendar) data, or a formula. You can change the width or height of a column of cells to suit your needs. You can insert new columns or rows from the Insert, Cells Menu. Text is usually aligned left, while numbers are aligned from the right, but you can change the alignment. Text is entered just by typing it, with a ' if it starts with a digit. Numbers are entered by typing them (however if you want to enter currency, don't just use a $ sign in front of a number or it might be mistaken as text). Formulas are entered by starting them with the equals sign (=). Use the Edit, Clear Menu item to change cell information other than its contents.

If you have a column of say 12 figures in column C, you could add them up with a formula like =C1+C2+C3 etc, however it is easier to write =SUM(C1:C12)

If you have done some work on a spreadsheet you should save the sheet, in case your next change causes problems. You can also select a range of cells and protect them from change. This is very helpful if you want someone else to enter data only in some cells.

In a typical project, you would leave the first few rows blank so you can add titles to columns, and would freeze those cells. You can show the current date using `=NOW'. For most business sheets you track costs and quantities, selling prices (as a currency) and stock. Set up columns for the cost of items sold, stock quantities, inventory value (the previous columns multiplied). You can copy rows down the sheet with copy and paste, and the formulas will be updated automatically.

You can use logical functions to show values that are positive and leave blank values that are negative. =IF(cell>=0,two double quotes here,cell) tests the condition cell less than or equal to 0, shows a blank if true, and shows the value of the cell if false. This sort of test makes it easy to spot items that need action. You could also provide a sum of the values of cells that require action.

Your spreadsheet can look a lot fancier, and easier to use, if you add some formatting. Use the View, Freeze Menu item or button to keep titles in view as you scroll.

Sheet does not have an Export facility, however if you need to move Sheet data (not formulas) to another program or another computer, try this trick. Highlight the cells you need, then press Ctrl C to copy them to the clipboard. Open Word with a new file, and use Ctrl V to paste the selected contents into the new file. The contents of each cell are now separated by a Tab. export as a text file in Word. A tab delimited plain text file is a standard file format that many data base applications can import.

Functions

The many functions available in Sheet are listed in detail in the Psion help file. Access from the Insert, Function menu, or the Function button.

Date and Time

Financial

These are broken into four groups

Annuities and Loan

Fv(interest,term,payment) - matured value of investment

Pv(interest,term,payment) - present value of investment

term(payments,interest,future_value) - number of payments needed

Pmt(interest,term,principal) - repayment per period

Cash Flow (of a range starting with initial cost, and returns)

Irr(guess, cash_flows) - Internal Rate of Return

Npv(interest, cash_flows) - Net Present Value

Depreciation

Sln(cost,salvage,life) - Depreciation per period over life

Syd(cost,salvage,life,period) - Depreciation per period assumes greater at start

Ddb(cost,salvage,life,period) - as above, but different formula

Lump Sum Investments

Cterm(interest,future_value,principal) - periods to reach a value

Rate(future_value,principal,periods) - rate to reach a future value over a period

Information

Cell(information,range) - returns specific information about first cell in range

ErrorType(x) - what sort of error

Isblank(x) - is cell empty

Iserr(x) - is there an error (except n/a) in the cell

Isna(x) - is the cell #n/a

Iserror(x) - is there an error in the cell

Islogical(x) - is it true or false

Isnontext(x) - is it not text

Isnumber(x) - is it a number

Istext(x) - is it text

N(x) - number in cell or False

Type(x) - what sort of contents are in cell, as a code

T(x) - text in cell or empty string

Logical

False - (0)

True - (1)

If(x, then, else) - values, expressions or cell references

And - both conditions must be met

Not - negate the conditions of an IF test

Or - only one condition need be met

Lookup

Address(row,column) - returns the cell reference as a string

Columns(x) - returns column number

Hlookup(x,range,offset) - finds x in range, offsets down, returns contents

Vlookup(x,range,offset) - as hlookup, but by row, then column

Index(range,column,row) - contents of cell in range

Indirect(x) - contents of cell pointed to by x

Lookup(x,range1,range2) - find x in range1, returns value of same cell in range2

Offset(reference,rows,columns) - offsets reference

Row(x) - row number of cell or first of range

rows(range) - number of rows in a range

Mathematical

Abs(value) - absolute

Acos(value) - arcCosine

Asin(value) - arcSine

Atan(value) - arcTangent

Atan2(x-value,y_value) - angle in radians whose tangent is y/x

Cos(value) - cosine

Degrees(value) - convert from radians to degrees

Exp(value) - e raised to number

Fact(value) - factorial of number

Int(value) - integer next lowest whole number

Ln(value) - natural logarithm of number

Log10(value) - base 10 logarithm of number

Mod(value1,value2) - remainder or modulus of value1/value2

Pi(value) - value of pi

Radians(value) - degrees converted to radians

Rand - random number between 0 and 1 (+Rand for unchanged result)

Round(number,number_of_digits) - round number to digits (use - for left of decimal point)

Sign(value) - Sign of number, 1 if positive, -1 if negative

Sin(value) - sine

Sqrt(value) - square root

Sumproduct(range1,range2) - sum the products of each item in two ranges

Tan(value) - tangent

Trunc(value) - number without fraction (differs from Int on negative numbers)

Statistical

Combin(n1,n2) - combinations

Permut(n1,n2) - permutations

Average(list) - average of the list of numbers

Choose(x,list) - xth item of list

Count(list) - how many cells contain numbers

Counta(list) - how many cells contain any information

Countblank(list) - how many cells have blank text

Max(list) - largest value in list

Min(list) - smallest value in list

Product(list) - product of list

Stdevp(list) - standard deviation of a population

Stdev(list) - standard deviation of a sample

Sum(list) - sum a list of values

Sumsq(list) - sum of squares of list

Varp(list) - variance of a population

Var(list) - variance of a list of samples

Text

& - Concatonate text

Char(x) - character indicated by code x

Code(string) - character code of first character in string

Exact(string1,string2) - compares strings and returns true or false

Find(searchstring, string, start_position) - returns position

Left(string,n) - first n letters of string

Len(string) - number of characters in string

Lower(string) - converts string to lower case

Mid(string,start_position,n) - middle n characters from start_position

Proper(string) - first letter of each word in upper case

Replace(string,start_position,n,newstring) - removes n letters, newstring replaces

Rept(string,n) - repeats string n times

Right(string,n) - last n letters of string

String(x,n) numnber x as a string with n decimal places

Trim(string) - removes leading and trailing spaces, multiple spaces

Upper(string) - converts string to upper case

Value(string) - converts string to a number

Date Inconsistent

If you have a Date cell set to say ddmmyy, and entering the date dd/mm/yy gives incorrect results, then you probably have a conflict with your international date settings in the control panel. The local Sheet date does not override, as it perhaps should, and you have to use the international setting. Solution. Keep the two consistent.

Steve Waddicor of Symbian says

1) Dates and times are recognised for input anywhere in the machine as specified in the international settings in the control panel.

2) Formatting of a cell in sheet affects how a cell is displayed, it doesn't affect the interpretation of input in any way.

3) Dates in Sheet are simple floating point numbers with a Date format applied. The whole number part represents the date, and the fractional part, the time. In other words 1.0 = 24 hours. Day zero happens to be 30/12/1899

4) Sheet will have a stab at interpreting input as a date or a time first, before it considers it as a number or text.

Here's a helpful suggestion to prevent confusion. The help file suggests you enter the date in the format 30 1 88. i.e. use spaces. If you enter a sequence that doesn't represent a valid date, it'll go in as text, and be quite obvious.

Date Now

If you want to include the current date and time in a spreadsheet, you have probably experimented with NOW. This is however evaluated whenever the spreadsheet is recalculated to provide the current date and time, which is not always what you want. If you just want to enter the date and time when the entry is made, use 0+NOW or even just +NOW Don't forget to format the cell for an appropriate date or time format.

Scattergraphs

If you start with two columns of figures, and want column 1 on the X axis, column 2 on the Y axis. Select the first column. Switch to Graph and define a new graph. Back to Sheet and select the second column. In Menu, Ranges, Add Selected Ranges. Tap on the graph to get Change Range Dialog. Remove the Displacement range. Set up formatting and labels and the rest. Sander van der Wal gave that suggestion.

Sorting

Sheet (up to ER3) mysteriously lacks a sort function.

Sibo says: I used to put the columns/rows into the Data application and sort them from there. Highlight the cells you want to sort and copy them. Paste into a text editor and save/export as plain text. You can then import into Data with Tabs as label separators and sort. Then export again and open/import into the text editor before finally pasting back into Sheet!

Term deposits or certificates of deposit

These pay a fixed interest rate for a fixed term.

At a first guess you need to record the date of the deposit, the amount, the interest rate, when interest is paid, when the deposit becomes due for renewal, the amount of interest due at each payment. You would probably total the amount of deposits, and also sum the annual interest for tax purposes.

Other uses

use Sheet for tracking expenses, such as petty cash, car expenses, and so on. Make categories to suit your tax accounting.

Test Input Values

You can force a True False evaluation of whether a numerical entry is within various value limits by using a formula like A4=(A3>A1)AND(A3<A2), where A1 contains the minimum value, A2 the maximum value, and A3 the value being tested. I actually thought this was built in, but couldn't find it when I wanted it. You could obviously use this principle for lots of tests.

Time Calculations

Sheet formats the time field as a time format, although it contains a number of decimal days. Sheet ignores the whole days before the decimal point. So A2=TRUNC(A1) would give the whole days. A3=TRUNC(A1*24) would give hours. A4=TRUNC((A1*24-A2)*60) would give minutes. A5=TRUNC((A1*24-A2)*60-A3)*60 would give seconds. Or do it all in one field =TRUNC(A1*24)&":"&TRUNC(MOD(A1*24*60,60))&":"&MOD(A1*24*60*60,60)

Tracking Shares or superannuation

We can put company names in C3, D3, E3, F3 etc. C7 to F7 etc can contain the number of shares, and C9 to F9 etc down to wherever can contain weekly or monthly share prices, formatted as currency. B9 and down can be formatted as dates. C6 to F6 etc, and C8 to F8 etc can be formatted as currency. You are going to display the current values in C8 to F8 etc, and multiply the number of shares by the most recent share price (which will originally be in an ever increasing cell number), and show the result in C6 to F6 etc. The formula for this is easy, just C6=C7*C8 (and the same for D6, E6 etc).

Getting the latest prices into C8 to F8 is more of a problem. One method uses column A9 down to store a sequence of numbers. Start with 1 in A9. For A10 use =IF(B10,A9+1,") which means A10 only increments if something exists in B10 (you can't use =A9+1 because that doesn't indicate the most recent price). You replicate the formula down the A column to the end (say A100). Then A8 can calculate the highest number reached using =MAX(A9:A100)

To extract the value for C8 (and D8 etc) you use a formula like =LOOKUP($A8,$A9:$A100,C9:C100) We use absolute cell numbers here, to look up the highest number reached in A9 to A100, then take the matching cell in C9 to C100. C8 then has the current share value. You can copy the cell C8 to D8, E8 and so on for the other shares.

Time Tracking

From: c.byatt at virgin.net

Subject: software use - Sheet for clocking on/off

I use Sheet very often - I love to be able to keep records at the time of things, having set the sheet up so that I get summary information built up from the cumulative data.

An example - a while back I wondered how many hours per day I was working on average. I therefore set up a sheet with days of the week in order (Mon-Sun) continuously down the first column, "=TODAY" in the cells of the 2nd col, and "=NOW" down the cells in the 3rd and 4th cols. I then wrote a macro which forces a recalculation (Shift+Ctrl+k) to make the NOW or TODAY value current, copies the cell's content (Ctrl+c), and then calls the Edit/Paste/Paste Special menu which pastes the cell's current value (not the formula) back into the cell.

When I arrive at work, I select the cells of the second and third cols by the relevant day of the week (today's was Tue) and then run the macro (which I called pasteval). This then copies the current value for the day and time back into into the highlighted cells. When I am leaving work, I simply select the relevant cell in the 4th col, run pasteval, and my departure time appears.

That's the data collection. For collation, I have used an =IF function to set the 5th cell to be blank if 3rd & 4th are equal, or 4-3 if they are not - that gives the number of hours I've worked that day (actually, it's a bit more complicated than just "cell 4-cell 3" because the time is stored in each cell as a decimal part of 1 day so the result has to be converted to hours [as integers plus decimal fractions] but I'll gloss over that bit unless anyone asks...).

The final bit is the mean number of days worked per weekday and per weekend day (my pattern of work is different at weekends, so I wanted to keep this information separately). I set up cells 6 and 7 of each row to show either a blank or respectively the value of cell 5 if today was a weekday or cell 7 if today was a weekend day [relevant IF function formula available on request!].

In the top row (always visible using Menu/View/Freeze panes menu option) I then have the mean values visible for cols 6 (weekdays) and 7 (weekends) so I can see the current figures, automatically updated after each new entry.

Keyboard Shortcuts

Ctrl+arrow to move page at a time
Ctrl+Tab to select a row
Ctrl+Space to select a column
Fn+left arrow to column A
Fn+right arrow to righthand column
Ctrl+Fn+left arrow to cell A1
Ctrl+Fn+right arrow to bottom right of sheet
Ctrl+Fn+up arrow to row one
Ctrl+Fn+down arrow to last row
Use Edit, Find, GoTo to selected cell
Use Edit, Find to locate contents of a cell

Bugs

Bug with large Sheet objects embedded. If the program is exited before recalculation is complete, the sheet can be corrupted. The fix is to set large sheets to manual recalculation.

Steven Pemberton notes the a sort order bug in Revo (probably others). Characters are sorted in a different order to their 'ascii' values, so if you sort a column of characters, or text, it is not necessarily true that for instance cell a20 &lt; a21. The operators '>' and '<' work in a different way than sorting works.

Series 5 (classic) bug when a cell is deleted via rows or columns if the cell has a range name. The range name list is corrupted, and so does the object.

Chris Malcolm says "I have been doing things with my 5mx spreadsheets which have caused previously quick recalculations (a few seconds) to suddenly take much longer (a few minutes). I just discovered what one of these things was. I had added a column D which contained "=Cn-Bn" where B and C were on the same line. I wondered if it could possibly be the ordering confusing a simple minded check and changed it to "=-(Bn-Cn}", thus mentioning the fields in the order of their occurrence left to right and top to bottom. Suddenly the long recalculations became quick again!

"But this keeping calculations always from previous fields and always mentioning them in the proper order is not the only secret trick to keeping recalculations fast, becuase I have some that have slipped into the recalculation bog without breaking these rules."

"Does anyone have a complete list of these rules?"

Alan Smith adds "It's worth trying a copy of the data into a new spreadsheet - that can help too. It has been known to speed up file opening as well.