 The Spreadsheet Manual

Part Two, Reference

General

Functions

 ABS ACOS ASIN ATAN ATAN2 AVG CHOOSE COS COUNT DATE DAY ERR EXP FALSE FV HLOOKUP IF INT IRR ISERR ISNA LN LOG MAX MIN MOD MONTH NA NPV OPL PI PMT PV RAND ROUND SIN SQRT STD SUM TAN TODAY TRUE VAR VLOOKUP YEAR

Menu Items

The OPL Function Functions

Any of the functions listed here may be included in cell formulae. A function takes the parameters supplied it and returns a value to the appropriate point in the formula.

Functions must be supplied with the correct number and type of arguments. If not, a 'syntax error' will be Indicated when you try to enter the formula into a cell.

The arguments must be enclosed in brackets and separated by commas.

Arguments may be literal values or expressions. In the descriptions of the functions:

<exp> is either a numeric expression or a reference to a cell displaying a numeric value (the numeric contents of the cell are taken for the operation).

<range> is a cell range in the format B2:G12.

<list> is a list of cell references, cell ranges and numeric expressions, separated by commas, for example B2, D3:F6,33.

Any other argument will be either a numeric expression or a reference to a single cell displaying a numeric value.

Example formulae using functions are:

• =AVG(B2:G17)
• =LOG(12)
• =LOG(B2)

The functions available in the Pocket Spreadsheet are these:

ABS(<exp>)

Returns the absolute value, i.e. without any sign, of <exp>.
For example, the formula =ABS(-10) returns the value 10.

ACOS(<exp>)

Returns the angle, in radians, whose cosine is <exp>.

ASIN(<exp>)

Returns the angle, in radians, whose sine is <exp>.

ATAN(<exp>)

Returns the angle, in radians, whose tangent is <exp>.

ATAN2(<exp1>,<exp2>)

Returns the angle, in radians, whose tangent is <exp2>/<exp1>.
Returns a result in the correct quadrant, taking account of the signs of <exp2> and <exp1>.

AVG(<list>)

Returns the arithmetic mean (average) of the values in <list>.
The items in <list> may be literal values, cell references or cell ranges. Specifically, function returns SUM(<list>)/(COUNT<list>)

CHOOSE(<exp>,<list>)

Returns the item at Position <exp> in the <list>.
The index, <exp> may be a literal value, a cell reference or a formula. The values in <list> may be literal values or cell references.

COS(<exp>)

Returns the cosine ff the (radian) angle <exp>.

COUNT(<list>)

Returns the number of non-blank cells In <list>.
Ranges may be included in the list.
If the literal value 0 appears in <list>, this is evaluated as non-blank and therefore counted.

DATE(yy,mm,dd)

Returns the number of days elapsed between 30th Dec 1899 and the specified date (1.1.1900 = day 2).
In Lotus 1-2-3, 1900 is incorrectly taken to be a leap year, so values returned by this function will differ from those Of Lotus 1-2-3 by 1 until 1.3.1900. Future limit is 31.12.2078.

DAY(<exp>)

Given a number (<exp>) of elapsed days since 30.12.1899, returns the day of the month in which that day lies.
For example, the formula =DAY(31941) returns 13 because 31941 days after 30.12.1899 is 13th June 1987. Future limit is 31.12.2078.

ERR

Returns an illegal value interpreted as ERRor.
For example, using the IF function, the formula =IF(G12<3,ERR,B2) returns ERR if the contents of G 12 are less than 3 and returns the contents of B2 otherwise. This can then be tested with the ISERR function.

EXP(<exp>)

Returns the value of the arithmetic constant e (2.71828 ... ) raised to the power of the expression inside the brackets.

FALSE

Returns the value 0.0 (the value returned by comparisons, e.g. =3<2 returns 0.0).
For example, B2 might contain a non-zero number to denote a certain event, so the formula =IF(B2=FALSE,D2,G2) would return the contents of cell D2 if cell B2 contains 0 or the contents of G2 otherwise.

FV(<payment>,<interest>,<term>)

Returns the future value of an annuity given the <interest> rate, the <payment> amount per period and the number of periods (<term>) according to the formula:

FV=<payment>*((1+<interest>)**<term>)- 1 /<interest>)

The interest rate is the period interest rate and is expressed in decimal form, i.e. 14% is expressed as .14.

HLOOKUP(<exp>,<range>,<offset>)

Performs a horizontal lookup on the values in the range of cells <range>.

The comparison values in the first row of the range must be in increasing order with no duplicates. The function finds the last cell in this row whose contents is not greater than the given index <exp> (unless this is the first cell in the range, when ERR Is returned). The function then returns the contents of the cell which lies <offset> rows below it. Non-integer values of <exp> are truncated to their integer part.

For example, in this worksheet:

```       |    |B     |C     |D     |E     |F
|2   |     1|     2|     3|     5|
|3   |    14|    15|    16|    17|
|4   |    18|    19|    20|    21|
|5   |    22|    23|    24|    25|
|6   |      |      |      |      | ```
 Formula Result =HLOOKUP(1,B2:E5,3) 22 =HLOOKUP(1.4,B2:E5,3) 22 =HLOOKUP(3,B2:E5,2) 20 =HLOOKUP(4,B2:E5, 1) 16 =HLOOKUP(5,B2:E5,2) 21

If the value of <offset> is negative or greater than or equal to the number of rows in the range then ERR is returned.

IF(<exp>, <then>, <else>)

If the condition <exp> returns non-zero (TRUE), the value of <then> is returned; otherwise, the value of <else> is returned.
For example, the formula =IF(B2=7,D2,G2) would return the contents of cell D2 if cell B2 contains 7 or the contents of G2 otherwise.

INT(<exp>)

Returns the integer (i.e. the whole number part) of <exp>. Negative numbers are rounded towards zero, so the formula =INT(5.7) returns the value -5.

IRR(<guess>,<range>)

Returns the approximate internal rate of return from a series of discounted cash flows (i.e. the interest rate which would give a net present value of zero on those cash flows).
The required arguments are an initial <guess> at the result and a cell <range>. The <range> contains a series of positive (income) and negative (payment) figures. Blank cells in <range> are permissible. The cash flows are taken to occur at the start of the period.

For example, in the next worksheet, the formula =IRR(B3,C3:C8) would return .06402. The interest rate is returned as a decimal, i.e. 14% would be returned as . 14.

```       |    |B    |C    |D
|2   |Guess|Range|
|3   |  0.5|-1500|
|4   |     |  360|
|5   |     |  360|
|6   |     |  360|
|7   |     |  360|
|8   |     |  360| ```

An iterative method is used to calculate the result, and if convergence to a valid result does not occur within 20 iterations, the result ERR is returned. The interest rate returned by IRR is the period interest rate, where that period is the fixed interval between the cash flows. If you require the interest rate for a different period, you can convert the value returned by the IRR function.

Converting one period interest rate to the equivalent interest rate for a different period is not simply a matter of multiplying by the time factor.

For example, a monthly interest rate of 2% is not equivalent to an annual rate of 24%. The correct equation is based on the statement that equivalent interest rates produce the same discount after the same time. A cashflow which occurs after two years must have the same discount as a cash flow which occurs after 24 months.

The formula to convert one period interest rate (i1) into another (i2) is:

i2 = (1+i1)**(t2/t1) -1

where t1 and t2 are the corresponding periods, in the same units.

For example, if i1 is the annual rate and i2 is the monthly rate, then t1 is 12 months and t2 is 1 month, and the formula is:

i2 = (1+i1)**(1/12) - 1

Conversely, If i1 is the monthly rate and i2 is the annual rate, the formula is:

i2 = (1+i1)**12 - 1

Using this equation, we can calculate that a 2% monthly rate Is equivalent to a 26.8% annual rate. (The results are displayed as a decimal, for example 0.02; 0.268.)

In the UK, the APR (Annual Percentage Rate) is a standard way of quoting interest rates so that consumers can compare interest rates offered by different companies. The IRR function may be used to derive the APR provided the result is converted, If necessary, to an annual rate.

ISERR(<exp>)

Returns TRUE (1.0) if <exp> returns ERR; otherwise returns FALSE (0.0).
For example, if cell B2 contains the formula =B2/0 (illegal divide by zero) the formula =ISERR(B2) returns TRUE (1.0).

ISNA(<exp>)

Returns TRUE (1.0) if <exp> returns NA; otherwise returns FALSE (0.0).
For example, if cell B2 contains the formula IF(B3>99,NA,B3), the formula =ISNA(B2) returns TRUE (1.0) if the contents of B3 are greater than 99.

LN(<exp>)

Returns the natural (base e) logarithm of <exp>.

LOG(<exp>)

Returns the base 10 logarithm of <exp>.

MAX(<list>)

Returns the largest value in <list>. <list> may be literal values, cell references or cell ranges.
Text cells evaluate to zero.

MIN(<list>)

Returns the smallest value in <list>.
The list may consist of literal values, cell references or cell ranges. Text cells evaluate to zero.

MOD(<exp1>,<exp2>)

Returns the remainder from <exp1> divided by <exp2> (modulo).
The arguments may be numbers or cell references.

MONTH(<exp>)

Given a number (<exp>) of elapsed days since 30.12.1899, returns the month of the year in which that day lies.
For example, the formula =MONTH(31941) returns 6 because 31941 days after 30.12.1899 is 13th June 1987. Future limit is 31.12.2078.

NA

Returns NA (Not Available).
For example, the formula =IF(B4>99,NA,B4) returns NA if the contents of B4 are greater than 99 and returns the contents of B4 otherwise.

NPV(<interest>,<range>)

Returns the Net Present Value of a series of future discounted cash flows for a given <interest> rate per period. <range> contains the positive (income) and negative (payment) cash flows.

The first cash flow is taken to occur at the end of the first period, and blank cells in the <range> are permissible. Subsequent cash flows are taken to occur at the end of subsequent periods.

For example, in the next worksheet, an initial payment of 4700 is followed by five receipts of 1600 at an interest rate of 14%. The formula =NPV(B2,C3:C7) would return the net present value of 5492.93 (to two decimal places). If you then subtract the initial payment you have the profit of 792.93 on the investment.

```       |    |B    |C    |D
|2   | 0.14|-4700|
|3   |     | 1600|
|4   |     | 1600|
|5   |     | 1600|
|6   |     | 1600|
|7   |     | 1600|
|8   |     |     | ```

The interest rate should be given as a decimal, i.e. 14% should be given as . 14.

OPL(<cell>,<range>)

It is recommended that you use this function only when you have a sound working knowledge of OPL (Organiser Programming Language) and the way in which values are passed back from OPL procedures. Refer to your main Organiser manual for details of this subject.

The OPL function adds two facilities to the Spreadsheet:

• Any cell in the grid can call OPL procedures.
• An OPL procedure called by a cell in the grid can 'grab' the contents of other cells in the same worksheet.

For further details see chapter The OPL Function.

PI

Returns the value of the mathematical constant Pi.

PMT(<principal>,<interest>,<term>)

Returns the mortgage payment per period given the amount of the loan (<principal>), the <interest> rate per period and the number periods (<term>).

The interest rate should be given as a decimal, i.e. 14% should be given as .14.

PV(<payment>,<interest>,<term>)

Returns the present value of an annuity, given the <payment> per period, the <interest> rate per period and the number of periods (<term>). For example, in this worksheet:

```       |    |B    |C    |D
|2   |Paymt|  .95|
|3   |Int  |   .1|
|4   |Term |   12|
|5   |     |     |```

the formula =PV(C2,C3,C4) returns the value 647.30.

The interest rate should be given as a decimal, i.e. 14% should be given as .14.

RAND

Returns a random floating point number in the range 0.0 (inclusive) to 1.0 (exclusive).

ROUND(<exp1>,<exp2>)

Returns the value of a cell-content <exp1> rounded to <exp2> decimal places.
The value of <exp2> must be in the range -11 to +11. If the value of <exp2> is less than 0 the function returns the value of <exp2> rounded to the nearest 10**ABS(<exp2>).
For example:

 Formula Result =ROUND(26858.30458,-3) 27000 =ROUND(26858.30458,-1) 26860 =ROUND(26858.30458,0) 26858 =ROUND(26858.30458,1) 26858.3 =ROUND(26858.30458,2) 26858.3 =ROUND(26858.30458,5) 26858.30458

Note that the zero in the second decimal place when the second parameter to ROUND is 2 isn't stored, nor displayed unless Format is set to 2 decimal places.

SIN(<exp>)

Returns the sine of the (radian) angle <exp>.

SQRT(<exp>)

Returns the square root of <exp>.

STD(<list>)

Returns the standard deviation of the numbers In <list>.
If all cells in <list> are empty, ERR is returned. Blank cells in <list> are ignored. Text cells evaluate to zero.

SUM(<list>)

Returns the sum of the values or contents of cells in <list>.
Text cells evaluate to zero.

TAN(<exp>)

Returns the tangent of the (radian) angle <exp>.

TODAY

Returns the number of days elapsed since 30.12.1899.
For example, if the Organiser's date is set to 26 August 1987, the formula =TODAY returns the value 32015 because on that date there are 32015 elapsed days since 30.12.1899. Future limit is 31.12.2078.

TRUE

Returns the value 1.0 (the value returned by comparisons, e.g. =3>2 returns 1.0).
For example. cell B2 might contain 1 to denote a certain event, so the formula =IF(B2=TRUE,D2,G2) would return the contents of cell D2 if cell B2 contains 1 or the contents of G2 otherwise.

VAR(<list>)

Returns the variance of the items in <list>.
The items in <list> may be literal values, cells or cell ranges. If there are no non-blank cells or values In <list>, ERR is returned. Text cells evaluate to zero.

VLOOKUP(<exp>,<range>,<offset>)

Performs a vertical lookup m the values In the range of cells <range>.

The comparison values in the first column of the range must be in Increasing order with no duplicates. The function finds the last cell in this column whose contents is not greater than the given index <exp> (unless this is the first cell in the range, when ERR is returned). The function then returns the contents of the cell which lies <offset> columns to the right. Non-integer values of <exp> are truncated to their integer part.

For example, in this worksheet:

```       |    |B     |C     |D     |E     |F
|2   |     1|     2|     3|     5|
|3   |    14|    15|    16|    17|
|4   |    18|    19|    20|    21|
|5   |    22|    23|    24|    25|
|6   |      |      |      |      | ```

these are the values returned:

 Formula Result =VLOOKUP(1,B2:E5,3) 22 =VLOOKUP(1.4,B2:E5,3) 22 =VLOOKUP(3,B2:E5,2) 20 =VLOOKUP(4,B2:E5, 1) 16 =VLOOKUP(5,B2:E5,2) 21

If the value of <offset> is n egative or greater than or equal to the number of columns in the range then ERR is returned.

YEAR(<exp>)

Given a number (<exp>) of elapsed days since 30.12.1899, returns the year since 1900 in which that day lies.
For example, the formula =MONTH(31941) returns 87 because 31941 days after 30.12.1899 is 13th June 1987. Future limit is 31.12.2078.

 These pages should be viewed using Netscape 4.03 or Microsoft Internet Explorer 3.02 at 800x600 pixels. General Reference ~ Homepage ~ Menu Reference 