FPSpreadsheet: List of formulas

Introduction

This is a list of the formulas supported by FPSpreadsheet.

The arguments can be constants of the given type, or cells containing values of the given type. Similar to the Office applications, type-checking is very relaxed, and data are automatically converted to the required type if possible.

Mathematical functions

<thead>

</thead> <tbody>

<code>base</code>, if omitted, is 10.</td>

</tbody>

Statistical functions

<thead>

</thead> <tbody>

<code>condition</code> is value, string or cell</td>

sfExcel5<br /> sfExcel8</td>

(like <code>A1:D5</code>)</td>

<code>condition</code> is value, string or cell</td>

<code>condition</code> is value, string or cell</td>

</tbody>

Date/time funtions

<thead>

</thead> <tbody>

<code>interval</code> is a string:<br /> <code>Y</code> = number of years,<br /> <code>M</code> = number of months,<br /> <code>D</code> = number of days</td>

Will refresh whenever the worksheet recalculates.</td>

<code>type=0</code>: Sunday=1, Saturday=7 (default)<br /> <code>type=1</code>: Monday=1, Sunday=7<br /> <code>type=2</code>: Monday=9, Sunday=6</td>

</tbody>

String functions

<thead>

</thead> <tbody>

<code>count</code>: integer (default 1)</td>

<code>start_pos, count</code>: integer</td>

<code>start_pos, count</code>: integer<br /> <code>new_text</code>: string</td>

<code>count</code>: integer<br />

<code>count</code>: integer (default 1)</td>

<code>nth_appearance</code>: integer<br /> (default: replace all)</td>

</tbody>

Logical functions

<thead>

</thead> <tbody>

or <code>value_false</code> (or false) if <code>condition</code> is false</td>

<code>value_true, value_false</code>: any type</td>

</tbody>

Info functions

Calling prototye	Meaning	Argument types	Arguments	Result type	Not for
ERROR.TYPE(value)	Returns the numeric representation of one of the errors in Excel (1 = #NULL!, 2 = #DIV/0!, 3 = #VALUE!, 4 = #REF!, 5 = #NAME?, 6 = #NUM!, #N/A else).	cell	1	integer	sfExcel2
ISBLANK(value)	Checks for blank or null values.	any, usually cell	1	boolean
ISERR(value)	Returns TRUE if value is an error but not #N/A	any, usually cell	1	boolean
ISERROR(value)	Returns TRUE if value is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL).	any, usually cell	1	boolean
ISLOGICAL(value)	Returns TRUE if value is boolean	any, usually cell	1	boolean
ISNA(value)	Returns TRUE if value is a #N/A error	any, usually cell	1	boolean
ISNONTEXT(value)	Returns TRUE if value is not a string	any, usually cell	1	boolean
ISNUMBER(value)	Returns TRUE if value is a number	any, usually cell	1	boolean
ISREF(value)	Returns TRUE if value is a cell reference	any, usually cell	1	boolean
ISTEXT(value)	Returns TRUE if value is a string	any, usually cell	1	boolean

Lookup/reference functions

<thead>

</thead> <tbody>

<code>ref_type</code> is the type of reference to use: 1=absolute (default); 2=relative column, absolute row; 3=absolute column, relative row; 4=relative<br /> <code>ref_style</code> if true (default) means: address in A1 dialect, otherwise in R1C1<br /> <code>sheet_name=name</code> of the worksheet</td>

boolean<br /> string</td>

<code>reference</code> is a reference to a cell or range of cells. If omitted, it is assumed that the reference is the cell address in which the COLUMN function has been entered.</td>

type = 1 (default) finds the largest value <= <code>value</code> (assumes an array in ascending order)<br /> type = -1 finds the smallest value >= <code>value</code> (assumes an array in descending order)<br /> type = 0 finds the first value equal to <code>value</code> (no requirement on sort order, array can contain strings with wildcard '?').</td>

<code>array</code>: 1-D cell range (e.g., <code>A1:A9</code>, or <code>A1:G1</code>)<br /> <code>type</code>: integer</td>

<code>reference</code> is a reference to a cell or range of cells. If omitted, it is assumed that the reference is the cell address in which the ROW function has been entered.</td>

</tbody>

---

From the Free Pascal wiki, page "FPSpreadsheet: List of formulas", as it stood on 2020-11-10 after a change by Wp, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.