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.

DEREF

DEREF(cell_ref)

Returns the value of cell_ref.

For example,

TYPE(DEREF(A1))

returns the type of the data in A1,

whereas

TYPE(A1)

will always give reference.

FLIP

FLIP(array)

Swaps rows about the middle row of an array.

e.g. {1;2;3} becomes {3;2;1}

IF

IF(condition, then, else)

If the value of condition is TRUE, i.e. 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 cell 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.

SET_NAME

SET_NAME(name, any)

Defines name to have the value of any.

SET_VALUE

SET_VALUE(cell_ref, any)

Sets the value of a number cell at cell_ref with no formula to the value of any.

For example,

SET_VALUE([fred]A1,56)

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

SET_VALUE(E78,"fred")

sets the value of cell E78 to "fred".

SET_VALUE(range, array)

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

For example

SET_VALUE(F1F1O,E1E1O*VAT)

would multiply each of the cells 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 cells 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 cells. You need to use it carefully because any data you type into the target cells will be overwritten the next time that SET_VALUE recalculates. SET_VALUE will not overwrite cells which contain formulae.

SORT

SORT(array, number)

Sorts the rows in the array in ascending order based on the values in column number.

For example, SORT(B1E100,2) sorts rows 1..100 based on the contents of column C.

Use with FLIP() to sort in descending order.

Use with TRANSPOSE() to sort by column based on values in rows.

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.