Miscellaneous Functions

ALERT

ALERT("message","button1"{,"button2"})

Displays a dialogue box displaying message.

button1 and button2 are text to be displayed on buttons. button2 is optional.

Returns 1 when button1 is clicked and 2 when button2 is clicked.

For use only with custom functions.

COL

COL

Returns the number of the column containing the slot in which the function is evaluated.

COL(slot_ref)

Returns the number of the column of slot_ref.

For example,

COL(A1)

returns 1.

COL(range)

Returns the number of the starting column of range.

For example,

COL(E1F10)

returns 5.

This is particularly useful if you have a name which is defined to be a range.

For example, typing:

COL(apples)

where apples is a name defined to be a range, returns the number of the first column of the range.

COLS

COLS(array)

Returns the number of columns in array which may be a range or an array.

For example,

COLS({1,2,3})

returns 3.

COLS with no parameters returns the number of columns in the file.

DEREF

DEREF(slot_ref)

Returns the value of slot_ref.

For example,

TYPE(DEREF(A1))

returns the type of the data in A1,

whereas

TYPE(A1)

will always give reference.

IF

IF(condition,then,else)

If the value of condition is TRUE, ie non-zero, the function evaluates and returns then; otherwise, the function evaluates and returns else.

IF statements can be nested, so you can have, for example:

IF(A1=3,"A1 is 3",IF(B1=3,"A1 is not 3, but B1 is","Neither slot is 3"))

INPUT

INPUT("message","name","button1"{,"button2"})

Displays a dialogue box displaying message and containing an input field.

Sets name to the text typed into the input field.

button1 and button2 are text to be displayed on buttons. button2 is optional.

Returns 1 when button1 is clicked and 2 when button2 is clicked.

For use only with custom functions.

ROUND

ROUND(number,x)

Rounds number to x places.

For example:

ROUND(12345.678,2)

returns 12345.68.

ROUND(12345.678,-2)

returns 12300.

ROUND can be used to help the inaccuracy problems often encountered in accountancy.

For example, if you have 1.6 in slots A1 and A2, adding them together gives the result 3.2, but if all the numbers are being displayed without decimal places, it will look as though adding 2+2=3. This is because all calculations are performed to the greatest accuracy, but the display of numbers is rounded. If you use ROUND(A1,0) and ROUND(A2,0), PipeDream will return 2 from both of these; therefore, what appears on the screen will look consistent even though it may not match the original data.

The Snapshot command is useful with ROUND. If you have a column of numbers and you want to have them rounded to a particular degree of precision:

1. Insert a new column using Ctrl-EIC.

2. Use ROUND and the Replicate Down command (Ctrl-BRD) to create a new column of data.

3. Mark the block and use the Snapshot command (Ctrl-BSS)to fix the numbers permanently in place.

4. Use the new column in your calculations.

ROW

ROW

Returns the number of the row containing the slot in which the function is evaluated.

ROW(slot_ref)

Returns the number of the row of slot_ref.

For example,

ROW(A1)

returns 1.

ROW(range)

Returns the number of the starting row of range.

For example,

ROW(E3F10)

returns 3.

ROWS

ROWS(range)

Returns the number of rows in range. which may be a range or an array.

For example,

ROWS (A1F1O)

returns 10.

ROWS with no parameters returns the number of rows in the file.

SET_NAME

SET_NAME(name, any)

Defines name to have the value of any.

SET_VALUE

SET_VALUE(slot_ref,any)

Sets the value of a number slot at slot_ref with no formula to the value of any.

For example,

SET_VALUE([fred]A1,56)

sets the value of slot A1 in the file called fred to 56.

SET_VALUE(E78,"fred")

sets the value of slot E78 to "fred".

SET_VALUE(range,array)

Enables you to store an array (array) over a range of slots (range).

For example

SET_VALUE(F1F1O,E1E1O*VAT)

would multiply each of the slots from E1 to E10 by the current value of the name VAT, copy the results into an internal array and then write out the array into the slots F1 to F10.

Using arrays and SET_VALUE in this way can remove large chunks of formulae from spreadsheets and significantly reduce the amount of memory being used.

Note that SET_VALUE is the only spreadsheet function which overwrites other slots. You need to use it carefully because any data you type into the target slots will be overwritten the next time that SET_VALUE recalculates.

TYPE

TYPE(any)

Returns the type of any as a text string.

This will be one of "number", "text", "array", "reference", "error" or "date".

VERSION

VERSION

Returns the current version number of PipeDream.