Author: Clive Whitear
Date: February 18th 1999
Version: ER5 release
Sheet
0x10000088
Sheet: Quick start
basics graphs charts spreadsheets worksheets transfer
Sheet is the EPOC machine's spreadsheet. The files created in it are compatible with PC spreadsheet applications. If a PC has EPOC CONNECT installed Sheet files can be copied to the PC and you can work on them there.
Use Sheet to:
•	Display information in a table.
•	Perform calculations based on sets of numbers, or that use formulae and functions.
•	Create graphs from sets of numbers.
Sheet has two views.
•	Sheet: for spreadsheet work, and for creating the ranges of information used to draw graphs.
•	Graph: for creating and printing graphs.
To switch between the two views: tap the appropriate button on the Toolbar, or select Sheet from the Switch view cascade or Graph from the Switch view cascade on the View menu.
In Sheet you can:
•	Format cells easily using the Top toolbar.
•	Freeze panes to keep some areas on screen while you scroll others.
•	Use "named" cells and ranges in calculations.
To enter information in a cell, tap on it and type the information.
To move around the worksheet:
•	Tap the cell you want to move to. Use the scroll bars or the arrow keys to move to other areas of the worksheet. Combine the arrow keys with the Ctrl key to move a page at a time.
•	Use Go to from the Find cascade on the Edit menu to jump to a specific cell.
•	Use the Find commands on the Edit menu to move to cells if you know their content.
You can insert spreadsheets as "objects" within other EPOC programs, but you cannot insert an object in a spreadsheet.
Cell references (Sheet)
Spreadsheet mixed references absolute relative go to
The worksheet is a grid of cells divided into horizontal rows and vertical columns. A reference, e.g. B4, consists of the letter of the column that the cell is in, followed by the number of its row.
Use cell references when:
•	Creating formulae for calculations.
•	Moving to cells using Go to from the Find cascade on the Edit menu.
There are two types of cell reference that can be used in formulae:
•	Relative references, e.g. A3, which update automatically when the formula is moved or copied. E.g. =SUM(A5:A8) in A9 becomes =SUM(D6:D9) when copied to D10.
•	Absolute references, e.g. $A$3, with a $ sign before any part of a reference that you do not want to change when you copy or move the formula elsewhere. E.g. =SUM($A$3:$A$8) in A9 remains as it is when copied to D10.
•	Relative and absolute references can be combined. E.g. =SUM(A$5:A$8) in A9 becomes =SUM(D$5:D$8) when copied to D10.
Entering information (Sheet)
active cell # hashes clear formula square gate hatch pound octothorpe
Highlight the cell and enter the information to be stored there. This information will be displayed in the input line above the worksheet. Move to another cell or press Enter to store the information.
•	A single selected cell will have a thicker border than other cells.
The first character typed in tells Sheet what type of information the cell will contain.
•	For numbers: make the first character a number or one of the following: + - . (
•	For text: type it in. If the text starts with a number, type a ' first, e.g. '16 Queen Street. To make your text begin with a ' type two of them.
•	Start formulae with an equals sign, as in '=A1+B2'.
•	# marks appear in the cell if a number is too long for the column width, though the numerical information is still stored. To change the column width, use Column width on the Format menu.
To replace a selected cell's contents: type the new information and press Enter.
To change its contents: press Enter, then edit the contents on the input line.
To remove certain aspects of a selected cell's information: use Clear on the Edit menu and choose whether you want to delete the formatting, contents or all the information associated with a cell.
To delete a selected cell's contents: press the Del key.
Selecting cells (Sheet)
selecting range painting nonadjacent highlighting non-contiguous
Highlight cells in order to change their formatting, create a range for a graph or formula, cut or copy their information, or paste information into them.
To highlight a range of cells:
•	Tap on the first cell and drag the highlight to cover the desired area, or highlight the first cell in the range and hold down the Shift key while you use the arrow keys to extend the highlight.
•	To highlight a whole column or row: tap the column or row heading.
•	To highlight the whole worksheet: tap the top left-hand box (to the left of the column headings and above the row headings).
•	To highlight unconnected areas: hold down the Ctrl key and tap in turn on each area you want to highlight.
Formatting cells (Sheet)
overrun typeface font emphasis wrapping bold italics underline wrap text to cell alignment
To change the way information is displayed on the worksheet, select the cells, then:
To change the format:
•	Use the Top toolbar buttons to change the font and size of characters, or to make the text bold, italic or underlined, or
•	Select Font on the Format menu and select the formatting options you require.
To change the alignment:
•	Use the Top toolbar button to choose from Left aligned, Centre aligned, Right aligned text and Standard alignment (numbers on the right and text on the left), or
•	Select Alignment on the Format menu. Use this dialog to set the vertical alignment of information in a cell.
•	To make information stay within cell boundaries, tick Wrap text to cell.
Adding borders & shading to cells (Sheet)
borders shading background colours colors outlines grey gray white lines
To add borders and shading to cells, select them, then:
•	Tap the Border button on the Top toolbar, or select Border on the Format menu, and choose the type of border required.
•	Tap the Shading button on the Top toolbar, or select Shading on the Format menu, and choose the type of shading required.
Column and Row size (Sheet)
column width row height wider taller narrower shorter
To change the width of a column or the height of a row:
•	Drag the right-hand edge of the column heading, or the lower edge of the row label.
•	You can change multiple columns or rows, by highlighting them first (tap in the middle of the column or row title and drag across the adjacent columns or rows). If a column is hidden, tap to the right of the line to drag it into view again (you'll see an arrow on one side of the line only); if a row is hidden, tap under the line. To change a column to the left of a hidden column, tap to the left of the line and drag it (you'll see two arrows); to change a row above a hidden row, tap above the line.
To set the column width as a number of characters, use Column width on the Format menu. To apply the new setting to a range of columns, specify the range in the For columns line of the dialog.
To set the row height in points (there are 72 points in an inch), use Row height on the Format menu. To apply the new setting to a range of rows, specify the range in the dialog.
Inserting cells, rows & columns (Sheet)
insert cells insert columns insert rows
To insert a single cell: select Cells on the Insert menu, and choose the direction in which you want the surrounding cells to move in order to make way for the insertion.
To insert rows:
1.	Position the cursor in the row you want to be immediately BELOW the new row(s).
2.	Select Rows on the Insert menu and enter the number of rows required.
To insert columns:
1.	Position the cursor in the column you want to be immediately to the RIGHT of the new column(s).
2.	Select Columns on the Insert menu and enter the number of columns required.
Protecting cells (Sheet)
unprotecting freeze freezing frozen protection lock worksheet unprotected cells
The worksheet can be locked to prevent any changes being made to the information it contains. You can also remove the protection from certain cells before locking the worksheet; the information in the unprotected cells can then be changed while leaving the remainder protected.
Locking the worksheet is done in two stages:
1.	Remove the protection from any cells you want to be able to change, by highlighting the cells, selecting Protection on the Tools menu and removing the tick from Protect cell(s).
2.	Lock the worksheet, by selecting Protection on the Tools menu and ticking Lock worksheet.
This means that cells containing critical information like formulae can be protected before, e.g., giving the spreadsheet to a workgroup.
A padlock symbol appears at the right end of the input line when the cursor is on a protected cell and the worksheet is locked.
Formulae (Sheet)
autosum button sigma formulae Formulas ranges range painting circular references arrows
Use formulae to perform the same calculation on different sets of numbers, e.g. when working out the sales tax on a number of differently priced products.
To use a formula:
1.	Put the components of the calculation, e.g. price and tax rate, in separate cells and note their cell references, e.g. A1 and B1.
2.	In another cell, type = followed by the calculation to be performed using the cell references, e.g. =A1*B1. If the cells used in the formula have been “named”, you can use these names, e.g. =Price*Tax.
•	Some functions can use ranges instead of normal cell references, e.g. AVERAGE(A1:A4).
3.	Press Enter to see the result in the cell that contains the formula.
•	If you place the cursor at the end of a row or column of numbers and press the SUM button ARCHIVE=SHEET NAME=sum-button on the Top toolbar, Sheet inserts the formula for calculating the total of those numbers in the current cell.
You can now perform "what if" calculations by changing the numbers in the cells which the formula refers to. The result will update automatically.
When entering a range, you can highlight the cells on the worksheet and Sheet will automatically insert the range in the formula. This is called "range painting".
•	A cell which refers to itself in a formula, directly or indirectly, causes a circular reference, indicated by a circular arrows sign ARCHIVE=SHEET NAME=circularreference in the input line. Results of calculations that involve circular references may be incorrect.
Functions (Sheet)
Formulas insert functions f(x) fx
Sheet has a large number of functions available for performing operations on the worksheet. They are divided into the following types:
•	Financial: for calculations that involve, e.g., interest or loan repayments.
•	Date & Time: for calculations that involve time.
•	Mathematical: for calculations such as square roots and logarithms.
•	Statistical: for calculations such as averages and sums of sets of numbers.
•	Lookup: for locating information within ranges.
•	Text: for performing actions on text contained in cells.
•	Logical: for logical testing of information on the worksheet.
•	Information: for returning information about cells on the worksheet.
Each group has a help topic showing the list of functions and how to use them.
To use a function, either type it in or:
1.	Tap the Function button, or select Function on the Insert menu.
2.	Select the function you require and add your variables to the expression on the input line.
Operators (Sheet)
plus minus divided by adding subtracting multiplying dividing comparisons logical operators arithmetic equals
Use the following operators in calculations and formulae:
Arithmetic operators:
+, -, *, / for addition, subtraction, multiplication and division.
^	for raising to a power (e.g. squaring or cubing)
-	unary minus (to make a number negative)
Comparison operators:
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
=	equal to
<>	not equal to
Logical operators:
NOT:	for negating conditions in IF tests
AND:	for linking conditions in IF tests
OR:	for specifying alternative conditions in IF tests.
Operator precedence (Sheet)
parentheses priority parenthesis brackets
Operator precedence is the order in which Sheet solves calculations; this order is important as it affects the results. Sheet evaluates sums according to the following order:
1.	( ) information in brackets
2.	^ powers
3.	* and / (equal importance), solved left to right.
4.	+ and - (equal importance), solved left to right.
5.	All comparison operators.
6.	NOT.
7.	AND and OR (equal importance), solved left to right.
•	When inserting brackets in a formula, do not use implicit multiplication such as 5(4+5). Use the * operator instead.
Recalculation (Sheet)
preferences auto recalculation plus minus positive +?- polarity recalculate now
Sheet updates answers to calculations whenever one of their components is changed. You might want to disable this feature if you are updating a lot of figures and don't want the worksheet to be continually recalculating.
•	To turn auto recalculation off, use Preferences on the Tools menu and remove the tick from Auto recalculation. 
•	A +?- ARCHIVE=SHEET NAME=recalculation sign appears at the right end of the input line if recalculation is needed. Use Recalculate now on the Tools menu to recalculate. You can continue using other Sheet features while it is recalculating.
##add freeze panes button to next topic##
Adding titles (Sheet)
Fix freezing panes add titles Header headings locking freeze panes frozen
You can create "titles" for columns and rows by "freezing" panes (horizontal or vertical areas of cells) which will stay in place when you scroll the worksheet. To do this:
1.	Enter text in the cells which are to be the column and row titles (for example, row 1 and column A).
2.	Move to the cell immediately below the row you want to freeze and to the right of the column you want to freeze (in the above example this is B2).
•	If the cursor is in cell A1 panes cannot be frozen.
3.	Tap the Freeze panes button ARCHIVE=SHEET NAME=freezepanesbutton on the Top toolbar, or use Freeze panes on the View menu.
•	A line will separate the frozen panes from the scrolling area.
•	Any cells which are above a frozen row or to the left of a frozen column, will not scroll when you move around the screen.
Ranges (Sheet)
naming areas insert names named areas
A "range" is a reference to a block of cells and consists of the cell references of the top left and bottom right corners of the block, separated by a colon, e.g. A3:D8.
Use ranges to refer to areas of the worksheet when creating:
•	Formulae for calculations, e.g. =SUM(A3:A8)
•	Graphs from numbers stored on the worksheet.
To create a range: highlight the cells to include in the range.
To name a range or cell:
•	Highlight the range or cell, then use Add name from the Names cascade on the Insert menu to add a name, e.g. 'Outgoings'. You can use this name in sums and formulae, e.g. =Sum(Outgoings). You can edit the range name using Edit name on the Names cascade on the Insert menu.
•	To help keep track of ranges, put labels in adjacent cells, or use the borders and shading options.
Graphs (Sheet)
Charts create new graphs rename graph multiple graphs
Graphs are based on ranges of cells on the worksheet.
To create a graph: highlight a range of cells and tap the Graph button on the Toolbar. This will create a graph called Graph 1. To rename this, use the command on the Graphs menu.
To create further graphs: highlight another range in the Sheet view and select Create new graph on the Graphs menu in the Graph view. To move between graphs, tap on the furthest left button on the Top toolbar.
While viewing a graph, use the menu commands and Toolbar buttons in the Graph view to:
•	Add more ranges.
•	Add labels, titles and legends.
•	Change the graph display from 2D to 3D.
•	Print the graph.
Replacing graph ranges (Sheet)
replace ranges Charts change graphs
When you create a graph, it is "locked" to the range you specified. The graph will reflect changes to the data within that range, but will not reflect any other ranges highlighted on the worksheet.
To replace the current graph range with a new one:
1.	In the Sheet view, highlight the new range to be portrayed in the graph.
2.	Switch to the Graph view and press the Set ranges button on the Top toolbar or use the command on the Ranges menu.
Graph settings (Sheet)
formatting graphs graph titles graph type formatting charts legends 3d 3-dimensional format x-axis format y-axis show titles show range labels change range
To customise a graph:
•	Change its "type", e.g. to a bar graph or X/Y Scatter graph, by tapping the Type button on the Top toolbar, and select the graph type you want.
•	Choose between 2D and 3D presentation, by tapping the 3D button on the Top toolbar, or using Graph settings on the Graphs menu and ticking 3-dimensional.
•	Add legends, by tapping the Legends button on the Top toolbar and choosing a position, or using Graph settings on the Graphs menu.
•	Add titles, by selecting Graph settings on the Graphs menu, and entering the title. The appearance of the title can be changed by selecting Title font on the Graphs menu and setting the formatting options.
•	Add labels to each element of a range, e.g. at the top of each bar, by selecting the type of label required on the Labels page of the Change range dialog.
•	Add titles to the axes, by using Format X-axis and Format Y-axis on the Axes menu, ticking the Show title box and typing in the title.
•	Turn axis labels off, by removing the ticks from the Show range labels boxes in the Format X-axis and Format Y-axis dialogs.
•	The formatting of titles and labels can be changed by tapping on them.
Axis markings on graphs (Sheet)
format x-axis format y-axis tick marks grid lines Show minor grid lines range where axes cross
Put major and minor "tick marks" across the axes of graphs and add grid lines to make divisions and values clearer.
•	A tick mark is a small line which helps divide the X and Y axis into smaller sections.
To add tick marks:
1.	Tap the axis to which you want to add tick marks, or select Format Y-Axis or Format X-axis on the Axes menu.
2.	Select the Other page in the dialog. Choose the type of tick marks required.
•	Tick marks can be inside, outside or across the axis.
To add grid lines:
1.	Tap the axis to which you want to add grid lines or select Format Y-axis or Format X-axis on the Axes menu.
2.	Select the Other page and tick Show major grid lines and/or Show minor grid lines.
To change the point at which the Y-axis crosses the X-axis: alter the Range where axes cross value in the Format X-axis dialog.
To hide an axis altogether: remove the tick from Show axis line on the Other page in the Format X-axis or Format Y-axis dialogs.
Shading graphs (Sheet)
Chart change range appearance range name fill style fill colours line colours fill colors line colors
To customise the appearance of the graph, change the:
•	Background and text colours, by selecting Graph settings on the Graphs menu and using the Colours page. Select the colours for the title and label text, and for the screen background.
•	Shading options for each range, by selecting Change range on the Ranges menu and using the Appearance page. Choose the range you want to edit in the Range name line, then use the Fill style, Fill colour and Line colour lines to create the style for that range.
Adding ranges to graphs (Sheet)
add range pie charts more ranges
To display a number of ranges on the same graph, e.g., to compare sets of monthly figures for several different years:
1.	Once a one-range graph has been created, highlight the next range in the Sheet view and go to the Graph view.
2.	Select Add range on the Ranges menu.
•	"Pie chart" graphs only display the first of the ranges selected in the Sheet view. A graph with multiple ranges can be created by selecting several columns or rows before switching to the Graph view.
Changing cell information formats (Sheet)
general fixed scientific percent triad true/false currency Data type formatting cells number format
The information format can be changed for individual cells, individual ranges or for the whole worksheet.
To change the format for selected cells, select Number on the Format menu, and select the appropriate settings in the dialog.
Sheet can display information in the following formats:
•	General: this displays numbers in standard notation, with decimal places if required. It aligns text to the left side of a cell, and numbers to the right.
•	Fixed: this displays numbers with a specified number of decimal places.
•	Scientific: this displays values as a single digit to the left of the decimal point, a specified number of decimal places, and then an exponent.
•	Currency: this prefixes numbers with your chosen currency sign.
•	Percent: this displays numbers as percentages with a % sign after them. 1 is taken to be 100%, so 0.45 will be displayed as 45%.
•	Triad: this splits long numbers before the decimal point into groups of 3 digits, e.g. 3,000,000.
•	True/False: in this format, a value of 0 will be displayed as FALSE, while all other values are displayed as TRUE.
•	Text: this treats all information as text.
•	Date & Time: a variety of formats which display dates and times in a range of styles.
Times and dates (Sheet)
To enter a date: type the day, month and year, separating them with spaces, e.g. '29 10 73'. You can also use '29 Oct 73'.
To enter a time: type the hours, minutes and seconds, separating them with colons, e.g. '14:10:25'.
•	The time and date will be displayed according to the "Number format" settings for the particular cells.
Display preferences (Sheet)
show grid lines show grid labels show zero values scroll bars scrollbars
Change the way the worksheet looks by setting Preferences on the Tools menu.
•	To hide the grid in the Sheet view: remove the tick from the Show grid lines box.
•	If the worksheet is printed while grid lines are displayed, the grid lines will be printed as well.
•	To hide the labels on the top and left sides of the worksheet, remove the tick from the Show grid labels box.
•	To permanently hide or display either of the scroll bars, tap either Off or On on the Horizontal and Vertical scroll bar lines.
•	The Auto setting for scroll bars means they will automatically appear whenever your work extends off the present screen area.
•	To display results of calculations that come to zero, tick the Show zero values box.
Cutting, copying & pasting (Sheet)
Formulas Paste special
Cells can be copied and pasted in Sheet using the Cut, Copy and Paste commands.
•	Where a cell contains a formula, it is the formula which is copied, not the value displayed in the cell. When the formula is pasted into the new cell it will refer to an equivalent range of cells relative to its new position (except when using 'absolute references').
•	To paste into an area different in size and shape from the area copied, highlight the top left-hand cell of the new area and then select Paste from the Edit menu.
•	If the new area is larger than the copied one, Sheet will repeat the information as many times as it takes to fill the area. This will only work, however, if the information can be repeated a whole number of times in the new area.
Use Paste special from the Paste cascade on the Edit menu to:
•	Overwrite the information in the cells you are pasting to.
•	Paste the values that appear in cells, rather than the formulae stored in them.
•	Add the new values to, or subtract the new values from, the values in the cells you are pasting to.
•	Multiply or divide the new values by the values in the cells you are pasting to.
•	Paste only part of the information in the new cells, such as formatting, in the cells you are pasting to.
•	If the area you are copying from contains blank cells, select Skip empty cells from Paste special on the Paste cascade on the Edit menu to prevent these empty cells overwriting anything in the pasting area.
Keypresses (Sheet)
ctrl fn keyboard
These are the default keypresses:
Fn+left arrow key - go to column A.
Fn+right arrow key - go to the right most column of the sheet.
Ctrl+Fn+up arrow key - go to row 1.
Ctrl+Fn+down arrow key - go to the bottom most row of the sheet.
Ctrl+Fn+left arrow key - go to cell A1.
Ctrl+Fn+right arrow key - go to the bottom right cell of the sheet.
Ctrl+Space select a whole column.
Ctrl+Tab select a whole row.
Mathematical functions (Sheet)
Maths functions abs acos asin atan2 cos degrees exp fact tan trunc sumproduct sqrt sin sign rand round radians int pi mod log10 ln
Sheet has a wide range of mathematical functions as follows:
•	The results of trigonometric calculations are displayed in radians.
ABS(value) returns the absolute value of a number, i.e. regardless of whether it is positive or negative.
ACOS(value) returns the arcCosine (inverse cosine) of a value.
ASIN(value) returns the arcSine (inverse sine) of a value.
ATAN(value) returns the arcTangent (inverse tangent) of a value.
ATAN2(x_value,y_value) returns the angle, in radians, whose tangent is y/x.
COS(value) returns the cosine of a number.
DEGREES(value) converts a given value from radians into degrees.
EXP(value) returns the value of e raised to a given number.
FACT(value) returns the factorial of a given number.
INT(value) rounds a value down to the nearest whole number.
LN(value) returns the natural logarithm of a number.
LOG10(value) returns the base-10 logarithm of a value.
MOD(value1,value2) returns the remainder (called the modulus) when value1 is divided by value2.
PI: use this to insert pi in a cell, sum or formula.
RADIANS(value) converts a given value from degrees into radians.
RAND returns a random number between zero and one. Use +RAND to insert a random number in a cell which does not change with each recalculation.
ROUND(number,number_of_digits) to round a number to a given number of digits. Make the second number a negative to round to a digit to the left of the decimal point. E.g. -2 to round to the nearest 100.
SIGN(value) indicates the sign of a number or of the number contained in a given cell. It returns 1 if the sign is positive, -1 if the sign is negative, and 0 if the number is 0.
SIN(value) returns the sine of a number.
SQRT(value) returns the square root of a number.
SUMPRODUCT(range1,range2) returns the sum of the products of corresponding items in two ranges. I.e., the first item in range1 is multiplied by the first item in range2, the second by the second, and so on. The result is the sum total of all these products.
TAN(value) returns the tangent of a number.
TRUNC(value) returns a number or the number stored in a cell with any fraction removed. So, =TRUNC(5.845) returns 5. TRUNC and INT only differ where the value is negative, as INT always rounds down to the lower number, where TRUNC just removes the section after the decimal point.
Statistical functions (Sheet)
testing stats combin permut average choose varp sum stdevp product min counta countblank max
The statistics functions are as follows:
•	Where a function requires a list of items between brackets, separate the items with commas. Alternatively, insert a range or a range name in the brackets.
COMBIN(n1,n2) returns the number of possible unique groups containing n2 number of items that could be made from a given total number of items, n1.
PERMUT(n1,n2) returns the number of possible permutations for a number, n2, of items selected from a given total number of items, n1. Unlike COMBIN, PERMUT treats the order of the possible groups as being important, so xy is different from yx.
AVERAGE(list) returns the average of the numbers in the list. E.g. =AVERAGE(3,4,5,6) returns 4.5.
CHOOSE(x,list) returns the xth item in the list, taking the first item to be number 1. The list may contain both text strings and values.
COUNT(list) returns the number of cells in the list which contain numerical information. Blank cells are not counted.
COUNTA(list) returns the number of cells in the list which contain any information. This excludes blank cells, but includes those which contain blank text (i.e. where the cell contains a ' mark).
COUNTBLANK(list) returns the number of blank cells in the list. This counts cells which contain blank text, or formulae which return blank text.
MAX(list) returns the maximum value in a list. MAX takes account of + and - signs, so 2 is larger than -10.
MIN(list) returns the minimum value in a list. MIN takes account of + and - signs, so -10 is smaller than 2.
PRODUCT(list) calculates the product of a list of values.
STDEVP(list) returns the standard deviation of the values in the list, treating them as a population.
STDEV(list) returns the standard deviation of the values in the list, treating them as a sample.
SUM(list) returns the sum of a list of values.
SUMSQ(list) calculates the sum of the squares of a list of values. E.g. =SUMSQ(3,5) returns 34.
VARP(list) returns the population variance of a list of values, treating them as a population.
VAR(list) returns the variance of values in a list, treating them as a sample.
Date & time functions (Sheet)
datevalue day hour minute month now second today timevalue year
The date and time functions in Sheet treat both dates and times as numbers.
•	Dates use a count of days that takes 31/12/1899 as 1. Thus, 1/1/1900 has a value of 2, and 31/07/1997 has a value of 35642.
•	Times use a count that takes midnight as 0, noon as 0.5, and just before midnight as 0.999…
DATE(year,month,day) returns the day number for the specified year, month and day.
DATEVALUE(datestring) returns the appropriate number, given the date string. E.g. =DATEVALUE("31 Jul 97") returns a value of 35642.
DAY(x) returns the day of the month (1 to 31) according to the day number x. E.g., =DAY(15476) returns 15, since day 15476 is the 15th of the month.
HOUR(x) returns the hour number between 0 and 23, according to the time value x. E.g., =HOUR(0.6) returns 14.
MINUTE(x) returns the minute number between 0 and 59, according to the time value x. E.g., =MINUTE(0.612) returns 41.
MONTH(x) returns the month number between 1 and 12, according to the day value x. E.g., =MONTH(15476) returns 5, since day 15476 falls in May.
NOW returns the value of the present time, combining both date and time numbers. E.g. 35642.5 is noon on 31/07/1997.
SECOND(x) returns the second number between 0 and 59, according to the time number x. E.g., =SECOND(0.612) returns 16.
TODAY returns the day number of the current day.
TIME(hour,minute,second) returns the time number for the specified hour, minute and second.
TIMEVALUE(timestring) returns the appropriate number, given the timestring. E.g., =TIMEVALUE("15:52:12") returns 0.66125.
YEAR(x) returns the year number (from 0 to 255) from the day number x. E.g., =YEAR(35642) returns 97, since day 35642 falls in 1997.
Lookup functions (Sheet)
address columns hlookup index vlookup rows offset at
The lookup functions are as follows:
ADDRESS(row_number,column_number) returns an absolute cell reference as a string, given the row and column numbers. So, =ADDRESS(5,4) returns '$D$5'.
COLUMN(x) returns the column number of a cell, or the number of the first column in a range.
COLUMNS(range) returns the number of columns in a range.
HLOOKUP(x,range,offset) looks along the top row of a range until it finds x. It then counts down to the cell which is offset rows below, taking the row that x is in as 1, and returns the value contained in that cell. The values in the first row of the range must be in ascending order.
INDEX(range,column,row) returns the contents of the cell at position (column,row) in range, where the top left cell in range is column 1, row 1.
INDIRECT(x) returns the information stored in the first cell that the cell x refers to. So, if B5 contains 'A2' and A2 contains '6', the function =AT(B5) will return 6.
LOOKUP(x,range1,range2) looks for x (either a string or a number) in range1, then returns the value in the corresponding cell in range2. Each of the ranges should be either a single row or a single column of cells, and they must both be equal in length.
OFFSET(reference,rows,columns) returns a new reference offset by rows and columns from the one you specify. Use positive numbers for rows and columns to offset right and down, and negative ones to offset left and up. If a range is specified in reference, the offset will be from the top left corner of the range.
ROW(x) returns the row number of a cell, or the number of the first row in a range.
ROWS(range) returns the number of rows in a range.
VLOOKUP(x,range,offset) works the same way as HLOOKUP, but counts down first, then across. The values in the first column of the range must be in ascending order.
Logical functions (Sheet)
false true if then and not or
The logical functions are as follows:
FALSE returns FALSE or 0.
TRUE returns TRUE or 1.
IF(x,then,else) where x, then and else may be cell references, values or expressions. Returns the value of then if x is non-zero (true), or the value of else if x is zero (false).
Sheet has 3 logical operators:
AND: use this to link more than one condition in an IF expression where both conditions must be true. E.g. =IF(D2>2000 AND D3>15000,"Taxable","No tax") will return 'Taxable' only if the conditions for both D2 and D3 are met. Otherwise it will return 'No tax'.
NOT: use this to negate the condition in an IF test. E.g. IF(NOT A1+A2=3,1,0) will return 1 if the sum of A1+A2 is NOT equal to 3.
OR: use this to link more than one condition in an IF test where only one of the conditions must be true. E.g. IF(A1=1 OR A1=2,1,0) will return 1 if A1 contains either 1 or 2, but will return a 0 in any other case.
Information functions (Sheet)
cell error.type istext isnumber isnontext iserrors isna islogical isblank
The information functions are as follows:
CELL(information,range) returns a type of information about the upper left cell in a range.The text string, 'information' can have the values "ROW", "COL", "ADDRESS" or "CONTENTS", which return the row number, column number, cell reference or the contents respectively.
ERROR.TYPE(x) returns a number between 1 and 7, according to the type of error value that exists in cell x. The numbers signify the following errors:
1	#NULL!: the information in the cell refers to an intersection of ranges that do not in fact intersect.
2	#DIV/0: the information in the cell involves division by zero.
3	#VALUE!: a formula contains the wrong kind of item, such as =3+"ghost".
4	#REF!: the cell contains an invalid reference.
5	#NAME?: the cell contains a reference to an invalid name.
6	#NUM!: the information in the cell involves an impossible calculation, or one whose result lies outside of Sheet's range.
7	#N/A: no information is available for the current cell.
ISBLANK(x) returns TRUE if x contains no information, or FALSE if x contains information.
ISERR(x) where x is a cell reference returns TRUE if the value in x is any error except #N/A, otherwise it returns FALSE.
•	Any cells which refer to other cells containing either an error or #N/A will also display the error or #N/A. To check for this ripple-through effect use ISERR or ISNA
ISNA(x) where x is a cell reference returns TRUE if the value in x is #N/A, or FALSE if the value in x is not #N/A.
ISERROR(x) where x is a cell reference returns TRUE if the value in x is any error, otherwise it returns FALSE.
ISLOGICAL(x) returns TRUE if the information in cell x is logical, FALSE if it is not.
ISNONTEXT(x) returns TRUE if the information in x is not text, or FALSE if the information in x is text.
ISNUMBER(x) returns TRUE if x is a number or a cell containing either a number or a formula which returns a number. Otherwise it returns FALSE.
ISTEXT(x) returns TRUE if the information in x is text, or FALSE if the information in x is not text.
N(x) returns the number in a cell or in the top left cell of a range. If the cell does not contain a number, Sheet returns FALSE.
TYPE(x) returns a code according to the type of information x contains. The codes are:
1	Number.
2	Text.
4	Logical value.
8	Formula.
16	Error value.
T(x) returns the text contained in a cell or in the top left cell of a range. If the cell does not contain text, blank text (an "empty string") is returned.
Text functions (Sheet)
char code exact find left len value upper trim string right rept replace proper mid lower
The text functions are as follows:
& joins together ("concatenates") one or more text strings. So, ="here"&"and"&"now" will return 'hereandnow'.
CHAR(x) returns the character indicated by the character code x.
CODE(string) returns the character code for the first character in string. E.g. =CODE("Hillcrest") returns the value 72, the character code for 'H'.
EXACT(string1,string2) will return TRUE if string1 is exactly the same as string2, otherwise it will return FALSE. The comparison takes account of case and accents.
FIND(searchstring,string,start_position) returns the position in string where searchstring starts. The search begins at start_position. Make start position 1 to start searching from the first character, 2 to start at the second, and so on.
LEFT(string,n) returns the first n letters of string. E.g. =LEFT("discotheque",5) will return 'disco'.
LEN(string) returns the number of characters, including spaces, in the string. E.g. =LEN("Hillcrest") will return the value 9.
LOWER(string) returns the string converted entirely to lower case. E.g. =LOWER("Clive") will return 'clive'.
MID(string,start_position,n) returns a string n characters long, taken from the start_position in string. For the start position, 0 is the first character, 1 the second and so on.
PROPER(string) returns the string with the first letter of each word in upper case and the rest in lower case. E.g. =PROPER("clive browning") returns 'Clive Browning'.
REPLACE(string,start_position,n,newstring) removes n characters from string, starting from start_position, then inserts newstring at start_position, and returns the resulting string. E.g. =REPLACE("electorate",6,5,"ion") returns 'election'.
REPT(string,n) returns string repeated n times. E.g. =REPT("*",50) will return a bar ***** 50 characters long.
RIGHT(string,n) returns the last n letters of a string. E.g. =RIGHT("farmhouse",5) will return 'house'.
STRING(x,n) returns a number x as a string to n decimal places. So, =STRING(3.1416,3) returns the string '3.142'.
TRIM(string) returns the string with leading and trailing spaces removed, and reduces multiple spaces to a single space.
UPPER(string) returns the string converted to upper case.
VALUE(string) converts a string to a number. E.g. =VALUE("45.9") returns the number 45.9.
Cash flow functions (Sheet)
irr npv financial
The financial functions include functions for dealing with cash flow.
In these functions, the item cash_flows should be a range reference, e.g. D2:D8, for cells containing the initial cost of the investment and the receipts resulting from it. Arrange these in chronological order, with the initial outlay in the first cell. Make all expenditures negative amounts, and all receipts positive.
•	When using these functions:
•	Enter rates as a percentage or a decimal, e.g. 17.5% or 0.175.
•	Enter interest rates for the same unit of time as the term. So if you use 8 as the term to indicate 8 months, use the interest rate per month rather than per year.
IRR(guess,cash_flows) calculates the Internal Rate of Return of a an investment, to give the approximate interest rate earned. 'Guess' is a number you suppose to be close to the IRR.
•	Cash flows are assumed to occur at equal intervals. If there are no receipts for a period, leave the cell blank.
NPV(interest,cash_flows) calculates the Net Present Value of an investment: what it would be worth if you had it as a lump sum now rather than a future receipt. Interest is the interest rate per receipt period. NPV assumes the receipt occurs at the beginning of the period. If receipt occurs at the end of the period, use the formula:
npv = first_receipt + NPV(interest,remaining_receipts)
Lump sum investments (Sheet)
cterm rate financial
The financial functions include functions for dealing with lump sum investments. These assume an investment earns a fixed rate of interest.
•	Enter rates as a percentage or a decimal, e.g. 17.5% or 0.175.
•	Enter interest rates for the same unit of time as the term. So if 8 is used as the term to indicate 8 months, use the interest rate per month rather than per year.
CTERM(interest,future_value,principal) returns the number of periods it will take for an investment of principal to reach future_value at a given interest rate. CTERM uses the formula:
term = ln(future_value/principal)/ln(1+interest)
RATE(future_value,principal,periods) returns the interest rate for an investment, principal, to reach future_value over the given number of periods. RATE uses the formula:
interest rate = ((future_value/principal)1/periods) - 1
•	Interest rates are often calculated for a month. The annual rate of interest cannot be calculated by multiplying this rate by 12. Instead, use the formula:
annual rate = ((1+monthly rate)12)-1
Depreciation functions (Sheet)
sln syd ddb double declining balance straight-line depreciation sum of the years digits sum-of-the-years-digits financial
The financial functions include functions for calculating the depreciation in value of an asset over time. They use the terms:
•	Cost: the initial outlay on the asset.
•	Salvage: the value at the end of its life.
•	Life: the number of periods of the assets life.
•	Period: the length of time of a period of depreciation.
SLN(cost,salvage,life) returns the depreciation per period for the asset's life. SLN assumes that the asset loses the same amount of its value during each period, and uses the formula:
depreciation per period=(cost-salvage)/life.
SYD(cost,salvage,life,period) returns the depreciation per period, assuming that the asset loses more of its value at the beginning of its life than at the end. SYD uses the formula:
depreciation per period= (cost-salvage)*(life-period+1)/(life*(life+1)/2).
DDB(cost,salvage,life,period) returns the depreciation per period, assuming that the asset loses much more of its value at the beginning of its life than at the end. DDB uses the formula:
depreciation per period=(book value*2)/life
where
book value at the start of each period= (total cost-total depreciation over all previous periods).
Annuities & loans (Sheet)
fv pv pmt term borrowed interest payments periodic payment financial
The financial functions include functions for calculating annuity investments and loan payments.
•	Enter rates as a percentage or a decimal, e.g. 17.5% or 0.175.
•	Enter interest rates for the same unit of time as the term. So if 8 is used as the term to indicate 8 months, use the interest rate per month rather than per year.
Annuity investments:
An annuity is an investment of a series of equal payments earning a fixed rate of interest, e.g. a savings account into which you pay £500 a month.
FV(interest,term,payment) returns the matured value of an investment given the interest rate, the term, and the payment per period. FV uses the formula:
fv=payment*(((1+interest)term)-1)/interest
where term is the number of periods.
PV(interest,term,payment) returns the present value of an investment given the interest rate, the term, and the payment per period. PV uses the formula:
present_value = payment * (1-(1+interest)-n) / interest.
TERM(payments,interest,future_value) returns the number of payments needed to accumulate future_value from a series of payments earning a periodic interest rate. TERM uses the formula:
number of payments= ln(1+future_value*interest/payment))/ln(1+interest)
where ln is the natural logarithm.
Loan payments:
PMT(interest,term,principal) returns the amount to be repaid per period on a loan, given the interest rate, the number of repayment periods and the principal amount loaned.
The function assumes repayment is made at the end of each period. If repayment is at the beginning of each period, use the formula =PMT(interest,term,principal)/(1+interest). PMT uses the formula:
payment=principal*interest/(1-(1+interest)-term).
Sorting (Sheet)
alphabetical order re-order sorted sort rows sort columns
You can sort the information in rows and columns in ascending or descending order. You can specify the sort order for up to three rows or columns at a time.
To sort rows:
1.	Highlight the rows which you want to sort.
2.	Select Sort|Left-to-right from the Tools menu.
3.	Specify the first row you want to sort on the 1st row line, and the order in which you want to sort it.
4.	To sort more than one row, select the row on the 2nd row and then the 3rd row lines, and select the order in which you want to sort.
•	If you want to make the sort order case sensitive for one or more rows, tap the Options button and tick the box beside the row.
To sort columns:
1.	Highlight the columns which you want to sort.
2.	Select Sort|Top-to-bottom from the Tools menu.
3.	Select the first column you want to sort on the 1st column line, and the order in which you want to sort it.
4.	To sort more than one column, select the column on the 2nd column and 3rd column lines, and then select the order in which you want to sort.
•	If you want to make the sort order case sensitive for one or more columns, tap the Options button and tick the box beside the column.