FPSpreadsheet: List of formulas

From Lazarus wiki
Revision as of 23:34, 1 September 2014 by Wp (talk | contribs) (Add a column on which file formats do not support the formula in the row)
Jump to navigationJump to search

This is a list of the formulas supported by FPSpreadsheet.

Light bulb  Note: The arguments can be constants of the given type, or cells containing values of the given type
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 (like "floor()") 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/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
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
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
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