# FPSpreadsheet: List of formulas

Jump to navigationJump to search

## 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
ref_style if true (default) means: address in A1 dialect, otherwise in R1C1
sheet_name=name of the worksheet

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)
type = -1 finds the smallest value >= value (assumes an array in descending order)
type = 0 finds the first value equal to value (no requirement on sort order, array can contain strings with wildcard '?').

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