FPSpreadsheet: List of formulas
From Lazarus wiki
Jump to navigationJump to searchThis is a list of the formulas supported by FPSpreadsheet.
Note: 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.
Calling prototye | Meaning | Argument types | Arguments | Result type | Not for | |
---|---|---|---|---|---|---|
Mathematical functions | ||||||
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 | ||||||
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 | ||
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 | |
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 | ||
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 ample of numbers | float | >1 | float | ||
VARP(num1 [, num2, ...] ) |
Returns the variance of a population based on an entire population | float | > 1 | float | ||
Date/time functions | ||||||
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/timeinterval 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=7type=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 | ||||||
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 : stringcount : 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 : stringstart_pos, count : integer |
3 | string | ||
REPLACE(text, start_pos, count, new_text) |
Replaces a sequence of characters in a string with another string | text : stringstart_pos, count : integernew_text : string |
4 | string | ||
REPT(text, count) |
Repeats a text a specified number of times | text : stringcount : integer |
2 | string | ||
RIGHT(text [, count]) |
Returns the right-most characters of a string | text : stringcount : 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 : stringnth_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 | ||||||
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 : booleanvalue_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 | ||||||
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/refernce functions | ||||||
HYPERLINK(link [, display_name]) |
Adds a hyperlink | string | 1 or 2 | string (hyperlink) | sfExcel2, sfExcel5 |