Psion Epoc Spreadsheets

The best miniature spreadsheet computers on the planet

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.


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
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
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
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
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
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
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)
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
& - 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.


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.


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

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


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.

This site will look much better in a browser that supports W3C web standards but it is accessible to any browser or internet device, including Psion Web and similar PDA or limited browsers. Netscape 4.x users - turn Style Sheets off. Your style sheet support is too broken to use (sorry). -> epoc -> applications -> sheet

Contact Webmaster