FPSpreadsheet: List of formulas: what changed

Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		## Introduction
2	2
3	3		This is a list of the formulas supported by [[FPSpreadsheet]].
4	4
5	5		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.
6	6
7	7		## Mathematical functions
8	8
9		-	~~<thead>~~
10		-
11		-	~~</thead>~~
12		-	~~<tbody>~~
13		-
14		-	~~<code>base</code>, if omitted, is 10.</td>~~
15		-
16		-	~~</tbody>~~
	9	+	\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
	10	+	\|---\|---\|---\|---\|---\|---\|
	11	+	\| `ABS(num)` \| Returns the absolute value of a number \| float \| 1 \| float \| \|
	12	+	\| `ACOS(num)` \| Returns the arccosine (in radians) of a number \| float (>= -1 and <= +1) \| 1 \| float \| \|
	13	+	\| `ACOSH(num)` \| Returns the inverse hyperbolic cosine of a number \| float (>= 1) \| 1 \| float \| \|
	14	+	\| `ASIN(num)` \| Returns the arcsine (in radians) of a number \| float (>= -1 and <= +1) \| 1 \| float \| \|
	15	+	\| `ASINH(num)` \| Returns the inverse hyperbolic sine of a number \| float \| 1 \| float \| \|
	16	+	\| `ATAN(num)` \| Returns the arctangent (in radians) of a number \| float \| 1 \| float \| \|
	17	+	\| `ATANH(num)` \| Returns the inverse hyperbolic tangent of a number \| float (> -1 and < +1) \| 1 \| float \| \|
	18	+	\| `CEILING(num,signif)` \| Rounds a number up to next multiple of `sign` \| float \| 2 \| float \| sfExcel2 \|
	19	+	\| `COS(num)` \| Returns the cosine of an angle (in radians) \| float \| 1 \| float \| \|
	20	+	\| `COSH(num)` \| Returns the hyperbolic cosine of a number \| float \| 1 \| float \| \|
	21	+	\| `DEGREES(num)` \| Converts an angle from radians to degrees \| float \| 1 \| float \| sfExcel2 \|
	22	+	\| `EVEN(num)` \| Rounds a pos number up, a neg number down to the next even integer \| float \| 1 \| integer \| sfExcel2 \|
	23	+	\| `EXP(num)` \| Calculates the exponential function of a number \| float \| 1 \| float \| \|
	24	+	\| `FACT(num)` \| Calculates the factorial of a number \| integer \| 1 \| float \| \|
	25	+	\| `FLOOR(num,signif)` \| Rounds a number down to next multiple of `sign` \| float \| 2 \| float \| sfExcel2 \|
	26	+	\| `INT(num)` \| Returns the integer portion of a number, rounds down \| float \| 1 \| integer \| \|
	27	+	\| `LN(num)` \| Calculates the natural logarithm of a number \| float (> 0) \| 1 \| float \| \|
	28	+	\| `LOG(num [, base])` \| Calculates the logarithm of a number to a specified base. · `base`, if omitted, is 10. \| float (> 0) \| 1 or 2 \| float \| \|
	29	+	\| `LOG10(num)` \| Calculates the base-10 logarithm of a number \| float (> 0) \| 1 \| float \| \|
	30	+	\| `ODD(num)` \| Rounds a pos number up, a neg number down to the next odd integer \| float \| 1 \| integer \| sfExcel2 \|
	31	+	\| `PI()` \| Returns the mathematical constant π (3.14159265358979) \| none \| 0 \| float \| \|
	32	+	\| `POWER(num, exponent)` \| Returns the result of a number raised to a given power \| float \| 2 \| float \| sfExcel2 \|
	33	+	\| `RADIANS(num)` \| Converts an angle from degrees to radians \| float \| 1 or 2 \| float \| sfExcel2 \|
	34	+	\| `RAND()` \| Returns a random number between 0 and 1 \| none \| 0 \| float \| \|
	35	+	\| `ROUND(num, digits)` \| Returns a number rounded to a specified number of digits \| float \| 2 \| float \| \|
	36	+	\| `SIGN(num)` \| Returns the sign of a number \| float \| 1 \| integer \| \|
	37	+	\| `SIN(num)` \| Returns the sine of an angle (in radians) \| float \| 1 \| float \| \|
	38	+	\| `SINH(num)` \| Returns the hyperbolic sine of a number \| float \| 1 \| float \| \|
	39	+	\| `TAN(num)` \| Returns the tangent of an angle (in radians) \| float (<> (integer)*π/2) \| 1 \| float \| \|
	40	+	\| `TANH(num)` \| Returns the hyperbolic tangent of a number \| float \| 1 \| float \| \|
17	41
18	42		## Statistical functions
19	43
20		-	~~<thead>~~
21		-
22		-	~~</thead>~~
23		-	~~<tbody>~~
24		-
25		-	~~<code>condition</code> is value, string or cell</td>~~
26		-
27		-	~~sfExcel5<br />~~
28		-	~~sfExcel8</td>~~
29		-
30		-	~~(like <code>A1:D5</code>)</td>~~
31		-
32		-	~~<code>condition</code> is value, string or cell</td>~~
33		-
34		-	~~<code>condition</code> is value, string or cell</td>~~
35		-
36		-	~~</tbody>~~
	44	+	\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
	45	+	\|---\|---\|---\|---\|---\|---\|
	46	+	\| `AVEDEV(num1 [, num2, ...] )` \| Average value of absolute deviations of data from their mean. \| float \| > 1 \| float \| sfExcel2 \|
	47	+	\| `AVERAGE(num1 [, num2, ...] )` \| Average value of a series of numbers \| float \| > 1 \| float \| \|
	48	+	\| `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 \|
	49	+	\| `COUNT(value1 [, value2, ...] )` \| Counts cells and arguments containing numbers \| any \| > 1 \| integer \| \|
	50	+	\| `COUNTA(value1 [, value2, ...] )` \| Counts the number of non-empty cells and arguments \| any \| > 1 \| integer \| \|
	51	+	\| `COUNTBLANK(range)` \| Counts the number of empty cells in a range \| cell range · (like `A1:D5`) \| 1 \| integer \| sfExcel2 \|
	52	+	\| `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 \|
	53	+	\| `MAX(num1 [, num2, ...] )` \| Returns the largest value from the numbers provided \| float \| > 1 \| float \| \|
	54	+	\| `MIN(num1 [, num2, ...] )` \| Returns the smallest value from the numbers provided \| float \| > 1 \| float \| \|
	55	+	\| `PRODUCT(num1 [, num2, ...] )` \| Calculates the product of the numbers provided \| float \| > 1 \| float \| \|
	56	+	\| `STDEV(num1 [, num2, ...] )` \| Returns the standard deviation of a population based on a ample of numbers \| float \| > 1 \| float \| \|
	57	+	\| `STDEVP(num1 [, num2, ...] )` \| Returns the standard deviation of a population based on an entire population \| float \| > 1 \| float \| \|
	58	+	\| `SUM(num1 [, num2, ...] )` \| Calculates the sum of the numbers provided \| float \| > 1 \| float \| \|
	59	+	\| `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 \|
	60	+	\| `SUMSQ(num1 [, num2, ...] )` \| Returns the sum of the squares of a series of numbers \| float \| > 1 \| float \| sfExcel2 \|
	61	+	\| `VAR(num1 [, num2, ...] )` \| Returns the variance of a population based on a sample of numbers \| float \| >1 \| float \| \|
	62	+	\| `VARP(num1 [, num2, ...] )` \| Returns the variance of a population based on an entire population \| float \| > 1 \| float \| \|
37	63
38	64		## Date/time funtions
39	65
40		-	~~<thead>~~
41		-
42		-	~~</thead>~~
43		-	~~<tbody>~~
44		-
45		-	~~<code>interval</code> is a string:<br />~~
46		-	~~<code>Y</code> = number of years,<br />~~
47		-	~~<code>M</code> = number of months,<br />~~
48		-	~~<code>D</code> = number of days</td>~~
49		-
50		-	~~Will refresh whenever the worksheet recalculates.</td>~~
51		-
52		-	~~<code>type=0</code>: Sunday=1, Saturday=7 (default)<br />~~
53		-	~~<code>type=1</code>: Monday=1, Sunday=7<br />~~
54		-	~~<code>type=2</code>: Monday=9, Sunday=6</td>~~
55		-
56		-	~~</tbody>~~
	66	+	\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
	67	+	\|---\|---\|---\|---\|---\|---\|
	68	+	\| `DATE(year, month, day)` \| Calculates a serial date number from year, month and day \| integer \| 3 \| date/time \| \|
	69	+	\| `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 \|
	70	+	\| `DATEVALUE(date_string)` \| Converts a (date) string to a date/time value. \| string \| 1 \| date/time \| \|
	71	+	\| `DAY(value)` \| Extracts the day number (1..31) of a date value. \| date/time, number, string \| 1 \| integer \| \|
	72	+	\| `HOUR(value)` \| Extracts the hour (0..23) of a time value. \| date/time, number, string \| 1 \| integer \| \|
	73	+	\| `MINUTE(value)` \| Extracts the minute (0..59) of a time value. \| date/time, number, string \| 1 \| integer \| \|
	74	+	\| `MONTH(value)` \| Extracts the month number (1..12) of a date value. \| date/time, number, string \| 1 \| integer \| \|
	75	+	\| `NOW()` \| Returns the current system date and time. · Will refresh whenever the worksheet recalculates. \| none \| 0 \| date/time \| \|
	76	+	\| `SECOND(value)` \| Extracts the second (0..59) of a time value. \| date/time, number, string \| 1 \| integer \| \|
	77	+	\| `TIME(year, month, day)` \| Calculates a date/time value from hours, minutes and seconds \| integer \| 3 \| date/time \| \|
	78	+	\| `TIMEVALUE(time_string)` \| Converts a (time) string to a date/time value. \| string \| 1 \| date/time \| \|
	79	+	\| `TODAY()` \| Returns the current system date \| none \| 0 \| date/time \| \|
	80	+	\| `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 \| \|
	81	+	\| `YEAR(value)` \| Extracts the year of a date value. \| date/time, number, string \| 1 \| integer \| \|
57	82
58	83		## String functions
59	84
60		-	~~<thead>~~
61		-
62		-	~~</thead>~~
63		-	~~<tbody>~~
64		-
65		-	~~<code>count</code>: integer (default 1)</td>~~
66		-
67		-	~~<code>start_pos, count</code>: integer</td>~~
68		-
69		-	~~<code>start_pos, count</code>: integer<br />~~
70		-	~~<code>new_text</code>: string</td>~~
71		-
72		-	~~<code>count</code>: integer<br />~~
73		-
74		-	~~<code>count</code>: integer (default 1)</td>~~
75		-
76		-	~~<code>nth_appearance</code>: integer<br />~~
77		-	~~(default: replace all)</td>~~
78		-
79		-	~~</tbody>~~
	85	+	\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
	86	+	\|---\|---\|---\|---\|---\|---\|
	87	+	\| `CHAR(ascii_value)` \| Returns the character based on its ASCII value \| integer \| 1 \| string \| \|
	88	+	\| `CODE(text)` \| Returns the ASCII code of the first character of a string \| string \| 1 \| integer \| \|
	89	+	\| `CONCATENATE(text1 [, text2, ...] )` \| Joins strings together \| float \| > 1 \| string \| \|
	90	+	\| `EXACT(text1, text2)` \| Compares two strings (case-sensitive) \| float \| 2 \| boolean \| \|
	91	+	\| `LEFT(text [, count])` \| Returns the left-most characters of a string \| `text`: string · `count`: integer (default 1) \| 1 or 2 \| string \| \|
	92	+	\| `LEN(text)` \| Returns the character count of a string \| string \| 1 \| integer \| \|
	93	+	\| `LOWER(text)` \| Converts a string to lower-case characters \| string \| 1 \| string \| \|
	94	+	\| `MID(text, start_pos, count)` \| Returns part of a string \| `text`: string · `start_pos, count`: integer \| 3 \| string \| \|
	95	+	\| `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 \| \|
	96	+	\| `REPT(text, count)` \| Repeats a text a specified number of times \| `text`: string · `count`: integer \| 2 \| string \| \|
	97	+	\| `RIGHT(text [, count])` \| Returns the right-most characters of a string \| `text`: string · `count`: integer (default 1) \| 1 or 2 \| string \| \|
	98	+	\| `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 \| \|
	99	+	\| `TRIM(text)` \| Removes leading and trailing spaces from a string \| string \| 1 \| string \| \|
	100	+	\| `UPPER(text)` \| Converts a string to upper-case characters \| string \| 1 \| string \| \|
	101	+	\| `VALUE(text)` \| Converts a string representing a number to a number \| string \| 1 \| float \| \|
80	102
81	103		## Logical functions
82	104
83		-	~~<thead>~~
84		-
85		-	~~</thead>~~
86		-	~~<tbody>~~
87		-
88		-	~~or <code>value_false</code> (or false) if <code>condition</code> is false</td>~~
89		-
90		-	~~<code>value_true, value_false</code>: any type</td>~~
91		-
92		-	~~</tbody>~~
	105	+	\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
	106	+	\|---\|---\|---\|---\|---\|---\|
	107	+	\| `AND(condition1 [, condition2, ...])` \| Calculates the logical AND of several boolean values \| boolean \| any \| boolean \| \|
	108	+	\| `FALSE()` \| Returns the boolean value FALSE \| none \| 0 \| boolean \| \|
	109	+	\| `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 \| \|
	110	+	\| `NOT(value)` \| Inverts a boolean value \| boolean \| 1 \| boolean \| \|
	111	+	\| `OR(condition1 [, condition2, ...])` \| Calculates the logical OR of several boolean values \| boolean \| any \| boolean \| \|
	112	+	\| `TRUE()` \| Returns the boolean value TRUE \| none \| 0 \| boolean \| \|
93	113
94	114		## Info functions
95	115
96	116		\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
97	117		\|----\|----\|----\|----\|----\|----\|
98	118		\| `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 \|
99	119		\| `ISBLANK(value)` \| Checks for blank or null values. \| any, usually cell \| 1 \| boolean \| \|
100	120		\| `ISERR(value)` \| Returns TRUE if `value` is an error but not \#N/A \| any, usually cell \| 1 \| boolean \| \|
101	121		\| `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 \| \|
102	122		\| `ISLOGICAL(value)` \| Returns TRUE if `value` is boolean \| any, usually cell \| 1 \| boolean \| \|
103	123		\| `ISNA(value)` \| Returns TRUE if `value` is a \#N/A error \| any, usually cell \| 1 \| boolean \| \|
104	124		\| `ISNONTEXT(value)` \| Returns TRUE if `value` is not a string \| any, usually cell \| 1 \| boolean \| \|
105	125		\| `ISNUMBER(value)` \| Returns TRUE if `value` is a number \| any, usually cell \| 1 \| boolean \| \|
106	126		\| `ISREF(value)` \| Returns TRUE if `value` is a cell reference \| any, usually cell \| 1 \| boolean \| \|
107	127		\| `ISTEXT(value)` \| Returns TRUE if `value` is a string \| any, usually cell \| 1 \| boolean \| \|
108	128		\| \| \| \| \| \| \|
109	129		\| \| \| \| \| \| \|
110	130
111	131		## Lookup/reference functions
112	132
113		-	~~<thead>~~
114		-
115		-	~~</thead>~~
116		-	~~<tbody>~~
117		-
118		-	~~<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 />~~
119		-	~~<code>ref_style</code> if true (default) means: address in A1 dialect, otherwise in R1C1<br />~~
120		-	~~<code>sheet_name=name</code> of the worksheet</td>~~
121		-
122		-	~~boolean<br />~~
123		-	~~string</td>~~
124		-
125		-	~~<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>~~
126		-
127		-	~~type = 1 (default) finds the largest value <= <code>value</code> (assumes an array in ascending order)<br />~~
128		-	~~type = -1 finds the smallest value >= <code>value</code> (assumes an array in descending order)<br />~~
129		-	~~type = 0 finds the first value equal to <code>value</code> (no requirement on sort order, array can contain strings with wildcard '?').</td>~~
130		-
131		-	~~<code>array</code>: 1-D cell range (e.g., <code>A1:A9</code>, or <code>A1:G1</code>)<br />~~
132		-	~~<code>type</code>: integer</td>~~
133		-
134		-	~~<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>~~
135		-
136		-	~~</tbody>~~
	133	+	\| Calling prototye \| Meaning \| Argument types \| Arguments \| Result type \| Not for \|
	134	+	\|---\|---\|---\|---\|---\|---\|
	135	+	\| `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 \|
	136	+	\| `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 \| \|
	137	+	\| `HYPERLINK(link [, display_name])` \| Adds a hyperlink \| string \| 1 or 2 \| string (hyperlink) \| sfExcel2, sfExcel5 \|
	138	+	\| `INDIRECT(address)` \| Returns cell reference bases on address string \| string \| 1 \| cell \| \|
	139	+	\| `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 \| \|
	140	+	\| `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 \| \|
137	141
138	142		---
139	143		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](/wiki-contact).

Back to history