Difference between revisions of "FPSpreadsheet: List of formulas"

From Lazarus wiki
Jump to navigationJump to search
(Add string function REPT)
(Add formulas EVEN(), EXACT(), and ODD())
Line 31: Line 31:
 
|- valign="top"
 
|- valign="top"
 
| <code>DEGREES(num)</code> || Converts an angle from radians to degrees || float || align="center" |1 || align="center" |float
 
| <code>DEGREES(num)</code> || Converts an angle from radians to degrees || float || align="center" |1 || align="center" |float
 +
|- valign="top"
 +
| <code>EVEN(num)</code> || Rounds a pos number up, a neg number down to the next even integer || float || align="center" |1 || align="center"|integer
 
|- valign="top"
 
|- valign="top"
 
| <code>EXP(num)</code> || Calculates the exponential function of a number || float || align="center" |1 || align="center" |float
 
| <code>EXP(num)</code> || Calculates the exponential function of a number || float || align="center" |1 || align="center" |float
Line 41: Line 43:
 
|- valign="top"
 
|- valign="top"
 
| <code>LOG10(num)</code> || Calculates the base-10 logarithm of a number || float (> 0) || align="center"|1 || align="center"|float
 
| <code>LOG10(num)</code> || Calculates the base-10 logarithm of a number || float (> 0) || align="center"|1 || align="center"|float
 +
|- valign="top"
 +
| <code>ODD(num)</code> || Rounds a pos number up, a neg number down to the next odd integer || float || align="center" |1 || align="center"|integer
 
|- valign="top"
 
|- valign="top"
 
| <code>PI()</code> || Returns the mathematical constant &pi; (3.14159265358979) || none || align="center"|0 || align="center"|float
 
| <code>PI()</code> || Returns the mathematical constant &pi; (3.14159265358979) || none || align="center"|0 || align="center"|float
Line 58: Line 62:
 
| <code>SINH(num)</code> || Returns the hyperbolic sine of a number || float || align="center"|1 || align="center"|float
 
| <code>SINH(num)</code> || Returns the hyperbolic sine of a number || float || align="center"|1 || align="center"|float
 
|- valign="top"
 
|- valign="top"
| <code>TAN(num)</code> || Returns the tangent of an angle (in radians) || float ( <> k*&pi;/2) || align="center"|1 || align="center"|float
+
| <code>TAN(num)</code> || Returns the tangent of an angle (in radians) || float (<> (integer)*&pi;/2) || align="center"|1 || align="center"|float
 
|- valign="top"
 
|- valign="top"
 
| <code>TANH(num)</code> || Returns the hyperbolic tangent of a number || float || align="center"|1 || align="center"|float
 
| <code>TANH(num)</code> || Returns the hyperbolic tangent of a number || float || align="center"|1 || align="center"|float
Line 136: Line 140:
 
|- valign="top"
 
|- valign="top"
 
| <code>CONCATENATE(text1 [, text2, ...] )</code> || Joins strings together || float || align="center"|> 1 || align="center" | string
 
| <code>CONCATENATE(text1 [, text2, ...] )</code> || Joins strings together || float || align="center"|> 1 || align="center" | string
 +
|- valign="top"
 +
| <code>EXACT(text1, text2)</code> || Compares two strings (case-sensitive) || float || align="center"|2 || align="center" | boolean
 
|- valign="top"
 
|- valign="top"
 
| <code>LEFT(text [, count])</code> || Returns the left-most characters of a string || <code>text</code>: string<br><code>count</code>: integer (default 1) || align="center" |1 or 2 || align="center" | string
 
| <code>LEFT(text [, count])</code> || Returns the left-most characters of a string || <code>text</code>: string<br><code>count</code>: integer (default 1) || align="center" |1 or 2 || align="center" | string

Revision as of 22:49, 1 September 2014

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
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
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
EVEN(num) Rounds a pos number up, a neg number down to the next even integer float 1 integer
EXP(num) Calculates the exponential function of a number float 1 float
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
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
RADIANS(num) Converts an angle from degrees to radians float 1 or 2 float
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
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
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
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
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