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

Calling prototye	Meaning	Argument types	Arguments	Result type	Not for
`ABS(num)`	Returns the absolute value of a number	float	1	float
`ACOS(num)`	Returns the arccosine (in radians) of a number	float (>= -1 and <= +1)	1	float
`ACOSH(num)`	Returns the inverse hyperbolic cosine of a number	float (>= 1)	1	float
`ASIN(num)`	Returns the arcsine (in radians) of a number	float (>= -1 and <= +1)	1	float
`ASINH(num)`	Returns the inverse hyperbolic sine of a number	float	1	float
`ATAN(num)`	Returns the arctangent (in radians) of a number	float	1	float
`ATANH(num)`	Returns the inverse hyperbolic tangent of a number	float (> -1 and < +1)	1	float
`CEILING(num,signif)`	Rounds a number up to next multiple of `sign`	float	2	float	sfExcel2
`COS(num)`	Returns the cosine of an angle (in radians)	float	1	float
`COSH(num)`	Returns the hyperbolic cosine of a number	float	1	float
`DEGREES(num)`	Converts an angle from radians to degrees	float	1	float	sfExcel2
`EVEN(num)`	Rounds a pos number up, a neg number down to the next even integer	float	1	integer	sfExcel2
`EXP(num)`	Calculates the exponential function of a number	float	1	float
`FACT(num)`	Calculates the factorial of a number	integer	1	float
`FLOOR(num,signif)`	Rounds a number down to next multiple of `sign`	float	2	float	sfExcel2
`INT(num)`	Returns the integer portion of a number, rounds down	float	1	integer
`LN(num)`	Calculates the natural logarithm of a number	float (> 0)	1	float
`LOG(num [, base])`	Calculates the logarithm of a number to a specified base. · `base`, if omitted, is 10.	float (> 0)	1 or 2	float
`LOG10(num)`	Calculates the base-10 logarithm of a number	float (> 0)	1	float
`ODD(num)`	Rounds a pos number up, a neg number down to the next odd integer	float	1	integer	sfExcel2
`PI()`	Returns the mathematical constant π (3.14159265358979)	none	0	float
`POWER(num, exponent)`	Returns the result of a number raised to a given power	float	2	float	sfExcel2
`RADIANS(num)`	Converts an angle from degrees to radians	float	1 or 2	float	sfExcel2
`RAND()`	Returns a random number between 0 and 1	none	0	float
`ROUND(num, digits)`	Returns a number rounded to a specified number of digits	float	2	float
`SIGN(num)`	Returns the sign of a number	float	1	integer
`SIN(num)`	Returns the sine of an angle (in radians)	float	1	float
`SINH(num)`	Returns the hyperbolic sine of a number	float	1	float
`TAN(num)`	Returns the tangent of an angle (in radians)	float (<> (integer)*π/2)	1	float
`TANH(num)`	Returns the hyperbolic tangent of a number	float	1	float

Statistical functions

Calling prototye	Meaning	Argument types	Arguments	Result type	Not for
`AVEDEV(num1 [, num2, ...] )`	Average value of absolute deviations of data from their mean.	float	> 1	float	sfExcel2
`AVERAGE(num1 [, num2, ...] )`	Average value of a series of numbers	float	> 1	float
`AVERAGEIF(range, condition [, value_range] )`	Average value of data in value_range if cells in range meet condition	cell ranges (like `A1:D5`) · `condition` is value, string or cell	2 or 3	float	sfExcel2 · sfExcel5 · sfExcel8
`COUNT(value1 [, value2, ...] )`	Counts cells and arguments containing numbers	any	> 1	integer
`COUNTA(value1 [, value2, ...] )`	Counts the number of non-empty cells and arguments	any	> 1	integer
`COUNTBLANK(range)`	Counts the number of empty cells in a range	cell range · (like `A1:D5`)	1	integer	sfExcel2
`COUNTIF(range, condition )`	Counts the cells in range which meet condition	cell range (like `A1:D5`) · `condition` is value, string or cell	2	integer	sfExcel2
`MAX(num1 [, num2, ...] )`	Returns the largest value from the numbers provided	float	> 1	float
`MIN(num1 [, num2, ...] )`	Returns the smallest value from the numbers provided	float	> 1	float
`PRODUCT(num1 [, num2, ...] )`	Calculates the product of the numbers provided	float	> 1	float
`STDEV(num1 [, num2, ...] )`	Returns the standard deviation of a population based on a ample of numbers	float	> 1	float
`STDEVP(num1 [, num2, ...] )`	Returns the standard deviation of a population based on an entire population	float	> 1	float
`SUM(num1 [, num2, ...] )`	Calculates the sum of the numbers provided	float	> 1	float
`SUMIF(range, condition [, value_range] )`	Adds the data in value_range if cells in range meet condition	cell ranges (like `A1:D5`) · `condition` is value, string or cell	2 or 3	float	sfExcel2
`SUMSQ(num1 [, num2, ...] )`	Returns the sum of the squares of a series of numbers	float	> 1	float	sfExcel2
`VAR(num1 [, num2, ...] )`	Returns the variance of a population based on a sample of numbers	float	>1	float
`VARP(num1 [, num2, ...] )`	Returns the variance of a population based on an entire population	float	> 1	float

Date/time funtions

Calling prototye	Meaning	Argument types	Arguments	Result type	Not for
`DATE(year, month, day)`	Calculates a serial date number from year, month and day	integer	3	date/time
`DATEDIF(start_date, end_date, interval)`	Calculates the difference between two date value based on a given interval	`start_date, end_date`: date/time · `interval` is a string: · `Y` = number of years, · `M` = number of months, · `D` = number of days	3	integer	sfExcel2
`DATEVALUE(date_string)`	Converts a (date) string to a date/time value.	string	1	date/time
`DAY(value)`	Extracts the day number (1..31) of a date value.	date/time, number, string	1	integer
`HOUR(value)`	Extracts the hour (0..23) of a time value.	date/time, number, string	1	integer
`MINUTE(value)`	Extracts the minute (0..59) of a time value.	date/time, number, string	1	integer
`MONTH(value)`	Extracts the month number (1..12) of a date value.	date/time, number, string	1	integer
`NOW()`	Returns the current system date and time. · Will refresh whenever the worksheet recalculates.	none	0	date/time
`SECOND(value)`	Extracts the second (0..59) of a time value.	date/time, number, string	1	integer
`TIME(year, month, day)`	Calculates a date/time value from hours, minutes and seconds	integer	3	date/time
`TIMEVALUE(time_string)`	Converts a (time) string to a date/time value.	string	1	date/time
`TODAY()`	Returns the current system date	none	0	date/time
`WEEKDAY(value [, type])`	Returns a number code for the weekday of a date	`value`: date/time, number, string · `type=0`: Sunday=1, Saturday=7 (default) · `type=1`: Monday=1, Sunday=7 · `type=2`: Monday=9, Sunday=6	1 or 2	integer
`YEAR(value)`	Extracts the year of a date value.	date/time, number, string	1	integer

String functions

Calling prototye	Meaning	Argument types	Arguments	Result type
`CHAR(ascii_value)`	Returns the character based on its ASCII value	integer	1	string
`CODE(text)`	Returns the ASCII code of the first character of a string	string	1	integer
`CONCATENATE(text1 [, text2, ...] )`	Joins strings together	float	> 1	string
`EXACT(text1, text2)`	Compares two strings (case-sensitive)	float	2	boolean
`LEFT(text [, count])`	Returns the left-most characters of a string	`text`: string · `count`: integer (default 1)	1 or 2	string
`LEN(text)`	Returns the character count of a string	string	1	integer
`LOWER(text)`	Converts a string to lower-case characters	string	1	string
`MID(text, start_pos, count)`	Returns part of a string	`text`: string · `start_pos, count`: integer	3	string
`REPLACE(text, start_pos, count, new_text)`	Replaces a sequence of characters in a string with another string	`text`: string · `start_pos, count`: integer · `new_text`: string	4	string
`REPT(text, count)`	Repeats a text a specified number of times	`text`: string · `count`: integer	2	string
`RIGHT(text [, count])`	Returns the right-most characters of a string	`text`: string · `count`: integer (default 1)	1 or 2	string
`SUBSTITUTE(text, old_text, new_text [, nth_appearance])`	Replaces part of a string with another string	`text, old_text, new_text`: string · `nth_appearance`: integer · (default: replace all)	3 or 4	string
`TRIM(text)`	Removes leading and trailing spaces from a string	string	1	string
`UPPER(text)`	Converts a string to upper-case characters	string	1	string
`VALUE(text)`	Converts a string representing a number to a number	string	1	float

Logical functions

Calling prototye	Meaning	Argument types	Arguments	Result type
`AND(condition1 [, condition2, ...])`	Calculates the logical AND of several boolean values	boolean	any	boolean
`FALSE()`	Returns the boolean value FALSE	none	0	boolean
`IF(condition, value_true [, value_false])`	Returns `value_true` if `condition` is true, · or `value_false` (or false) if `condition` is false	`condition`: boolean · `value_true, value_false`: any type	2 or 3	any type
`NOT(value)`	Inverts a boolean value	boolean	1	boolean
`OR(condition1 [, condition2, ...])`	Calculates the logical OR of several boolean values	boolean	any	boolean
`TRUE()`	Returns the boolean value TRUE	none	0	boolean

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

Calling prototye	Meaning	Argument types	Arguments	Result type	Not for
`ADDRESS(row, col [, ref_type] [, ref_style], [sheet_name])`	\| Returns a text representation of a cell address · `ref_type` is the type of reference to use: 1=absolute (default); 2=relative column, absolute row; 3=absolute column, relative row; 4=relative · `ref_style` if true (default) means: address in A1 dialect, otherwise in R1C1 · `sheet_name=name` of the worksheet	\| 3x integer · boolean · string	2 (up to 5)	string	sfExcel2
`COLUMN( [reference] )`	\| Returns the (1-based) column number of a cell reference. · `reference` 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.	string	1 (or 0)	integer
`HYPERLINK(link [, display_name])`	Adds a hyperlink	string	1 or 2	string (hyperlink)	sfExcel2, sfExcel5
`INDIRECT(address)`	Returns cell reference bases on address string	string	1	cell
`MATCH(value, array [, type])`	Searches for a value in a 1-D array and returns the relative position of that item. · type = 1 (default) finds the largest value <= `value` (assumes an array in ascending order) · type = -1 finds the smallest value >= `value` (assumes an array in descending order) · type = 0 finds the first value equal to `value` (no requirement on sort order, array can contain strings with wildcard '?').	\| `value`: float or text · `array`: 1-D cell range (e.g., `A1:A9`, or `A1:G1`) · `type`: integer	2 or 3	integer
`ROW( [reference] )`	\| Returns the (1-based) row number of a cell reference. · `reference` 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.	string	1 (or 0)	integer

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.

Revision 2, last changed by WikiArchive 11-10-2026.

Edited on wiki.freepascal.org by 3 people, 2014–2020

From the official wiki's backup of 26 May 2024: who edited this page there, and when. Edits made there after that date were not recorded, and the earlier versions are not shown here; the main page says why.

When	Who	What changed
8-30-2014	Wp	Initial version (incomplete)
8-30-2014	Wp	(minor)
8-30-2014	Wp	Complete skeleton of table
8-31-2014	Wp	Complete section on statistical formulas
8-31-2014	Wp	Complete date/time formulas
8-31-2014	Wp	Highlight group headers
8-31-2014	Wp	Style change: vertically align cells to top
8-31-2014	Wp	Complete string functions
8-31-2014	Wp	Complete logical functions
8-31-2014	Wp	Complete info formulas
8-31-2014	Wp	Hint on usage of argument data types
8-31-2014	Wp	Correct COUNTBLANK row
9-1-2014	Wp	Add string function REPT
9-1-2014	Wp	Add formulas EVEN(), EXACT(), and ODD()
9-1-2014	Wp	Add FACT function
9-1-2014	Wp	Add formulas CEILING and FLOOR
9-1-2014	Wp	Add a column on which file formats do not support the formula in the row
9-1-2014	Wp	(minor)
9-3-2014	Wp	Note on relaxed type-checking
9-3-2014	Wp	Add categroy fpspreadsheet
3-6-2015	Wp	Add HYPERLINK
6-7-2015	Wp
6-10-2015	Wp	Replace "code" tokens by "tt"
6-10-2015	Wp	(minor)
6-10-2015	Wp	Reorganize with TOC
7-9-2015	FTurtle
1-27-2016	Wp	Statistical functions: Add COUNTIF, SUMIF, AVERAGEIF
1-27-2016	Wp	(minor)
5-11-2017	Wp	Statistical functions (minor)
9-21-2018	Zoltanleo
9-21-2018	Zoltanleo
9-22-2018	FTurtle	Added template. Category moved to template.
12-24-2018	Wp	Lookup/reference functions: MATCH
12-24-2018	Wp	Lookup/reference functions
12-24-2018	Wp	Lookup/reference functions
11-10-2020	Wp	Lookup/reference functions: Add docs for ADDRESS, COLUMN, INDIRECT and ROW formulas.

What readers said (0)

Talk about this page

Nobody has opened a bench for this page yet. Sign in to open one.