FPSpreadsheet: List of formulas
From Free Pascal wiki
Jump to navigationJump to search
│
English (en) │
русский (ru) │
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 signif | 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 signif | 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, integer | 2 | float | |
| ROUNDDOWN(num, digits) | Returns a number rounded down to a specified number of digits | float, integer | 2 | float | sfExcel2 |
| ROUNDUP(num, digits) | Returns a number rounded up to a specified number of digits | float, integer | 2 | float | sfExcel2 |
| 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 | Not for |
|---|---|---|---|---|---|
| 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 | Not for |
|---|---|---|---|---|---|
| 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 |
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 |
| INDEX(address, row , [col]) | Returns the cell content at the intersection of the given row and colum of the 2D cell range. row and column indices are relative to the cell range (1-based). The last parameter can be omitted when the input array is 1-dimensional (i.e., a single column or row). |
1-D or 2-D cell range integer [integer] |
2 or 3 | (cell value) | |
| INDIRECT(address) | Returns cell reference based 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) |
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 |
