Difference between revisions of "FPSpreadsheet: List of formulas"
m |
(→Lookup/reference functions: Add docs for ADDRESS, COLUMN, INDIRECT and ROW formulas.) |
||
(33 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
− | This is a list of the formulas supported by [[FPSpreadsheet]] | + | {{FPSpreadsheet: List of formulas}} |
+ | |||
+ | |||
+ | == 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 == | ||
{| class="wikitable" | {| class="wikitable" | ||
Line 5: | Line 13: | ||
! scope="col" | Meaning | ! scope="col" | Meaning | ||
! scope="col" | Argument types | ! scope="col" | Argument types | ||
− | ! scope="col" | | + | ! scope="col" | Arguments |
! scope="col" | Result type | ! scope="col" | Result type | ||
+ | ! scope="col" | Not for | ||
+ | |- valign="top" | ||
+ | | <tt>ABS(num)</tt> || Returns the absolute value of a number || float || align="center" |1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>ACOS(num)</tt> || Returns the arccosine (in radians) of a number || float (>= -1 and <= +1)|| align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>ACOSH(num)</tt> || Returns the inverse hyperbolic cosine of a number || float (>= 1) || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>ASIN(num)</tt> || Returns the arcsine (in radians) of a number || float (>= -1 and <= +1) || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>ASINH(num)</tt> || Returns the inverse hyperbolic sine of a number || float || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>ATAN(num)</tt> || Returns the arctangent (in radians) of a number || float || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>ATANH(num)</tt> || Returns the inverse hyperbolic tangent of a number || float (> -1 and < +1) || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>CEILING(num,signif)</tt> || Rounds a number up to next multiple of <tt>sign</tt> || float || align="center" |2 || align="center" |float || align="center"|sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>COS(num)</tt> || Returns the cosine of an angle (in radians) || float || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>COSH(num)</tt> || Returns the hyperbolic cosine of a number || float || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>DEGREES(num)</tt> || Converts an angle from radians to degrees || float || align="center" |1 || align="center" |float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>EVEN(num)</tt> || Rounds a pos number up, a neg number down to the next even integer || float || align="center" |1 || align="center"|integer || align="center"|sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>EXP(num)</tt> || Calculates the exponential function of a number || float || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>FACT(num)</tt> || Calculates the factorial of a number || integer || align="center"|1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>FLOOR(num,signif)</tt> || Rounds a number down to next multiple of <tt>sign</tt> || float || align="center" |2 || align="center" |float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>INT(num)</tt> || Returns the integer portion of a number, rounds down || float || align="center" |1 || align="center" |integer || | ||
+ | |- valign="top" | ||
+ | | <tt>LN(num)</tt> || Calculates the natural logarithm of a number || float (> 0) || align="center" |1 || align="center" |float || | ||
+ | |- valign="top" | ||
+ | | <tt>LOG(num [, base])</tt> || Calculates the logarithm of a number to a specified base.<br> <tt>base</tt>, if omitted, is 10. || float (> 0) || align="center" |1 or 2 || align="center" |float | ||
+ | |- valign="top" | ||
+ | | <tt>LOG10(num)</tt> || Calculates the base-10 logarithm of a number || float (> 0) || align="center"|1 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>ODD(num)</tt> || Rounds a pos number up, a neg number down to the next odd integer || float || align="center" |1 || align="center"|integer || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>PI()</tt> || Returns the mathematical constant π (3.14159265358979) || none || align="center"|0 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>POWER(num, exponent)</tt> || Returns the result of a number raised to a given power || float || align="center"|2 || align="center"|float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>RADIANS(num)</tt> || Converts an angle from degrees to radians || float || align="center"|1 or 2 || align="center"|float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>RAND()</tt> || Returns a random number between 0 and 1 || none || align="center"|0 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>ROUND(num, digits)</tt> || Returns a number rounded to a specified number of digits || float || align="center"|2 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>SIGN(num)</tt> || Returns the sign of a number || float || align="center"|1 || align="center"|integer || | ||
+ | |- valign="top" | ||
+ | | <tt>SIN(num)</tt> || Returns the sine of an angle (in radians) || float || align="center"|1 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>SINH(num)</tt> || Returns the hyperbolic sine of a number || float || align="center"|1 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>TAN(num)</tt> || Returns the tangent of an angle (in radians) || float (<> (integer)*π/2) || align="center"|1 || align="center"|float || | ||
+ | |- valign="top" | ||
+ | | <tt>TANH(num)</tt> || Returns the hyperbolic tangent of a number || float || align="center"|1 || align="center"|float || | ||
|- | |- | ||
− | | < | + | |} |
− | |- | + | |
− | | < | + | == Statistical functions == |
+ | |||
+ | {| class="wikitable" | ||
+ | ! scope="col" | Calling prototye | ||
+ | ! scope="col" | Meaning | ||
+ | ! scope="col" | Argument types | ||
+ | ! scope="col" | Arguments | ||
+ | ! scope="col" | Result type | ||
+ | ! scope="col" | Not for | ||
+ | |- valign="top" | ||
+ | | <tt>AVEDEV(num1 [, num2, ...] )</tt> || Average value of absolute deviations of data from their mean. || float || align="center"|> 1 || align="center" | float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>AVERAGE(num1 [, num2, ...] )</tt> || Average value of a series of numbers || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>AVERAGEIF(range, condition [, value_range] )</tt> || Average value of data in value_range if cells in range meet condition || cell ranges (like <tt>A1:D5</tt>)<br /><tt>condition</tt> is value, string or cell || align="center"|2 or 3 || align="center"|float || sfExcel2<br />sfExcel5<br />sfExcel8 | ||
+ | |- valign="top" | ||
+ | | <tt>COUNT(value1 [, value2, ...] )</tt> || Counts cells and arguments containing numbers || any || align="center"|> 1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>COUNTA(value1 [, value2, ...] )</tt> || Counts the number of non-empty cells and arguments || any || align="center"|> 1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>COUNTBLANK(range)</tt> || Counts the number of empty cells in a range || cell range<br />(like <tt>A1:D5</tt>) || align="center"|1 || align="center"|integer || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>COUNTIF(range, condition )</tt> || Counts the cells in range which meet condition || cell range (like <tt>A1:D5</tt>)<br /><tt>condition</tt> is value, string or cell || align="center"|2 || align="center"|integer || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>MAX(num1 [, num2, ...] )</tt> || Returns the largest value from the numbers provided || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>MIN(num1 [, num2, ...] )</tt> || Returns the smallest value from the numbers provided || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>PRODUCT(num1 [, num2, ...] )</tt> || Calculates the product of the numbers provided || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>STDEV(num1 [, num2, ...] )</tt> || Returns the standard deviation of a population based on a ample of numbers || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>STDEVP(num1 [, num2, ...] )</tt> || Returns the standard deviation of a population based on an entire population || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>SUM(num1 [, num2, ...] )</tt> || Calculates the sum of the numbers provided || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>SUMIF(range, condition [, value_range] )</tt> || Adds the data in value_range if cells in range meet condition || cell ranges (like <tt>A1:D5</tt>)<br /><tt>condition</tt> is value, string or cell || align="center"|2 or 3 || align="center"|float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>SUMSQ(num1 [, num2, ...] )</tt> || Returns the sum of the squares of a series of numbers || float || align="center"|> 1 || align="center" | float || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>VAR(num1 [, num2, ...] )</tt> || Returns the variance of a population based on a sample of numbers || float || align="center"|>1 || align="center" | float || | ||
+ | |- valign="top" | ||
+ | | <tt>VARP(num1 [, num2, ...] )</tt> || Returns the variance of a population based on an entire population || float || align="center"|> 1 || align="center" | float || | ||
+ | |- valign="top" | ||
|- | |- | ||
− | | < | + | |} |
+ | |||
+ | == Date/time funtions == | ||
+ | |||
+ | {| class="wikitable" | ||
+ | ! scope="col" | Calling prototye | ||
+ | ! scope="col" | Meaning | ||
+ | ! scope="col" | Argument types | ||
+ | ! scope="col" | Arguments | ||
+ | ! scope="col" | Result type | ||
+ | ! scope="col" | Not for | ||
+ | |- valign="top" | ||
+ | | <tt>DATE(year, month, day)</tt> || Calculates a serial date number from year, month and day || integer || align="center" |3 || align="center" | date/time || | ||
+ | |- valign="top" | ||
+ | | <tt>DATEDIF(start_date, end_date, interval)</tt> || Calculates the difference between two date value based on a given interval || <tt>start_date, end_date</tt>: date/time<br> <tt>interval</tt> is a string:<br><tt>Y</tt> = number of years, <br><tt>M</tt> = number of months, <br><tt>D</tt> = number of days || align="center" |3 || align="center" | integer || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>DATEVALUE(date_string)</tt> || Converts a (date) string to a date/time value. || string || align="center"|1 || align="center" | date/time || | ||
+ | |- valign="top" | ||
+ | | <tt>DAY(value)</tt> || Extracts the day number (1..31) of a date value. || date/time, number, string || align="center"|1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>HOUR(value)</tt> || Extracts the hour (0..23) of a time value. || date/time, number, string || align="center"|1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>MINUTE(value)</tt> || Extracts the minute (0..59) of a time value. || date/time, number, string || align="center"|1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>MONTH(value)</tt> || Extracts the month number (1..12) of a date value. || date/time, number, string || align="center"|1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>NOW()</tt> || Returns the current system date and time. <br> Will refresh whenever the worksheet recalculates. || none || align="center"|0 || align="center" | date/time || | ||
+ | |- valign="top" | ||
+ | | <tt>SECOND(value)</tt> || Extracts the second (0..59) of a time value. || date/time, number, string || align="center"|1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>TIME(year, month, day)</tt> || Calculates a date/time value from hours, minutes and seconds || integer || align="center" |3 || align="center" | date/time || | ||
+ | |- valign="top" | ||
+ | | <tt>TIMEVALUE(time_string)</tt> || Converts a (time) string to a date/time value. || string || align="center"|1 || align="center" | date/time || | ||
+ | |- valign="top" | ||
+ | | <tt>TODAY()</tt> || Returns the current system date || none || align="center"|0 || align="center" | date/time || | ||
+ | |- valign="top" | ||
+ | | <tt>WEEKDAY(value [, type])</tt> || Returns a number code for the weekday of a date || <tt>value</tt>: date/time, number, string <br> <tt>type=0</tt>: Sunday=1, Saturday=7 (default)<br><tt>type=1</tt>: Monday=1, Sunday=7<br><tt>type=2</tt>: Monday=9, Sunday=6 || align="center"|1 or 2 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>YEAR(value)</tt> || Extracts the year of a date value. || date/time, number, string || align="center"|1 || align="center" | integer || | ||
+ | |- valign="top" | ||
|- | |- | ||
− | | <code> | + | |} |
+ | |||
+ | == String functions == | ||
+ | |||
+ | {| class="wikitable" | ||
+ | ! scope="col" | Calling prototye | ||
+ | ! scope="col" | Meaning | ||
+ | ! scope="col" | Argument types | ||
+ | ! scope="col" | Arguments | ||
+ | ! scope="col" | Result type | ||
+ | ! scope="col" | Not for | ||
+ | |- valign="top" | ||
+ | | <tt>CHAR(ascii_value)</tt> || Returns the character based on its ASCII value || integer || align="center" |1 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>CODE(text)</tt> || Returns the ASCII code of the first character of a string || string || align="center" |1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>CONCATENATE(text1 [, text2, ...] )</tt> || Joins strings together || float || align="center"|> 1 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>EXACT(text1, text2)</tt> || Compares two strings (case-sensitive) || float || align="center"|2 || align="center" | boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>LEFT(text [, count])</tt> || Returns the left-most characters of a string || <tt>text</tt>: string<br><tt>count</tt>: integer (default 1) || align="center" |1 or 2 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>LEN(text)</tt> || Returns the character count of a string || string || align="center" |1 || align="center" | integer || | ||
+ | |- valign="top" | ||
+ | | <tt>LOWER(text)</tt> || Converts a string to lower-case characters || string || align="center" |1 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>MID(text, start_pos, count)</tt> || Returns part of a string || <tt>text</tt>: string<br><tt>start_pos, count</tt>: integer || align="center" |3 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>REPLACE(text, start_pos, count, new_text)</tt> || Replaces a sequence of characters in a string with another string || <tt>text</tt>: string<br><tt>start_pos, count</tt>: integer<br><tt>new_text</tt>: string || align="center" |4 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>REPT(text, count)</tt> || Repeats a text a specified number of times || <tt>text</tt>: string<br><tt>count</tt>: integer<br> || align="center"|2 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>RIGHT(text [, count])</tt> || Returns the right-most characters of a string || <tt>text</tt>: string<br><tt>count</tt>: integer (default 1) || align="center" |1 or 2 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>SUBSTITUTE(text, old_text, new_text [, nth_appearance])</tt> || Replaces part of a string with another string || <tt>text, old_text, new_text</tt>: string<br><tt>nth_appearance</tt>: integer<br>(default: replace all) || align="center"|3 or 4 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>TRIM(text)</tt> || Removes leading and trailing spaces from a string || string || align="center"|1 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>UPPER(text)</tt> || Converts a string to upper-case characters || string || align="center"|1 || align="center" | string || | ||
+ | |- valign="top" | ||
+ | | <tt>VALUE(text)</tt> || Converts a string representing a number to a number || string || align="center"|1 || align="center" | float || | ||
|- | |- | ||
− | | < | + | |} |
+ | |||
+ | == Logical functions == | ||
+ | |||
+ | {| class="wikitable" | ||
+ | ! scope="col" | Calling prototye | ||
+ | ! scope="col" | Meaning | ||
+ | ! scope="col" | Argument types | ||
+ | ! scope="col" | Arguments | ||
+ | ! scope="col" | Result type | ||
+ | ! scope="col" | Not for | ||
+ | |- valign="top" | ||
+ | | <tt>AND(condition1 [, condition2, ...])</tt> || Calculates the logical AND of several boolean values || boolean || align="center" |any || align="center" | boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>FALSE()</tt> || Returns the boolean value FALSE || none || align="center"|0 || align="center" | boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>IF(condition, value_true [, value_false])</tt> || Returns <tt>value_true</tt> if <tt>condition</tt> is true,<br> or <tt>value_false</tt> (or false) if <tt>condition</tt> is false || <tt>condition</tt>: boolean<br><tt>value_true, value_false</tt>: any type || align="center"|2 or 3 || align="center" | any type || | ||
+ | |- valign="top" | ||
+ | | <tt>NOT(value)</tt> || Inverts a boolean value || boolean || align="center"|1 || align="center" | boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>OR(condition1 [, condition2, ...])</tt> || Calculates the logical OR of several boolean values || boolean || align="center" |any || align="center" | boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>TRUE()</tt> || Returns the boolean value TRUE || none || align="center"|0 || align="center" | boolean || | ||
+ | |- valign="top" | ||
|- | |- | ||
− | | < | + | |} |
+ | |||
+ | == Info functions == | ||
+ | |||
+ | {| class="wikitable" | ||
+ | ! scope="col" | Calling prototye | ||
+ | ! scope="col" | Meaning | ||
+ | ! scope="col" | Argument types | ||
+ | ! scope="col" | Arguments | ||
+ | ! scope="col" | Result type | ||
+ | ! scope="col" | Not for | ||
+ | |- valign="top" | ||
+ | | <tt>ERROR.TYPE(value)</tt> || 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 || align="center"|1 || align="center"|integer || sfExcel2 | ||
+ | |- valign="top" | ||
+ | | <tt>ISBLANK(value)</tt> || Checks for blank or null values. || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISERR(value)</tt> || Returns TRUE if <tt>value</tt> is an error but not #N/A || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISERROR(value)</tt> || Returns TRUE if <tt>value</tt> is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISLOGICAL(value)</tt> || Returns TRUE if <tt>value</tt> is boolean || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISNA(value)</tt> || Returns TRUE if <tt>value</tt> is a #N/A error || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISNONTEXT(value)</tt> || Returns TRUE if <tt>value</tt> is not a string || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISNUMBER(value)</tt> || Returns TRUE if <tt>value</tt> is a number || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISREF(value)</tt> || Returns TRUE if <tt>value</tt> is a cell reference || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
+ | | <tt>ISTEXT(value)</tt> || Returns TRUE if <tt>value</tt> is a string || any, usually cell || align="center"|1 || align="center"|boolean || | ||
+ | |- valign="top" | ||
|- | |- | ||
− | | | + | |} |
− | |- | + | |
− | | < | + | == Lookup/reference functions == |
− | + | ||
− | + | {| class="wikitable" | |
− | + | ! scope="col" | Calling prototye | |
− | + | ! scope="col" | Meaning | |
− | + | ! scope="col" | Argument types | |
− | + | ! scope="col" | Arguments | |
− | + | ! scope="col" | Result type | |
− | + | ! scope="col" | Not for | |
− | | | + | |
− | | < | + | |- valign="top" |
− | |- | + | | <tt>ADDRESS(row, col [, ref_type] [, ref_style], [sheet_name])</tt> |
− | | < | + | || Returns a text representation of a cell address <br /> |
− | + | <tt>ref_type</tt> is the type of reference to use: 1=absolute (default); 2=relative column, absolute row; 3=absolute column, relative row; 4=relative <br /> | |
− | + | <tt>ref_style</tt> if true (default) means: address in A1 dialect, otherwise in R1C1 <br /> | |
− | |- | + | <tt>sheet_name=name</tt> of the worksheet |
− | | < | + | || 3x integer<br /> boolean <br /> string || align="center"|2 (up to 5) || align="center"|string || sfExcel2 |
− | + | ||
− | + | |- valign="top" | |
− | |- | + | | <tt>COLUMN( [reference] )</tt> |
− | | < | + | || Returns the (1-based) column number of a cell reference. <br /><tt>reference</tt> 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 || align="center"|1 (or 0) || align="center"|integer || |
− | | | + | |
− | + | |- valign="top" | |
− | |- | + | | <tt>HYPERLINK(link [, display_name])</tt> || Adds a hyperlink || string || align="center"|1 or 2 || align="center"|string (hyperlink) || sfExcel2, sfExcel5 |
− | | < | + | |
− | + | |- valign="top" | |
− | + | | <tt>INDIRECT(address)</tt> || Returns cell reference bases on address string || string || align="center"|1 || align="center"|cell || | |
− | + | ||
− | + | |- valign="top" | |
− | + | | <tt>MATCH(value, array [, type])</tt> || Searches for a value in a 1-D array and returns the relative position of that item. <br /> | |
− | + | type = 1 (default) finds the largest value <= <tt>value</tt> (assumes an array in ascending order)<br /> | |
− | + | type = -1 finds the smallest value >= <tt>value</tt> (assumes an array in descending order) <br /> | |
− | + | type = 0 finds the first value equal to <tt>value</tt> (no requirement on sort order, array can contain strings with wildcard '?'). | |
− | |- | + | || <tt>value</tt>: float or text <br /><tt>array</tt>: 1-D cell range (e.g., <tt>A1:A9</tt>, or <tt>A1:G1</tt>)<br /><tt>type</tt>: integer || align="center"|2 or 3 || align="center"|integer || |
− | | < | + | |
− | + | |- valign="top" | |
− | + | | <tt>ROW( [reference] )</tt> | |
+ | || Returns the (1-based) row number of a cell reference. <br /><tt>reference</tt> 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 || align="center"|1 (or 0) || align="center"|integer || | ||
+ | |||
|- | |- | ||
|} | |} |
Latest revision as of 01:05, 11 November 2020
│
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 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
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 |
INDIRECT(address) | Returns cell reference bases 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 |