FunctionsTable of Contents
Calligra Sheets has a huge range of built in mathematical and other functions that can be used in a formula cell.
This chapter holds a brief overview of all supported functions in the following groups:
| Bit Operations |
| Conversion |
| Database |
| Date & Time |
| Engineering |
| Financial |
| Information |
| Logical |
| Lookup & Reference |
| Math |
| Statistical |
| Text |
| Trigonometric |
The BITAND() function performs a bit-wise AND operation for the two integer parameters.
- Syntax
BITAND(value; value)
- Parameters
- Comment: First numberType: Whole number (like 1, 132, 2344)Comment: Second numberType: Whole number (like 1, 132, 2344)
- Examples
BITAND(12;10) returns 8 (because decimal 12 is binary 1100, and decimal 10 is binary 1010; and 1100 "anded" with 1010 is 1000, which is integer 8).
- Related Functions
BITOR BITXOR
The BITLSHIFT() function performs a bit-wise left shift operation of the first parameter. The number of bits to shift by is specified by the second parameter. Note that a negative number of bits to left shift by becomes a right shift.
- Syntax
BITLSHIFT(value; shift size)
- Parameters
- Comment: First numberType: Whole number (like 1, 132, 2344)Comment: Amount to left shift byType: Whole number (like 1, 132, 2344)
- Related Functions
BITLSHIFT
The BITOR() function performs a bit-wise OR operation for the two integer parameters.
- Syntax
BITOR(value; value)
- Parameters
- Comment: First numberType: Whole number (like 1, 132, 2344)Comment: Second numberType: Whole number (like 1, 132, 2344)
- Examples
BITOR(12;10) returns 14 (because decimal 12 is binary 1100, and decimal 10 is binary 1010; and 1100 "ored" with 1010 is 1110, which is integer 14).
- Related Functions
BITAND BITXOR
The BITRSHIFT() function performs a bit-wise right shift operation of the first parameter. The number of bits to shift by is specified by the second parameter. Note that a negative number of bits to right shift by becomes a left shift.
- Syntax
BITRSHIFT(value; shift size)
- Parameters
- Comment: First numberType: Whole number (like 1, 132, 2344)Comment: Amount to right shift byType: Whole number (like 1, 132, 2344)
- Related Functions
BITLSHIFT
The BITXOR() function performs a bit-wise exclusive-OR operation for the two integer parameters.
- Syntax
BITXOR(value; value)
- Parameters
- Comment: First numberType: Whole number (like 1, 132, 2344)Comment: Second numberType: Whole number (like 1, 132, 2344)
- Examples
BITXOR(12;10) returns 6 (because decimal 12 is binary 1100, and decimal 10 is binary 1010; and 1100 "xored" with 1010 is 0110, which is integer 6).
- Related Functions
BITAND BITOR
The ARABIC() function converts a roman numeral into a number.
- Syntax
ARABIC(Numeral)
- Parameters
- Comment: NumeralType: Text
- Examples
ARABIC("IV") returns 4
- Examples
ARABIC("XCIX") returns 99
- Related Functions
ROMAN
The ASCIITOCHAR() function returns the character for each given ASCII code
- Syntax
ASCIITOCHAR(value)
- Parameters
- Comment: The ASCII values to convertType: Whole number (like 1, 132, 2344)
- Examples
ASCIITOCHAR(118) returns "v"
- Examples
ASCIITOCHAR(75; 68; 69) returns "KDE"
The BOOL2INT() function returns an integer value for a given boolean value. This method is intended for using a boolean value in methods which require an integer.
- Syntax
BOOL2INT(value)
- Parameters
- Comment: Bool value to convertType: A truth value (TRUE or FALSE)
- Examples
BOOL2INT(True) returns 1
- Examples
BOOL2INT(False) returns 0
- Related Functions
INT2BOOL
The BOOL2STRING() function returns a string value for a given boolean value. This method is intended for using a boolean in methods which require a string
- Syntax
BOOL2STRING(value)
- Parameters
- Comment: Bool value to convertType: A truth value (TRUE or FALSE)
- Examples
BOOL2STRING(true) returns "True"
- Examples
BOOL2STRING(false) returns "False"
- Examples
upper(BOOL2STRING(find("nan";"banana"))) returns TRUE
The CARX() function returns the X position corresponding to the position of a point in a polar landmark.
The CARY() function returns the Y position corresponding to the position of a point in a polar landmark.
The CHARTOASCII() function returns the ASCII code for the given character.
- Syntax
CHARTOASCII(value)
- Parameters
- Comment: A one character string to convertType: Text
- Examples
CHARTOASCII("v") returns 118
- Examples
CHARTOASCII(r) is an error. The character must be in quotes.
The DECSEX() function converts a double value to a time value.
- Syntax
DECSEX(double)
- Parameters
- Comment: ValueType: Double
- Examples
DECSEX(1.6668) returns 1:40
- Examples
DECSEX(7.8) returns 7:47
The INT2BOOL() function returns a boolean value for a given integer number. This method is intended for using an integer in methods which require a boolean. It only accepts 0 or 1. If any other value is given, false is returned.
- Syntax
INT2BOOL(value)
- Parameters
- Comment: Integer value to convertType: Whole number (like 1, 132, 2344)
- Examples
INT2BOOL(1) returns true
- Examples
INT2BOOL(0) returns false
- Examples
OR(INT2BOOL(1); false) returns true
- Related Functions
BOOL2INT
The NUM2STRING() function returns a string value for a given number. Note that Calligra Sheets can auto-convert numbers to strings if needed, so this function should rarely be needed.
- Syntax
NUM2STRING(value)
- Parameters
- Comment: Number to convert into stringType: A floating point value (like 1.3, 0.343, 253 )
- Examples
NUM2STRING(10) returns "10"
- Examples
NUM2STRING(2.05) returns "2.05"
- Examples
=find("101";NUM2STRING(A1)) (A1 = 2.010102) returns True
- Related Functions
STRING
The POLA() function returns the angle (in radians) corresponding to the position of a point in a cartesian landmark.
The POLR() function returns the radius corresponding to the position of a point in a cartesian landmark.
The ROMAN() function returns the number in Roman format. Only positive whole numbers can be converted. The optional Format argument specifies the level of conciseness, and defaults to 0.
- Syntax
ROMAN(Number)
- Parameters
- Comment: NumberType: Whole number (like 1, 132, 2344)Comment: FormatType: Whole number (like 1, 132, 2344)
- Examples
ROMAN(99) returns "XCIX"
- Examples
ROMAN(-55) returns "Err"
- Related Functions
ARABIC
The SEXDEC() function returns a decimal value. You can also supply a time value.
- Syntax
SEXDEC(time value) or SEXDEC(hours;minutes;seconds)
- Parameters
- Comment: HoursType: Whole number (like 1, 132, 2344)Comment: MinutesType: Whole number (like 1, 132, 2344)Comment: SecondsType: Whole number (like 1, 132, 2344)
- Examples
SEXDEC(1;5;7) returns 1.0852778
- Examples
DECSEX("8:05") returns 8.08333333
The STRING() function returns a string value for a given number. It is the same as the NUM2STRING function.
- Syntax
- Parameters
- Comment: Number to convert into stringType: A floating point value (like 1.3, 0.343, 253 )
- Related Functions
NUM2STRING
Calculates the average in a column of a database specified by a set of conditions for values that are numbers
- Syntax
DAVERAGE(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DAVERAGE(A1:C5; "Salary"; A9:A11)
Counts the cells containing numeric values in a column of a database specified by a set of conditions.
- Syntax
DCOUNT(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DCOUNT(A1:C5; "Salary"; A9:A11)
- Related Functions
DCOUNTA
Counts the cells containing numeric or alphanumeric values in a column of a database specified by a set of conditions.
- Syntax
DCOUNTA(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DCOUNTA(A1:C5; "Salary"; A9:A11)
- Related Functions
DCOUNT
Returns a single value from a column of a database specified by a set of conditions. This function returns an error if no value or more than one value exist.
- Syntax
DGET(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DGET(A1:C5; "Salary"; A9:A11)
Returns the largest value in a column of a database specified by a set of conditions.
- Syntax
DMAX(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DMAX(A1:C5; "Salary"; A9:A11)
- Related Functions
DMIN
Returns the smallest values in a column of a database specified by a set of conditions.
- Syntax
DMIN(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DMIN(A1:C5; "Salary"; A9:A11)
- Related Functions
DMAX
Returns the product of all numeric values in a column of a database specified by a set of conditions.
- Syntax
DPRODUCT(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DPRODUCT(A1:C5; "Salary"; A9:A11)
Returns the estimate of the standard deviation of a population based on a sample using all numeric values in a column of a database specified by a set of conditions.
- Syntax
DSTDEV(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DSTDEV(A1:C5; "Salary"; A9:A11)
- Related Functions
DSTDEVP
Returns the standard deviation of a population based on the entire population using all numeric values in a column of a database specified by a set of conditions.
- Syntax
DSTDEVP(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DSTDEVP(A1:C5; "Salary"; A9:A11)
- Related Functions
DSTDEV
Sums up the numbers in a column of a database specified by a set of conditions.
- Syntax
DSUM(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DSUM(A1:C5; "Salary"; A9:A11)
Returns the estimate of the variance of a population based on a sample using all numeric values in a column of a database specified by a set of conditions.
- Syntax
DVAR(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DVAR(A1:C5; "Salary"; A9:A11)
- Related Functions
DVARP
Returns the variance of a population based on the entire population using all numeric values in a column of a database specified by a set of conditions.
- Syntax
DVARP(Database; "Header"; Conditions)
- Parameters
- Comment: Range marking the databaseType: A range of stringsComment: String marking the column in the databaseType: TextComment: Range marking the conditionsType: A range of strings
- Examples
DVARP(A1:C5; "Salary"; A9:A11)
- Related Functions
DVAR
The CURRENTDATE() function returns the current date. It is equivalent to the TODAY function.
- Syntax
CURRENTDATE()
- Parameters
- Examples
CURRENTDATE() returns "Saturday 13 April 2002"
- Related Functions
CURRENTTIME TODAY
The CURRENTDATETIME() function returns the current date and time.
- Syntax
CURRENTDATETIME()
- Parameters
- Examples
CURRENTDATETIME() returns "Saturday 13 April 2002 19:12:01"
The CURRENTTIME() function returns the current time formatted with local parameters.
- Syntax
CURRENTTIME()
- Parameters
- Examples
CURRENTTIME() returns "19:12:01"
The DATE() function returns the date formatted with local parameters.
- Syntax
DATE(year;month;date)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)Comment: MonthType: Whole number (like 1, 132, 2344)Comment: DayType: Whole number (like 1, 132, 2344)
- Examples
DATE(2000;5;5) returns Friday 05 May 2000
DATE2UNIX() function converts a date and time value to unix time.
A unix time is the number of seconds after midnight January 1st, 1970.
- Syntax
DATE2UNIX(date)
- Parameters
- Comment: DateType: Text
- Examples
DATE2UNIX("01/01/2000") returns 946,684,800
The DATEDIF() function returns the difference between two dates.
Interval must be one of the following: "m": month; "d": days; "y": complete years; "ym": month excluding years; "yd": days excluding years; "md": days excluding months and years
- Syntax
DATEDIF(first date; second date; interval)
- Parameters
- Comment: First dateType: TextComment: Second dateType: TextComment: intervalType: Text
- Examples
DATEDIF(A1;A2;"d") A1 is "1st of January 1995" and A2 is "15th of June 1999" returns number of days 1626
- Examples
DATEDIF(A1;A2;"m") A1 is "1st of January 1995" and A2 is "15th of June 1999" returns number of months 53
The DATEVALUE function returns a number representing the day, i.e the number of days elapsed since December 31, 1899.
- Syntax
DATEVALUE(date)
- Parameters
- Comment: DateType: Text
- Examples
DATEVALUE("2/22/2002") returns 37309
- Related Functions
TIMEVALUE
The DAY functions returns the day of a date. If no parameter is specified the current day gets returned.
The DAYNAME() function returns the name of the day of the week (1..7). In some countries the first day of the week is Monday, while in others the first day of the week is Sunday.
- Syntax
DAYNAME(weekday)
- Parameters
- Comment: Number of day in week (1..7)Type: Whole number (like 1, 132, 2344)
- Examples
DAYNAME(1) returns Monday (if the week starts on Monday)
- Related Functions
WEEKDAY
The DAYOFYEAR() function returns the number of the day in the year (1...365).
- Syntax
DAYOFYEAR(year;month;date)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)Comment: MonthType: Whole number (like 1, 132, 2344)Comment: DayType: Whole number (like 1, 132, 2344)
- Examples
DAYOFYEAR(2000;12;1) returns 336
- Examples
DAYOFYEAR(2000;2;29) returns 60
The DAYS() function returns the difference between two dates in days.
- Syntax
DAYS(date2; date1)
- Parameters
- Comment: First (earlier) date valueType: TextComment: Second date valueType: Text
- Examples
DAYS("2002-02-22"; "2002-02-26") returns 4
The DAYS360() function returns the number of days from date1 to date2 using a 360-day calendar in which all months are assumed to have 30 days. If method is false (default) the US method will be used, the European otherwise.
The function DAYSINMONTH() returns the number of days in the given year and month.
- Syntax
DAYSINMONTH(year;month)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)Comment: MonthType: Whole number (like 1, 132, 2344)
- Examples
DAYSINMONTH(2000;2) returns 29
The function DAYSINYEAR() returns the number of days in the given year.
- Syntax
DAYSINYEAR(year)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)
- Examples
DAYSINYEAR(2000) returns 366
The EASTERSUNDAY() function returns the date which corresponds to Easter Sunday in the year given as the parameter.
- Syntax
EASTERSUNDAY(year)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)
- Examples
EASTERSUNDAY(2003) returns "20th April 2003"
The EDATE functions returns the date that is specified by a given date and a number of months before or after that date.
The EOMONTH functions returns the last day in the month specified by a date and the number of months from that date.
The HOUR functions returns the hour of a time. If no parameter is specified the current hour gets returned.
The HOURS() function returns the value of the hours in a time expression.
- Syntax
HOURS(time)
- Parameters
- Comment: TimeType: Text
- Examples
HOURS("10:5:2") returns 10
The function ISLEAPYEAR() returns True if the given year is leap.
- Syntax
ISLEAPYEAR(year)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)
- Examples
ISLEAPYEAR(2000) returns True
The ISOWEEKNUM() function returns number of the week which the date falls into. Note that this function is compliant with the ISO8601 standard: a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year.
- Syntax
ISOWEEKNUM(date)
- Parameters
- Comment: DateType: Text
- Examples
ISOWEEKNUM(A1) returns 51 when A1 is "21st of Dec".
- Related Functions
WEEKNUM
The MINUTE functions returns the minutes of a time. If no parameter is specified the current minute is returned.
The MINUTES() function returns the value of the minutes in a time expression.
- Syntax
MINUTES(time)
- Parameters
- Comment: TimeType: Text
- Examples
MINUTES("10:5:2") returns 5
The MONTH functions returns the month of a date. If no parameter is specified the current month gets returned.
The MONTHNAME() function returns the name of the month (1...12).
- Syntax
MONTHNAME(number)
- Parameters
- Comment: Number of month (1..12)Type: Whole number (like 1, 132, 2344)
- Examples
MONTHNAME(5) returns May
The MONTHS() function returns the difference between two dates in months.The third parameter indicates the calculation mode: if the mode is 0, MONTHS() returns the maximal possible number of months between those days. If the mode is 1, it only returns the number of complete months in between.
- Syntax
MONTHS(date2; date1; mode)
- Parameters
- Comment: First (earlier) date valueType: TextComment: Second date valueType: TextComment: Calculation modeType: Whole number (like 1, 132, 2344)
- Examples
MONTHS("2002-01-18"; "2002-02-26"; 0) returns 1, because there is 1 month and 8 days in between
- Examples
MONTHS("2002-01-19"; "2002-02-26"; 1) returns 0, because there is not a whole month in between, starting at the first day of the month
The NETWORKDAY() function returns the number of working days between startdate and enddate.
Holidays must be one of the following: number = days to add, a single date or an array of dates.
- Syntax
NETWORKDAY(start date; end date; holidays)
- Parameters
- Comment: Start dateType: TextComment: End dateType: TextComment: HolidaysType: Text
- Examples
NETWORKDAY("01/01/2001";"01/08/2001") returns 5 workdays
- Examples
NETWORKDAY("01/01/2001";"01/08/2001";2) returns 3 workdays
The NOW() function returns the current date and time. It is identical with CURRENTDATETIME and provided for compatibility with other applications.
- Syntax
NOW()
- Parameters
- Examples
NOW() returns "Saturday 13 April 2002 19:12:01"
- Related Functions
CURRENTTIME TODAY
The SECOND functions returns the seconds of a time. If no parameter is specified the current second is returned.
The SECONDS() function returns the value of the seconds in a time expression.
- Syntax
SECONDS(time)
- Parameters
- Comment: TimeType: Text
- Examples
SECONDS("10:5:2") returns 2
The TIME() function returns the time formatted with local parameters.
- Syntax
TIME(hours;minutes;seconds)
- Parameters
- Comment: HoursType: Whole number (like 1, 132, 2344)Comment: MinutesType: Whole number (like 1, 132, 2344)Comment: SecondsType: Whole number (like 1, 132, 2344)
- Examples
TIME(10;2;2) returns 10:02:02
- Examples
TIME(10;70;0) returns 11:10:0
- Examples
TIME(10;-40;0) returns 9:20:0
The TIMEVALUE() function returns a number (between 0 and 1) representing the time of day.
- Syntax
TIMEVALUE(time)
- Parameters
- Comment: TimeType: Text
- Examples
TIMEVALUE("10:05:02") returns 0.42
- Related Functions
DATEVALUE
The TODAY() function returns the current date.
- Syntax
TODAY()
- Parameters
- Examples
TODAY() returns "Saturday 13 April 2002"
- Related Functions
CURRENTTIME NOW
UNIX2DATE() function converts unix time to a date and time value.
A unix time is the number of seconds after midnight January 1st, 1970.
- Syntax
UNIX2DATE(unixtime)
- Parameters
- Comment: UnixtimeType: Whole number (like 1, 132, 2344)
- Examples
UNIX2DATE(0) returns 1970-01-01
The WEEKDAY() function returns the weekday of given date. If the method is 1 (default) WEEKDAY() returns 1 for sunday, 2 for monday,.. If the method is 2, monday is 1, tuesday 2, ... and if the method is 3 WEEKDAY() returns 0 for monday, 1 for tuesday,...
- Syntax
WEEKDAY(date; method)
- Parameters
- Comment: DateType: TextComment: Method (optional)Type: Whole number (like 1, 132, 2344)
- Examples
WEEKDAY("2002-02-22"; 2) returns 5
- Related Functions
DAYNAME
The WEEKNUM() function returns the non-ISO week number in which the date falls into.
- Syntax
WEEKNUM(date; method)
- Parameters
- Comment: DateType: TextComment: Method (optional)Type: Whole number (like 1, 132, 2344)
- Examples
WEEKNUM(A1; 1) returns 11 when A1 is "9th of March 2008". Number of the week in the year, with a week beginning on Sunday (1, this is the default if Method is omitted.)
- Examples
WEEKNUM(A1; 2) returns 10 when A1 is "9th of March 2008". Number of the week in the year, with a week beginning on Monday (2)
- Related Functions
ISOWEEKNUM
The WEEKS() function returns the difference between two dates in weeks.The third parameter indicates the calculation mode: if the mode is 0, WEEKS() returns the maximal possible number of weeks between those days. If the mode is 1, it only returns the number of whole weeks in between.
- Syntax
WEEKS(date2; date1; mode)
- Parameters
- Comment: First (earlier) date valueType: TextComment: Second date valueType: TextComment: Calculation modeType: Whole number (like 1, 132, 2344)
- Examples
WEEKS("2002-02-18"; "2002-02-26"; 0) returns 1, because there is one week and 1 day in between
- Examples
WEEKS("2002-19-02"; "2002-19-02"; 1) returns 0, because there is not a whole week in between, starting at the first day of the week (monday or sunday, depending on your local settings)
The function WEEKSINYEAR() returns the number of weeks in the given year.
- Syntax
WEEKSINYEAR(year)
- Parameters
- Comment: YearType: Whole number (like 1, 132, 2344)
- Examples
WEEKSINYEAR(2000) returns 52
The WORKDAY() function returns the date which is working days from the start date.
Holidays must be one of the following: number = days to add, a single date or an array of dates.
- Syntax
WORKDAY(start date; days; holidays)
- Parameters
- Comment: Start dateType: TextComment: Working daysType: Whole number (like 1, 132, 2344)Comment: HolidaysType: Text
- Examples
if B9 is "01/01/2001", D3 is "01/03/2001", D4 is "01/04/2001" then WORKDAY(B9;2;D3:D4) returns "Fri Jan 5 2001"
The YEAR functions returns the year of a date. If no parameter is specified the current year gets returned.
The YEARFRAC() function returns the number of full days between start date and end date according to the basis.
Basis must be one of the following: 0 = 30/360 US, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360
- Syntax
YEARFRAC(start date; end date; basis)
- Parameters
- Comment: First dateType: TextComment: Second dateType: TextComment: intervalType: Text
The YEARS() function returns the difference between two dates in years. The third parameter indicates the calculation mode: if the mode is 0, YEARS() returns the maximal possible number of years between those days. If the mode is 1, it only returns whole years, starting at the 1st Jan and ending on the 31st Dec.
- Syntax
YEARS(date2; date1; mode)
- Parameters
- Comment: First (earlier) date valueType: TextComment: Second date valueType: TextComment: Calculation modeType: Whole number (like 1, 132, 2344)
- Examples
YEARS("2001-02-19"; "2002-02-26"; 0) returns 1, because there is one year and 7 days in between
- Examples
YEARS("2002-02-19"; "2002-02-26"; 1) returns 0, because there is not a whole year in between, starting at the first day of the year
The BASE() function converts a number from base-10 to a string value in a target base from 2 to 36.
- Syntax
BASE(number;base;prec)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: BaseType: Whole number (like 1, 132, 2344)Comment: MinLengthType: Whole number (like 1, 132, 2344)
- Examples
BASE(128;8) returns "200"
The BESSELI() function returns the modified Bessel function In(x).
The BESSELJ() function returns the Bessel function.
The BESSELK() function returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments.
The BESSELY() function returns the Bessel function, which is also called the Weber function or the Neumann function.
The BIN2DEC() function returns the value formatted as a decimal number.
- Syntax
BIN2DEC(value)
- Parameters
- Comment: The value to convertType: Whole number (like 1, 132, 2344)
- Examples
BIN2DEC("1010") returns 10
- Examples
BIN2DEC("11111") returns 31
The BIN2HEX() function returns the value formatted as a hexadecimal number.
- Syntax
BIN2HEX(value)
- Parameters
- Comment: The value to convertType: TextComment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
BIN2HEX("1010") returns "a"
- Examples
BIN2HEX("11111") returns "1f"
The BIN2OCT() function returns the value formatted as an octal number.
- Syntax
BIN2OCT(value)
- Parameters
- Comment: The value to convertType: TextComment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
BIN2OCT("1010") returns "12"
- Examples
BIN2OCT("11111") returns "37"
The COMPLEX(real;imag) returns a complex number of form x+yi.
- Syntax
COMPLEX(real;imag)
- Parameters
- Comment: Real coefficientType: A floating point value (like 1.3, 0.343, 253 )Comment: Imaginary coefficientType: A floating point value (like 1.3, 0.343, 253 )
- Examples
COMPLEX(1.2;3.4) returns "1.2+3.4i"
- Examples
COMPLEX(0;-1) returns "-i"
The CONVERT() function returns a conversion from one measurement system to another.
Supported mass units: g (gram), sg (pieces), lbm (pound), u (atomic mass), ozm (ounce), stone, ton, grain, pweight (pennyweight), hweight (hundredweight).
Supported distance units: m (meter), in (inch), ft (feet), mi (mile), Nmi (nautical mile), ang (Angstrom), parsec, lightyear.
Supported pressure units: Pa (Pascal), atm (atmosphere), mmHg (mm of Mercury), psi, Torr.
Supported force units: N (Newton), dyn, pound.
Supported energy units: J (Joule), e (erg), c (Thermodynamic calorie), cal (IT calorie), eV (electronvolt), HPh (Horsepower-hour), Wh (Watt-hour), flb (foot-pound), BTU.
Supported power units: W (Watt), HP (horsepower), PS (Pferdestaerke).
Supported magnetism units: T (Tesla), ga (Gauss).
Supported temperature units: C (Celsius), F (Fahrenheit), K (Kelvin).
Supported volume units: l (liter), tsp (teaspoon), tbs (tablespoon), oz (ounce liquid), cup, pt (pint), qt (quart), gal (gallon), barrel, m3 (cubic meter), mi3 (cubic mile), Nmi3 (cubic Nautical mile), in3 (cubic inch), ft3 (cubic foot), yd3 (cubic yard), GRT or regton (gross register ton).
Supported area units: m2 (square meter), mi2 (square mile), Nmi2 (square Nautical mile), in2 (square inch), ft2 (square foot), yd2 (square yard), acre, ha (hectare).
Supported speed units: m/s (meters per second), m/h (meters per hour), mph (miles per hour), kn (knot).
For metric units any of the following prefixes can be used: E (exa, 1E+18), P (peta, 1E+15), T (tera, 1E+12), G (giga, 1E+09), M (mega, 1E+06), k (kilo, 1E+03), h (hecto, 1E+02), e (dekao, 1E+01), d (deci, 1E-01), c (centi, 1E-02), m (milli, 1E-03), u (micro, 1E-06), n (nano, 1E-09), p (pico, 1E-12), f (femto, 1E-15), a (atto, 1E-18).
- Syntax
CONVERT(Number; From Unit; To Unit)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: From unitType: TextComment: To unitType: Text
- Examples
CONVERT(32;"C";"F") equals 89.6
- Examples
CONVERT(3;"lbm";"kg") equals 1.3608
- Examples
CONVERT(7.9;"cal";"J") equals 33.0757
The DEC2BIN() function returns the value formatted as a binary number.
- Syntax
DEC2BIN(value)
- Parameters
- Comment: The value to convertType: Whole number (like 1, 132, 2344)Comment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
DEC2BIN(12) returns "1100"
- Examples
DEC2BIN(55) returns "110111"
The DEC2HEX() function returns the value formatted as a hexadecimal number.
- Syntax
DEC2HEX(value)
- Parameters
- Comment: The value to convertType: Whole number (like 1, 132, 2344)Comment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
DEC2HEX(12) returns "c"
- Examples
DEC2HEX(55) returns "37"
The DEC2OCT() function returns the value formatted as an octal number.
- Syntax
DEC2OCT(value)
- Parameters
- Comment: The value to convertType: Whole number (like 1, 132, 2344)Comment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
DEC2OCT(12) returns "14"
- Examples
DEC2OCT(55) returns "67"
The DELTA() function returns 1 if x equals y, otherwise returns 0. y defaults to 0.
- Syntax
DELTA(x; y)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
DELTA(1.2; 3.4) returns 0
- Examples
DELTA(3; 3) returns 1
- Examples
DELTA(1; TRUE) returns 1
The ERF() function returns the error function. With a single argument, ERF() returns the error function between 0 and that argument.
- Syntax
ERF(Lower limit; Upper limit)
- Parameters
- Comment: Lower limitType: A floating point value (like 1.3, 0.343, 253 )Comment: Upper limitType: A floating point value (like 1.3, 0.343, 253 )
- Examples
ERF(0.4) equals 0.42839236
- Related Functions
ERFC
The ERFC() function returns the complementary error function.
- Syntax
ERFC(Lower limit; Upper limit)
- Parameters
- Comment: Lower limitType: A floating point value (like 1.3, 0.343, 253 )Comment: Upper limitType: A floating point value (like 1.3, 0.343, 253 )
- Examples
ERFC(0.4) equals 0.57160764
- Related Functions
ERF
The GESTEP() function returns 1 if x greater or equals y, otherwise returns 0. y defaults to 0.
- Syntax
GESTEP(x; y)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
GESTEP(1.2; 3.4) returns 0
- Examples
GESTEP(3; 3) returns 1
- Examples
GESTEP(0.4; TRUE) returns 0
- Examples
GESTEP(4; 3) returns 1
The HEX2BIN() function returns the value formatted as a binary number.
- Syntax
HEX2BIN(value)
- Parameters
- Comment: The value to convertType: Text
- Examples
HEX2BIN("a") returns "1010"
- Examples
HEX2BIN("37") returns "110111"
The HEX2DEC() function returns the value formatted as a decimal number.
- Syntax
HEX2DEC(value)
- Parameters
- Comment: The value to convertType: Text
- Examples
HEX2DEC("a") returns 10
- Examples
HEX2DEC("37") returns 55
The HEX2OCT() function returns the value formatted as an octal number.
- Syntax
HEX2OCT(value)
- Parameters
- Comment: The value to convertType: Text
- Examples
HEX2OCT("a") returns "12"
- Examples
HEX2OCT("37") returns "67"
The IMABS(complex number) returns the norm of a complex number of form x+yi.
- Syntax
IMABS(complex number)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMABS("1.2+5i") returns 5.1419
- Examples
IMABS("-i") returns 1
- Examples
IMABS("12") returns 12
The IMAGINARY(string) returns the imaginary coefficient of a complex.
- Syntax
IMAGINARY(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMAGINARY("1.2+3.4i") returns 3.4
- Examples
IMAGINARY("1.2") returns 0
The IMARGUMENT(complex number) returns the argument of a complex number of form x+yi.
- Syntax
IMARGUMENT(complex number)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMARGUMENT("1.2+5i") returns 0.6072
- Examples
IMARGUMENT("-i") returns -1.57079633
- Examples
IMARGUMENT("12") returns "#Div/0"
The IMCONJUGATE(complex number) returns the conjugate of a complex number of form x+yi.
- Syntax
IMCONJUGATE(complex number)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMCONJUGATE("1.2+5i") returns "1.2-5i"
- Examples
IMCONJUGATE("-i") returns "i"
- Examples
IMCONJUGATE("12") returns "12"
The IMCOS(string) returns the cosine of a complex number.
- Syntax
IMCOS(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMCOS("1+i") returns "0.83373-0.988898i"
- Examples
IMCOS("12i") returns 81 377.4
The IMCOSH(string) returns the hyperbolic cosine of a complex number.
- Syntax
IMCOSH(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMCOSH("1+i") returns "0.83373+0.988898i"
- Examples
IMCOSH("12i") returns 0.84358
The IMCOT(string) returns the cotangent of a complex number.
- Syntax
IMCOT(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMCOT("1+i") returns "0.21762-0.86801i"
The IMCSC(string) returns the cosecant of a complex number.
- Syntax
IMCSC(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMCSC("1+i") returns "0.62151-0.30393i"
The IMCSCH(string) returns the hyperbolic cosecant of a complex number.
- Syntax
IMCSCH(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMCSCH("1+i") returns "0.30393-i0.62151"
The IMDIV() returns the division of several complex numbers of form x+yi.
- Syntax
IMDIV(value;value;...)
- Parameters
- Comment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of strings
- Examples
IMDIV(1.2;"3.4+5i") returns "0.111597-0.164114i"
- Examples
IMDIV("12+i";"12-i") returns "0.986207+0.16551i"
The IMEXP(string) returns the exponential of a complex number.
- Syntax
IMEXP(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMEXP("2-i") returns "3.99232-6.21768i"
- Examples
IMEXP("12i") returns "0.843854-0.536573i"
The IMLN(string) returns the natural logarithm of a complex number.
- Syntax
IMLN(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMLN("3-i") returns "1.15129-0.321751i"
- Examples
IMLN("12") returns 2.48491
The IMLOG10(string) returns the base-10 logarithm of a complex number.
- Syntax
IMLOG10(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMLOG10("3+4i") returns "0.69897+0.402719i"
The IMLOG2(string) returns the base-2 logarithm of a complex number.
- Syntax
IMLOG2(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMLOG2("3+4i") returns "2.321928+1.337804i"
The IMPOWER(string) returns a complex number raised to a power.
- Syntax
IMPOWER(string)
- Parameters
- Comment: Complex numberType: TextComment: PowerType: Whole number (like 1, 132, 2344)
- Examples
IMPOWER("4-i";2) returns "15-8i"
- Examples
IMPOWER("1.2";2) returns 1.44
The IMPRODUCT() returns the product of several complex numbers of form x+yi.
- Syntax
IMPRODUCT(value;value;...)
- Parameters
- Comment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of strings
- Examples
IMPRODUCT(1.2;"3.4+5i") returns "4.08+6i"
- Examples
IMPRODUCT(1.2;"1i") returns "+1.2i"
The IMREAL(string) returns the real coefficient of a complex.
- Syntax
IMREAL(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMREAL("1.2+3.4i") returns 1.2
- Examples
IMREAL("1.2i") returns 0
The IMSEC(string) returns the secant of a complex number.
- Syntax
IMSEC(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMSEC("1+i") returns "0.49833+i0.59108"
The IMSECH(string) returns the hyperbolic secant of a complex number.
- Syntax
IMSECH(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMSECH("1+i") returns "0.49833-i0.59108"
The IMSIN(string) function returns the sine of a complex number.
- Syntax
IMSIN(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMSIN("1+i") returns "1.29846+0.634964i"
- Examples
IMSIN("1.2") returns -0.536573
The IMSINH(string) function returns the hyperbolic sine of a complex number.
- Syntax
IMSINH(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMSINH("1+i") returns "0.63496+1.29846i"
- Examples
IMSINH("1.2") returns 1.50946
The IMSQRT(string) returns the square root of a complex number.
- Syntax
IMSQRT(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMSQRT("1+i") returns "1.09868+0.45509i"
- Examples
IMSQRT("1.2i") returns "0.774597+0.774597i"
The IMSUB() returns the difference of several complex numbers of form x+yi.
- Syntax
IMSUB(value;value;...)
- Parameters
- Comment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of strings
- Examples
IMSUB(1.2;"3.4+5i") returns "-2.2-5i"
- Examples
IMSUB(1.2;"1i") returns "1.2-i"
The IMSUM() returns the sum of several complex numbers of form x+yi.
- Syntax
IMSUM(value;value;...)
- Parameters
- Comment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of stringsComment: Complex numberType: A range of strings
- Examples
IMSUM(1.2;"3.4+5i") returns "4.6+5i"
- Examples
IMSUM(1.2;"1i") returns "1.2+i"
The IMTAN(string) function returns the tangent of a complex number.
- Syntax
IMTAN(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMTAN("1+i") returns "0.27175+1.08392i"
- Examples
IMTAN("1.2") returns 2.57215
The IMTANH(string) function returns the hyperbolic tangent of a complex number.
- Syntax
IMTANH(string)
- Parameters
- Comment: Complex numberType: Text
- Examples
IMTANH("1+i") returns "1.08392+0.27175i"
- Examples
IMTANH("1.2") returns 0.83365
The OCT2BIN() function returns the value formatted as a binary number.
- Syntax
OCT2BIN(value)
- Parameters
- Comment: The value to convertType: TextComment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
OCT2BIN("12") returns "1010"
- Examples
OCT2BIN("55") returns "101101"
The OCT2DEC() function returns the value formatted as a decimal number.
- Syntax
OCT2DEC(value)
- Parameters
- Comment: The value to convertType: Text
- Examples
OCT2DEC("12") returns 10
- Examples
OCT2DEC("55") returns 45
The OCT2HEX() function returns the value formatted as a hexadecimal number.
- Syntax
OCT2HEX(value)
- Parameters
- Comment: The value to convertType: TextComment: The minimum length of the outputType: Whole number (like 1, 132, 2344)
- Examples
OCT2HEX("12") returns "A"
- Examples
OCT2HEX("55") returns "2D"
The ACCRINT function returns accrued interest for a security which pays periodic interest. Allowed frequencies are 1 - annual, 2 - semi-annual or 4 - quarterly. Basis is the type of day counting you want to use: 0: US 30/360 (default), 1: real days, 2: real days/360, 3: real days/365 or 4: European 30/365.
- Syntax
ACCRINT(issue; first interest; settlement; rate; par; frequency; basis)
- Parameters
- Comment: Issue dateType: DateComment: First interestType: DateComment: SettlementType: DateComment: Annual rate of securityType: A floating point value (like 1.3, 0.343, 253 )Comment: Par valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Number of payments per yearType: A floating point value (like 1.3, 0.343, 253 )Comment: Day counting basisType: Whole number (like 1, 132, 2344)
- Examples
ACCRINT("2/28/2001"; "8/31/2001"; "5/1/2001"; 0.1; 1000; 2; 0) returns 16,944
- Related Functions
ACCRINTM
The ACCRINTM function returns accrued interest for a security which pays interests at maturity date. Basis is the type of day counting you want to use: 0: US 30/360 (default), 1: real days, 2: real days/360, 3: real days/365 or 4: European 30/365.
- Syntax
ACCRINTM(issue; settlement; rate; par; basis)
- Parameters
- Comment: Issue dateType: DateComment: SettlementType: DateComment: Annual rate of securityType: A floating point value (like 1.3, 0.343, 253 )Comment: Par valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Day counting basisType: Whole number (like 1, 132, 2344)
- Examples
ACCRINTM("2/28/2001"; "8/31/2001"; 0.1; 100) returns 5.0278
- Related Functions
ACCRINT
The AMORDEGRC function calculates the amortization value for the French accounting system using degressive depreciation.
- Syntax
AMORDEGRC( Cost; purchaseDate; firstPeriodEndDate; salvage; period; rate; basis)
- Parameters
- Comment: CostType: A floating point value (like 1.3, 0.343, 253 )Comment: PvType: A floating point value (like 1.3, 0.343, 253 )Comment: FvType: A floating point value (like 1.3, 0.343, 253 )
- Examples
AMORDEGRC( 1000; "2006-02-01"; "2006-12-31"; 10; 0; 0.1; 1 ) returns 228
- Related Functions
AMORLINC DB DDB YEARFRAC
The AMORLINC function calculates the amortization value for the French accounting system using linear depreciation.
- Syntax
AMORLINC( Cost; purchaseDate; firstPeriodEndDate; salvage; period; rate; basis)
- Parameters
- Comment: PType: Whole number (like 1, 132, 2344)Comment: PvType: A floating point value (like 1.3, 0.343, 253 )Comment: FvType: A floating point value (like 1.3, 0.343, 253 )
- Examples
AMORLINC( 1000; "2004-02-01"; "2004-12-31"; 10; 0; 0.1; 1 ) returns 91.256831
- Related Functions
AMORDEGRC DB DDB YEARFRAC
The COMPOUND() function returns the value of an investment, given the principal, nominal interest rate, compounding frequency and time. For example: $5000 at 12% interest compounded quarterly for 5 years will become COMPOUND(5000;0.12;4;5) or $9030.56.
- Syntax
COMPOUND(initial;interest;periods;periods_per_year)
- Parameters
- Comment: PrincipalType: A floating point value (like 1.3, 0.343, 253 )Comment: Interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: Periods per yearType: A floating point value (like 1.3, 0.343, 253 )Comment: YearsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
COMPOUND(5000;0.12;4;5) equals 9030.56
The CONTINUOUS() function calculates the return on continuously compounded interest, given the principal, nominal rate and time in years. For example: $1000 earning 10% for 1 year becomes CONTINUOUS(1000;.1;1) or $1105.17.
- Syntax
CONTINOUS(principal;interest;years)
- Parameters
- Comment: PrincipalType: A floating point value (like 1.3, 0.343, 253 )Comment: Interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: YearsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
CONTINUOUS(1000;0.1;1) equals 1105.17
The COUPNUM function returns the number of coupons to be paid between the settlement and the maturity. Basis is the type of day counting you want to use: 0: US 30/360 (default), 1: real days, 2: real days/360, 3: real days/365 or 4: European 30/365.
- Syntax
COUPNUM(settlement; maturity; frequency; basis)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: FrequencyType: A floating point value (like 1.3, 0.343, 253 )Comment: Day counting basisType: Whole number (like 1, 132, 2344)
- Examples
COUPNUM("2/28/2001"; "8/31/2001"; 2; 0) returns 1
Calculates the cumulative interest payment.
- Syntax
CUMIPMT(rate, periods, value, start, end, type)
- Parameters
- Comment: rateType: A floating point value (like 1.3, 0.343, 253 )Comment: periodsType: A floating point value (like 1.3, 0.343, 253 )Comment: valueType: A floating point value (like 1.3, 0.343, 253 )Comment: startType: Whole number (like 1, 132, 2344)Comment: endType: Whole number (like 1, 132, 2344)Comment: typeType: Whole number (like 1, 132, 2344)
- Examples
CUMIPMT( 0.06/12; 5*12; 100000; 5; 12; 0 ) equals -3562,187023
- Related Functions
IPMT CUMPRINC
Calculates the cumulative principal payment.
- Syntax
CUMPRINC(rate, periods, value, start, end, type)
- Parameters
- Comment: rateType: A floating point value (like 1.3, 0.343, 253 )Comment: periodsType: A floating point value (like 1.3, 0.343, 253 )Comment: valueType: A floating point value (like 1.3, 0.343, 253 )Comment: startType: Whole number (like 1, 132, 2344)Comment: endType: Whole number (like 1, 132, 2344)Comment: typeType: Whole number (like 1, 132, 2344)
- Examples
CUMPRINC( 0.06/12; 5*12; 100000; 5; 12; 0 ) equals -11904.054201
- Related Functions
PPMT CUMIPMT
The DB() function will calculate the depreciation of an asset for a given period using the fixed-declining balance method. Month is optional, if omitted it is assumed to be 12.
- Syntax
DB(cost; salvage value; life; period [;month])
- Parameters
- Comment: CostType: A floating point value (like 1.3, 0.343, 253 )Comment: SalvageType: A floating point value (like 1.3, 0.343, 253 )Comment: LifeType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodType: A floating point value (like 1.3, 0.343, 253 )Comment: MonthType: A floating point value (like 1.3, 0.343, 253 )
- Examples
DB(8000;400;6;3) equals 1158.40
- Examples
DB(8000;400;6;3;2) equals 1783.41
- Related Functions
DDB SLN
The DDB() function calculates the depreciation of an asset for a given period using the arithmetic-declining method. The factor is optional, if omitted it is assumed to be 2. All the parameter must be greater than zero.
- Syntax
DDB(cost; salvage value; life; period [;factor])
- Parameters
- Comment: CostType: A floating point value (like 1.3, 0.343, 253 )Comment: SalvageType: A floating point value (like 1.3, 0.343, 253 )Comment: LifeType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodType: A floating point value (like 1.3, 0.343, 253 )Comment: FactorType: A floating point value (like 1.3, 0.343, 253 )
- Examples
DDB(75000;1;60;12;2) returns 1721.81
- Related Functions
SLN
The DISC function returns the discount rate for a security. Basis is the type of day counting you want to use: 0: US 30/360 (default), 1: real days, 2: real days/360, 3: real days/365 or 4: European 30/365.
- Syntax
DISC(settlement; maturity; par; redemption [; basis ] )
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: Price per $100 face valueType: A floating point value (like 1.3, 0.343, 253 )Comment: RedemptionType: A floating point value (like 1.3, 0.343, 253 )Comment: Day counting basisType: Whole number (like 1, 132, 2344)
- Examples
DISC("2/28/2001"; "8/31/2001"; 12; 14) returns 0.2841
- Related Functions
YEARFRAC
The DOLLARDE() function returns a dollar price expressed as a decimal number. The fractional dollar is the number to be converted and the fraction is the denominator of the fraction
The DOLLARFR() function returns a dollar price expressed as a fraction. The decimal dollar is the number to be converted and the fraction is the denominator of the fraction
Returns the number of periods needed for an investment to retain a desired value.
- Syntax
DURATION(rate; pv; fv)
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: Present value (PV)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Future value (FV)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
DURATION(0.1; 1000; 2000) returns 7.27
- Related Functions
FV PV
Returns the Macauley duration of a fixed interest security in years.
- Syntax
DURATION_ADD(Settlement; Maturity; Coupon; Yield; Frequency; Basis)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: CouponType: A floating point value (like 1.3, 0.343, 253 )Comment: YieldType: A floating point value (like 1.3, 0.343, 253 )Comment: FrequencyType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
DURATION_ADD( "1998-01-01"; "2006-01-01"; 0.08; 0.09; 2; 1 ) returns 5.9937749555
- Related Functions
MDURATION
The EFFECT() function calculates the effective yield for a nominal interest rate (annual rate or APR). For example: 8% interest compounded monthly provides an effective yield of EFFECT(.08;12) or 8.3%.
The EFFECTIVE() function calculates the effective yield for a nominal interest rate (annual rate or APR). It is the same as the EFFECT function.
- Syntax
EFFECTIVE(nominal;periods)
- Parameters
- Comment: Nominal interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodsType: A floating point value (like 1.3, 0.343, 253 )
- Related Functions
EFFECT
The EURO() function converts one Euro to a given national currency in the European monetary union. Currency is one of the following: ATS (Austria), BEF (Belgium), DEM (Germany), ESP (Spain), EUR (Euro), FIM (Finland), FRF (France), GRD (Greece), IEP (Ireland), ITL (Italy), LUF (Luxembourg), NLG (Netherlands), or PTE (Portugal).
- Syntax
EURO(currency)
- Parameters
- Comment: CurrencyType: Text
- Examples
EURO("DEM") equals 1.95583
- Related Functions
EUROCONVERT
The EUROCONVERT() function converts a number from one national currency to another currency in the European monetary union by using EURO an intermediary. Currency is one of the following: ATS (Austria), BEF (Belgium), DEM (Germany), ESP (Spain), EUR (Euro), FIM (Finland), FRF (France), GRD (Greece), IEP (Ireland), ITL (Italy), LUF (Luxembourg), NLG (Netherlands), or PTE (Portugal).
- Syntax
EUROCONVERT(number; source currency; target currency)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Source currencyType: TextComment: Target currencyType: Text
- Examples
EUROCONVERT(1; "EUR"; "DEM") equals 1.95583
- Related Functions
EURO
The FV() function returns the future value of an investment, given the yield and the time elapsed. If you have $1000 in a bank account earning 8% interest, after two years you will have FV(1000;0.08;2) or $1166.40.
- Syntax
FV(present value;yield;periods)
- Parameters
- Comment: Present valueType: A floating point value (like 1.3, 0.343, 253 )Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FV(1000;0.08;2) equals 1166.40
- Related Functions
PV NPER PMT RATE
The FV_ANNUITY() function returns the future value of a stream of payments given the amount of the payment, the interest rate and the number of periods. For example: If you receive $500 per year for 20 years, and invest it at 8%, the total after 20 years will be FV_annuity(500;0.08;20) or $22,880.98. This function assumes that payments are made at the end of each period.
- Syntax
FV_ANNUITY(amount;interest;periods)
- Parameters
- Comment: Payment per periodType: A floating point value (like 1.3, 0.343, 253 )Comment: Interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FV_ANNUITY(1000;0.05;5) equals 5525.63
The INTRATE function returns the interest rate for a fully invested security. Basis is the type of day counting you want to use: 0: US 30/360 (default), 1: real days, 2: real days/360, 3: real days/365 or 4: European 30/365.
- Syntax
INTRATE(settlement; maturity; investment; redemption; basis)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: InvestmentType: A floating point value (like 1.3, 0.343, 253 )Comment: RedemptionType: A floating point value (like 1.3, 0.343, 253 )Comment: Day counting basisType: Whole number (like 1, 132, 2344)
- Examples
INTRATE("2/28/2001"; "8/31/2001"; 1000000; 2000000; 1) returns 1.98
IPMT calculates the amount of a payment of an annuity going towards interest.
Rate is the periodic interest rate.
Period is the amortizement period. 1 for the first and NPER for the last period.
NPER is the total number of periods during which annuity is paid.
PV is the present value in the sequence of payments.
FV (optional) is the desired (future) value. default: 0.
Type (optional) defines the due date. 1 for payment at the beginning of a period and 0 (default) for payment at the end of a period.
The example shows the interest to pay in the last year of a three year loan. The interest rate is 10 percent.
- Syntax
IPMT(Rate; Period; NPer; PV; FV; Type)
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodType: A floating point value (like 1.3, 0.343, 253 )Comment: Number of periodsType: A floating point value (like 1.3, 0.343, 253 )Comment: Present valuesType: A floating point value (like 1.3, 0.343, 253 )Comment: Future value (optional)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Type (optional)Type: Whole number (like 1, 132, 2344)
- Examples
IPMT(0.1;3;3;8000) equals -292.45
- Related Functions
PPMT PV PMT
The IRR function calculates the internal rate of return for a series of cash flows.
- Syntax
IRR( Values[; Guess = 0.1 ] )
- Parameters
- Comment: ValuesType: A floating point value (like 1.3, 0.343, 253 )Comment: GuessType: A floating point value (like 1.3, 0.343, 253 )
- Related Functions
XIRR
Calculates the interest paid on a given period of an investment.
Rate is the periodic interest rate.
Period is the amortizement period. 1 for the first and NPer for the last period.
NPer is the total number of periods during which annuity is paid.
PV is the present value in the sequence of payments.
- Syntax
ISPMT(Rate; Period; NPer; PV)
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodType: Whole number (like 1, 132, 2344)Comment: Number of periodsType: Whole number (like 1, 132, 2344)Comment: Present values (PV)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ISPMT(0.1; 1; 3; 8000000) equals -533333
- Related Functions
PV FV NPER PMT RATE
The LEVEL_COUPON() function calculates the value of a level-coupon bond. For example: if the interest rate is 10%, a $1000 bond with semi-annual coupons at a rate of 13% that matures in 4 years is worth LEVEL_COUPON(1000;.13;2;4;.1) or $1096.95.
- Syntax
LEVEL_COUPON(face value;coupon rate;coupons per year;years;market rate)
- Parameters
- Comment: Face valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Coupon rateType: A floating point value (like 1.3, 0.343, 253 )Comment: Coupons per yearType: A floating point value (like 1.3, 0.343, 253 )Comment: YearsType: A floating point value (like 1.3, 0.343, 253 )Comment: Market interest rateType: A floating point value (like 1.3, 0.343, 253 )
- Examples
LEVEL_COUPON(1000;.13;2;4;.1) equals 1096.95
The MDURATION() function will calculate the modified Macauley duration of a fixed interest security in years.
- Syntax
MDURATION( Settlement; Maturity; Coupon; Yield; Frequency; [ Basis=0 ])
- Parameters
- Comment: SettlementType: A floating point value (like 1.3, 0.343, 253 )Comment: MaturityType: A floating point value (like 1.3, 0.343, 253 )Comment: CouponType: A floating point value (like 1.3, 0.343, 253 )Comment: YieldType: A floating point value (like 1.3, 0.343, 253 )Comment: FrequencyType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
MDURATION("2004-02-01"; "2004-05-31"; 0.08; 0.09; 2; 0) returns 0.316321106
- Related Functions
DURATION
The MIRR() function will calculate the modified internal rate of return (IRR) of a series of periodic investments.
- Syntax
MIRR(values; investment; reinvestment)
- Parameters
- Comment: ValuesType: A floating point value (like 1.3, 0.343, 253 )Comment: InvestmentType: A floating point value (like 1.3, 0.343, 253 )Comment: ReinvestmentType: A floating point value (like 1.3, 0.343, 253 )
- Examples
MIRR({100;200;-50;300;-200}, 5%, 6%) equals 34.2823387842%
- Related Functions
IRR
The NOMINAL() function calculates the nominal (stated) interest rate for an effective (annualized) interest rate compounded at given intervals. For example: to earn 8% on an account compounded monthly, you need a return of NOMINAL(.08;12) or 7.72%.
- Syntax
NOMINAL(effective;periods)
- Parameters
- Comment: Effective interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
NOMINAL(0.08;12) equals 0.0772
- Related Functions
EFFECT
Returns the number of periods of an investment.
- Syntax
NPER(rate;payment;pv;fv;type)
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: PaymentType: A floating point value (like 1.3, 0.343, 253 )Comment: Present value (PV)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Future value (FV - optional)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Type (optional)Type: Whole number (like 1, 132, 2344)
- Examples
NPER(0.1; -100; 1000) equals 11
- Examples
NPER(0.06; 0; -10000; 20000 ;0) returns 11.906
- Related Functions
FV RATE PMT PV
The net present value (NPV) for a series of periodic cash flows.
Computes the net present value for a series of periodic cash flows with the discount rate Rate. Values should be positive if they are received as income, and negative if the amounts are expenditure.
The ODDLPRICE function calculates the value of the security per 100 currency units of face value. The security has an irregular last interest date.
- Syntax
ODDLPRICE( Settlement; Maturity; Last; Rate; AnnualYield; Redemption; Frequency [; Basis = 0 ] )
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: LastType: DateComment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: AnnualYieldType: A floating point value (like 1.3, 0.343, 253 )Comment: RedemptionType: A floating point value (like 1.3, 0.343, 253 )Comment: FrequencyType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;100;2) returns 90.991042345
The ODDLYIELD function calculates the yield of the security which has an irregular last interest date.
- Syntax
ODDLYIELD( Settlement; Maturity; Last; Rate; Price; Redemption; Frequency [; Basis = 0 ] )
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: LastType: DateComment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: PriceType: A floating point value (like 1.3, 0.343, 253 )Comment: RedemptionType: A floating point value (like 1.3, 0.343, 253 )Comment: FrequencyType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;91;100;2) returns 4.997775351
- Related Functions
ODDLPRICE
PMT returns the amount of payment for a loan based on a constant interest rate and constant payments (each payment is equal amount).
- Syntax
PMT(rate; nper ; pv [; fv = 0 [; type = 0 ]] )
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: Number of periods (NPer)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Present value (PV)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Future value (FV - optional)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Type (optional)Type: Whole number (like 1, 132, 2344)
- Examples
PMT(0.1; 4; 10000) equals -3154.71
- Related Functions
NPER IPMT PPMT PV
PPMT calculates the amount of a payment of an annuity going towards principal.
Rate is the periodic interest rate.
Period is the amortizement period. 1 for the first and NPER for the last period.
NPER is the total number of periods during which annuity is paid.
PV is the present value in the sequence of payments.
FV (optional) is the desired (future) value. default: 0.
Type (optional) defines the due date. 1 for payment at the beginning of a period and 0 (default) for payment at the end of a period.
- Syntax
PPMT(Rate; Period; NPer; PV [; FV = 9 [; Type = 0 ]] )
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodType: A floating point value (like 1.3, 0.343, 253 )Comment: Number of periodsType: A floating point value (like 1.3, 0.343, 253 )Comment: Present valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Future value (optional)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Type (optional)Type: Whole number (like 1, 132, 2344)
- Examples
PPMT(0.0875;1;36;5000;8000;1) equals -18.48
- Related Functions
IPMT PMT PV
PRICEMAT Calculate the price per 100 currency units of face value of the security that pays interest on the maturity date.
Basis Calculation method
0 US method, 12 months, each month with 30 days
1 Actual number of days in year, actual number of days in months
2 360 days in a year, actual number of days in months
4 365 days in a year, actual number of days in months
5 European method, 12 months, each month has 30 days
- Syntax
PRICEMAT(settlement; maturity; issue; rate; yield [; basis = 0 ] )
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: IssueType: DateComment: Discount rateType: A floating point value (like 1.3, 0.343, 253 )Comment: YieldType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5%) returns 103.819218241
The PV() function returns the present value of an investment -- the value today of a sum of money in the future, given the rate of interest or inflation. For example if you need $1166.40 for your new computer and you want to buy it in two years while earning 8% interest, you need to start with PV(1166.4;0.08;2) or $1000.
- Syntax
PV(future value;rate;periods)
- Parameters
- Comment: Future valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
PV(1166.4;0.08;2) equals 1000
The PV_ANNUITY() function returns the present value of an annuity or stream of payments. For example: a "million dollar" lottery ticket that pays $50,000 a year for 20 years, with an interest rate of 5%, is actually worth PV_ANNUITY(50000;0.05;20) or $623,111. This function assumes that payments are made at the end of each period.
- Syntax
PV_ANNUITY(amount;interest;periods)
- Parameters
- Comment: Payment per periodType: A floating point value (like 1.3, 0.343, 253 )Comment: Interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
PV_ANNUITY(1000;0.05;5) equals 4329.48
The RATE() function computes the constant interest rate per period of an investment.
- Syntax
RATE(nper;pmt;pv;fv;type;guess)
- Parameters
- Comment: Payment periodType: A floating point value (like 1.3, 0.343, 253 )Comment: Regular paymentsType: A floating point value (like 1.3, 0.343, 253 )Comment: Present valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Future valueType: A floating point value (like 1.3, 0.343, 253 )Comment: TypeType: A floating point value (like 1.3, 0.343, 253 )Comment: GuessType: A floating point value (like 1.3, 0.343, 253 )
- Examples
RATE(4*12;-200;8000) equals 0.007701472
The RECEIVED function returns the amount received at the maturity date for a invested security. Basis is the type of day counting you want to use: 0: US 30/360 (default), 1: real days, 2: real days/360, 3: real days/365 or 4: European 30/365. The settlement date must be before maturity date.
- Syntax
RECEIVED(settlement; maturity; investment; discount; basis)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: InvestmentType: A floating point value (like 1.3, 0.343, 253 )Comment: Discount rateType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
RECEIVED("2/28/2001"; "8/31/2001"; 1000; 0.05; 0) returns 1,025.787
The RRI function calculates the interest rate resulting from the profit (return) of an investment.
The SLN() function will determine the straight line depreciation of an asset for a single period. Cost is the amount you paid for the asset. Salvage is the value of the asset at the end of the period. Life is the number of periods over which the asset is depreciated. SLN divides the cost evenly over the life of an asset.
- Syntax
SLN(cost; salvage value; life)
- Parameters
- Comment: CostType: A floating point value (like 1.3, 0.343, 253 )Comment: SalvageType: A floating point value (like 1.3, 0.343, 253 )Comment: LifeType: A floating point value (like 1.3, 0.343, 253 )
- Examples
SLN(10000;700;10) equals 930
- Related Functions
SYD DDB
The SYD() function will calculate the sum-of-years digits depreciation for an asset based on its cost, salvage value, anticipated life, and a particular period. This method accelerates the rate of the depreciation, so that more depreciation expense occurs in earlier periods than in later ones. The depreciable cost is the actual cost minus the salvage value. The useful life is the number of periods (typically years) over which the asset is depreciated.
- Syntax
SYD(cost; salvage value; life; period)
- Parameters
- Comment: CostType: A floating point value (like 1.3, 0.343, 253 )Comment: SalvageType: A floating point value (like 1.3, 0.343, 253 )Comment: LifeType: A floating point value (like 1.3, 0.343, 253 )Comment: PeriodType: A floating point value (like 1.3, 0.343, 253 )
- Examples
SYD(5000; 200; 5; 2) equals 1280
- Related Functions
SLN DDB
The TBILLEQ functions returns the bond equivalent for a treasury bill. The maturity date must be after the settlement date but within 365 days.
- Syntax
TBILLEQ(settlement; maturity; discount)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: Discount rateType: A floating point value (like 1.3, 0.343, 253 )
- Examples
TBILLEQ("2/28/2001"; "8/31/2001"; 0.1) returns 0.1068
- Related Functions
TBILLPRICE TBILLYIELD
The TBILLPRICE functions returns the price per $100 value for a treasury bill. The maturity date must be after the settlement date but within 365 days. The discount rate must be positive.
- Syntax
TBILLPRICE(settlement; maturity; discount)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: Discount rateType: A floating point value (like 1.3, 0.343, 253 )
- Examples
TBILLPRICE("2/28/2001"; "8/31/2001"; 0.05) returns 97.4444
- Related Functions
TBILLEQ TBILLYIELD
The TBILLYIELD functions returns the yield for a treasury bill. The maturity date must be after the settlement date but within 365 days. The price must be positive.
- Syntax
TBILLYIELD(settlement; maturity; price)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: Price per $100 face valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
TBILLYIELD("2/28/2001"; "8/31/2001"; 600) returns -1.63
- Related Functions
TBILLEQ TBILLPRICE
VDB calculates the depreciation allowance of an asset with an initial value, an expected useful life, and a final value of salvage for a period specified, using the variable-rate declining balance method.
- Syntax
VDB(cost; salvage; life; start-period; end-period; [; depreciation-factor = 2 [; switch = false ]] )
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: PriceType: A floating point value (like 1.3, 0.343, 253 )Comment: RedemptionType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
VDB(10000;600;10;0;0.875;1.5) returns 1312.5
The XIRR function calculates the internal rate of return for a non-periodic series of cash flows.
- Syntax
XIRR( Values; Dates[; Guess = 0.1 ] )
- Parameters
- Comment: ValuesType: A floating point value (like 1.3, 0.343, 253 )Comment: DatesType: DateComment: GuessType: A floating point value (like 1.3, 0.343, 253 )
- Examples
XIRR(B1:B4;C1:C4) Suppose B1:B4 contains -20000, 4000, 12000, 8000 while C1:C4 contains "=DATE(2000;1;1)", "=DATE(2000;6;1)", "=DATE(2000;12;30)", "=DATE(2001;3;1)" returns 0.2115964
- Related Functions
IRR
The XNPV function calculates the net present value of a series of cash flows.
- Syntax
XNPV( Rate; Values; Dates )
- Parameters
- Comment: RateType: A floating point value (like 1.3, 0.343, 253 )Comment: ValuesType: A floating point value (like 1.3, 0.343, 253 )Comment: DatesType: Date
- Examples
XNPV(5%;B1:B4;C1:C4) suppose B1:B4 contains -20000, 4000, 12000, 8000 while C1:C4 contains "=DATE(2000;1;1)", "=DATE(2000;6;1)", "=DATE(2000;12;30)", "=DATE(2001;3;1)" returns 2907.83187
- Related Functions
NPV
YIELDDISC calculates the yield of a discounted security per 100 currency units of face value.
- Syntax
YIELDDISC(settlement; maturity; price, redemp, basis)
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: PriceType: A floating point value (like 1.3, 0.343, 253 )Comment: RedemptionType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
YIELDDISC(DATE(1990;6;1);DATE(1990;12;31);941.66667;1000) returns 0.106194684
The YIELDMAT function calculates the yield of the security that pays interest on the maturity date.
- Syntax
YIELDMAT( Settlement; Maturity; Issue; Rate; Price; Basis )
- Parameters
- Comment: SettlementType: DateComment: MaturityType: DateComment: IssueType: DateComment: Discount rateType: A floating point value (like 1.3, 0.343, 253 )Comment: PriceType: A floating point value (like 1.3, 0.343, 253 )Comment: BasisType: Whole number (like 1, 132, 2344)
- Examples
YIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990; 1; 1); 6%;103.819218241) returns 0.050000000
- Related Functions
YIELDDISC
The ZERO_COUPON() function calculates the value of a zero-coupon (pure discount) bond. For example: if the interest rate is 10%, a $1000 bond that matures in 20 years is worth ZERO_COUPON(1000;.1;20) or $148.64.
- Syntax
ZERO_COUPON(face value;rate;years)
- Parameters
- Comment: Face valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Interest rateType: A floating point value (like 1.3, 0.343, 253 )Comment: YearsType: A floating point value (like 1.3, 0.343, 253 )
- Examples
ZERO_COUPON(1000;.1;20) equals 148.64
The ERRORTYPE() function converts a error to a number. If the value is not an error, an error is returned. Otherwise, a numerical code is returned. Error codes are modelled on Excel.
- Syntax
ERRORTYPE(value)
- Parameters
- Comment: ErrorType: Any kind of value
- Examples
ERRORTYPE(NA()) returns 7
- Examples
ERRORTYPE(0) returns an error
Returns the current filename. If the current document is not saved, an empty string is returned.
- Syntax
FILENAME()
- Parameters
The FORMULA() function returns the formula of a cell as string.
- Syntax
FORMULA(x)
- Parameters
- Comment: ReferenceType: Reference
- Examples
FORMULA(A1) returns "=SUM(1+2)" if the cell A1 contains such a formula.
The INFO() function returns information about the current operating environment. Parameter type specifies what type of information you want to return. It is one of the following: "directory" returns the path of the current directory, "numfile" returns the number of active documents, "release" returns the version of Calligra Sheets as text, "recalc" returns the current recalculation mode: "Automatic" or "Manual", "system" returns the name of the operating environment, "osversion" returns the current operating system.
- Syntax
INFO(type)
- Parameters
- Comment: Type of informationType: Text
The ISBLANK() function returns True if the parameter is empty. Otherwise it returns False.
- Syntax
ISBLANK(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISBLANK(A1) returns True if A1 is empty
- Examples
ISBLANK(A1) returns False if A1 holds a value
The ISDATE() function returns True if the parameter is a date value. Otherwise it returns False
- Syntax
ISDATE(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISDATE("2000-2-2") returns True
- Examples
ISDATE("hello") returns False
The ISERR() function returns True if its parameter is an error other than N/A. Otherwise, it returns False. Use ISERROR() if you want to include the N/A error as well.
The ISERROR() function returns True if its parameter is an error of any type. Otherwise, it returns False.
The ISEVEN() function returns True if the number is even. Otherwise returns False.
- Syntax
ISEVEN(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISEVEN(12) returns True
- Examples
ISEVEN(-7) returns False
The ISFORMULA() function returns True if the referenced cell contains a formula. Otherwise it returns False
- Syntax
ISFORMULA(x)
- Parameters
- Comment: ReferenceType: Reference
The ISLOGICAL() function returns True if the parameter is a boolean value. Otherwise it returns False.
- Syntax
ISLOGICAL(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISLOGICAL(A1>A2) returns True
- Examples
ISLOGICAL(12) returns False
The ISNA() function returns True if its parameter is a N/A error. In all other cases, it returns False.
The ISNONTEXT() function returns True if the parameter is not a string. Otherwise it returns False. It's the same as ISNOTTEXT.
- Syntax
ISNONTEXT(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISNONTEXT(12) returns True
- Examples
ISNONTEXT("hello") returns False
- Related Functions
ISNOTTEXT
The ISNOTTEXT() function returns True if the parameter is not a string. Otherwise it returns False. It's the same as ISNONTEXT.
- Syntax
ISNOTTEXT(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISNOTTEXT(12) returns True
- Examples
ISNOTTEXT("hello") returns False
- Related Functions
ISNONTEXT
The ISNUM() function returns True if the parameter is a numerical value. Otherwise it returns False. It's the same as ISNUMBER.
- Syntax
ISNUM(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISNUM(12) returns True
- Examples
ISNUM(hello) returns False
- Related Functions
ISNUMBER
The ISNUMBER() function returns True if the parameter is a numerical value. Otherwise it returns False. It's the same as ISNUM.
- Syntax
ISNUMBER(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISNUMBER(12) returns True
- Examples
ISNUMBER(hello) returns False
- Related Functions
ISNUM
The ISODD() function returns True if the number is odd. Otherwise returns False.
- Syntax
ISODD(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISODD(12) returns False
- Examples
ISODD(-7) returns True
The ISREF() function returns True if the parameter refers to a reference. Otherwise it returns False
- Syntax
ISREF(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISREF(A12) returns true
- Examples
ISREF("hello") returns false
The ISTEXT() function returns True if the parameter is a string. Otherwise it returns False
- Syntax
ISTEXT(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISTEXT(12) returns False
- Examples
ISTEXT("hello") returns True
The ISTIME() function returns True if the parameter is a time value. Otherwise it returns False.
- Syntax
ISTIME(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
ISTIME("12:05") returns True
- Examples
ISTIME("hello") returns False
The N() function converts a value to a number. If value is or refers to a number, this function returns the number. If value is True, this function returns 1. If a value is a date, this function returns the serial number of that date. Anything else will cause the function to return 0.
- Syntax
N(value)
- Parameters
- Comment: ValueType: Any kind of value
- Examples
N(3.14) returns 3.14
- Examples
N("7") returns 0 (because "7" is text)
The NA() function returns the constant error value, N/A.
The TYPE() function returns 1 if the value is a number, 2 if it is text, 4 if the value is a logical value, 16 if it is an error value or 64 if the value is an array. If the cell the value represents contains a formula you get its return type.
- Syntax
TYPE(x)
- Parameters
- Comment: Any valueType: Any kind of value
- Examples
TYPE(A1) returns 2, if A1 contains "Text"
- Examples
TYPE(-7) returns 1
- Examples
TYPE(A2) returns 1, if A2 contains "=CURRENTDATE()"
The AND() function returns True if all the values are true. Otherwise it returns False (unless any of the values in an error - then it returns an error).
- Syntax
AND(value;value;...)
- Parameters
- Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)
- Examples
AND(true;true;true) returns True
- Examples
AND(true;false) returns False
The FALSE() function returns the boolean value FALSE.
- Syntax
FALSE()
- Parameters
- Examples
FALSE() returns FALSE
The IF() function is a conditional function. This function returns the second parameter if the condition is True. Otherwise it returns the third parameter (which defaults to being false).
- Syntax
IF(condition;if_true;if_false)
- Parameters
- Comment: ConditionType: A truth value (TRUE or FALSE)Comment: If trueType: Any kind of valueComment: If falseType: Any kind of value
- Examples
A1=4;A2=6;IF(A1>A2;5;3) returns 3
Return X unless it is an Error, in which case return an alternative value.
- Syntax
IFERROR(AnyX;AnyAlternative)
- Parameters
- Comment: Any XType: Any kind of valueComment: Any AlternativeType: Any kind of value
- Examples
IFERROR(A1;A2) returns the content of A1 if that content is not an error-value else the content of A2 is returned.
Return X unless it is an NA, in which case return an alternative value.
- Syntax
IFNA(AnyX;AnyAlternative)
- Parameters
- Comment: Any XType: Any kind of valueComment: Any AlternativeType: Any kind of value
- Examples
IFNA(A1;A2) returns the content of A1 if that content is not an #N/A error-value else the content of A2 is returned.
The NAND() function returns True if at least one value is not true. Otherwise it returns False.
- Syntax
NAND(value;value;...)
- Parameters
- Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)
- Examples
NAND(true;false;false) returns True
- Examples
NAND(true;true) returns False
The NOR() function returns True if all the values given as parameters are of boolean type and have the value false. Otherwise it returns False.
- Syntax
NOR(value;value;...)
- Parameters
- Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)
- Examples
NOR(true;false;false) returns False
- Examples
NOR(false;false) returns True
The NOT() function returns True if the value is False and returns False if the value is True. It returns an error if the input in an error.
- Syntax
NOT(bool)
- Parameters
- Comment: Boolean valueType: A truth value (TRUE or FALSE)
- Examples
NOT(false) returns True
- Examples
NOT(true) returns False
The OR() function returns True if at least one of the values is true. Otherwise it returns False (unless any of the values is an error, then it returns an error).
- Syntax
OR(value;value;...)
- Parameters
- Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)
- Examples
OR(false;false;false) returns False
- Examples
OR(true;false) returns True
The TRUE() function returns the boolean value TRUE.
- Syntax
TRUE()
- Parameters
- Examples
TRUE() returns TRUE
The XOR() function returns False if the number of True values is even. Otherwise it returns True. It returns an error if any argument is an error.
- Syntax
XOR(value;value;...)
- Parameters
- Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)Comment: Boolean valuesType: A range of truth values (TRUE or FALSE)
- Examples
XOR(false;false;false) returns True
- Examples
XOR(true;false) returns True
The ADDRESS creates a cell address. Parameter Row is the row number and Column is the column number.
Absolute number specifies the type of reference: 1 or omitted = Absolute, 2 = Absolute row, relative column, 3 = Relative row; absolute column and 4 = Relative.
A1 Style specifies the style of the address to return. If A1 is set to TRUE (default) the address is returned in A1 style if it is set to FALSE in R1C1 style.
Sheet name is the text specifying the name of the sheet.
- Syntax
ADDRESS(row; col; absolute; style; sheet name)
- Parameters
- Comment: Row numberType: Whole number (like 1, 132, 2344)Comment: Column numberType: Whole number (like 1, 132, 2344)Comment: Absolute number (optional)Type: Whole number (like 1, 132, 2344)Comment: A1 style (optional)Type: A truth value (TRUE or FALSE)Comment: Sheet nameType: Text
- Examples
ADDRESS(6; 4) returns $D$6
- Examples
ADDRESS(6; 4; 2) returns D$6
- Examples
ADDRESS(6; 4; 2; FALSE; "Sheet1") returns Sheet1!R6C[4]
- Examples
ADDRESS(6; 4; 1; FALSE; "Sheet1") returns Sheet1!R6C4
- Examples
ADDRESS(6; 4; 4; TRUE; "Sheet1") returns Sheet1!D6
Returns the number of areas in the reference string. An area can be asingle cell or a set of cells.
- Syntax
AREAS(reference)
- Parameters
- Comment: ReferenceType: A range of strings
- Examples
AREAS(A1) returns 1
- Examples
AREAS((A1; A2:A4)) returns 2
Returns information about position, formatting or contents in a reference.
- Syntax
CELL(type; reference)
- Parameters
- Comment: TypeType: TextComment: ReferenceType: Reference
- Examples
CELL("COL", C7) returns 3
- Examples
CELL("ROW", C7) returns 7
- Examples
CELL("ADDRESS", C7) returns $C$7
Returns the parameter specified by the index.
- Syntax
CHOOSE(index; parameter1; parameter2;...)
- Parameters
- Comment: IndexType: Whole number (like 1, 132, 2344)Comment: ArgumentsType:
- Examples
CHOOSE(1; "1st"; "2nd") returns "1st"
- Examples
CHOOSE(2; 3; 2; 4) returns 2
The COLUMN function returns the column of given cell reference. If no parameter is specified the column of the current cell gets returned.
The COLUMNS function returns the number of columns in a reference.
Look for a matching value in the first row of the given table, and return the value of the indicated row.
Looks up the 'lookup value' in the first row of the 'data source'. If a value matches, the value in the 'row' and the column, the value was found in, is returned. If 'sorted' is true (default), the first row is assumed to be sorted. The search will end, if the 'lookup value' is lower than the value, currently compared to.
- Syntax
HLOOKUP(Lookup value; data source; Row; Sorted)
- Parameters
- Comment: Lookup valueType: String/NumericComment: Data sourceType: ArrayComment: RowType: Whole number (like 1, 132, 2344)Comment: Sorted (optional)Type: A truth value (TRUE or FALSE)
If a range is given, returns value stored in a given row/column. If one cell is given, which contains an array, then one element of the array is returned.
- Syntax
INDEX(cell, row, column)
- Parameters
- Comment: ReferenceType: TextComment: RowType: Whole number (like 1, 132, 2344)Comment: ColumnType: Whole number (like 1, 132, 2344)
- Examples
INDEX(A1:C3;2;2), returns contents of B2
- Examples
INDEX(A1;2;2), if A1 is a result of array calculation, returns its (2,2) element.
Returns the content of the cell specified by the reference text. The second parameter is optional.
- Syntax
INDIRECT(referenceText, a1 style)
- Parameters
- Comment: ReferenceType: TextComment: A1 style (optional)Type: A truth value (TRUE or FALSE)
- Examples
INDIRECT(A1), A1 contains "B1", and B1 1 => returns 1
- Examples
INDIRECT("A1"), returns content of A1
The LOOKUP function looks up the first parameter in the lookup vector. It returns a value in the result Vector with the same index as the matching value in the lookup vector. If value is not in the lookup vector it takes the next lower one. If no value in the lookup vector matches an error is returned. The lookup vector must be in ascending order and lookup and result vector must have the same size. Numeric values, string and boolean values are recognized. Comparison between strings is case-insensitive.
- Syntax
LOOKUP(value; lookup vector; result vector)
- Parameters
- Comment: Lookup valueType: String/NumericComment: Lookup vectorType: String/NumericComment: Result vectorType: String/Numeric
- Examples
LOOKUP(1.232; A1:A6; B1:B6) for A1 = 1, A2 = 2 returns the value of B1.
Finds a search value in a search region, and returns its position (starting from 1). Match type can be either -1, 0 or 1 and determines how is searched for the value. If match type is 0, the index of the first value that equals search value is returned. If match type is 1 (or omitted), the index of the first value that is less than or equal to the search value is returned and the values in the search region must be sorted in ascending order. If match type is -1, the smallest value that is greater than or equal to the search value is found, and the search region needs to be sorted in descending order.
- Syntax
MATCH(Search value; Search region; Match type)
- Parameters
- Comment: Search valueType: String/NumericComment: Search regionType: Reference/ArrayComment: Match type (optional)Type: Whole number (like 1, 132, 2344)
MULTIPLE.OPERATIONS executes the formula expression pointed to by FormulaCell and all formula expressions it depends on while replacing all references to RowCell with references to RowReplacement respectively all references to ColumnCell with references to ColumnReplacement. The function may be used to easily create tables of expressions that depend on two input parameters.
- Syntax
MULTIPLE.OPERATIONS(Formula cell; Row cell; Row replacement; Column cell; Column replacement)
- Parameters
- Comment: Formula cellType: ReferenceComment: Row cellType: ReferenceComment: Row replacementType: ReferenceComment: Column cell (optional)Type: ReferenceComment: Column replacement (optional)Type: Reference
Modifies a reference's position and dimension.
- Syntax
OFFSET(Reference reference; Integer rowOffset; Integer columnOffset; Integer newHeight; Integer newWidth)
- Parameters
- Comment: Reference or rangeType: ReferenceComment: Number of rows to offsetType: Whole number (like 1, 132, 2344)Comment: Number of columns to offsetType: Whole number (like 1, 132, 2344)Comment: Height of the offset range (optional)Type: Whole number (like 1, 132, 2344)Comment: Width of the offset range (optional)Type: Whole number (like 1, 132, 2344)
The ROW function returns the row of given cell reference. If no parameter is specified the row of the current cell gets returned.
The ROWS function returns the number of rows in a reference.
Returns the sheet number of the reference or the string representing a sheet name.
- Syntax
SHEET(reference)
- Parameters
- Comment: ReferenceType: Reference
- Examples
SHEET(Sheet1!C7) returns 1
- Examples
SHEET(Sheet2!C7) returns 2
Returns the number of sheets in a reference or current document.
- Syntax
SHEETS(reference)
- Parameters
- Comment: ReferenceType: Reference
Look for a matching value in the first column of the given table, and return the value of the indicated column.
Looks up the 'lookup value' in the first column of the 'data source'. If a value matches, the value in the 'column' and the row, the value was found in, is returned. If 'sorted' is true (default), the first column is assumed to be sorted. The search will end, if the 'lookup value' is lower than the value, currently compared to.
- Syntax
VLOOKUP(Lookup value; data source; Column; Sorted)
- Parameters
- Comment: Lookup valueType: String/NumericComment: Data sourceType: ArrayComment: ColumnType: Whole number (like 1, 132, 2344)Comment: Sorted (optional)Type: A truth value (TRUE or FALSE)
The ABS() function returns the absolute value of the floating-point number x.
- Syntax
ABS(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
ABS(12.5) equals 12.5
- Examples
ABS(-12.5) equals 12.5
The CEIL() function rounds x up to the nearest integer which is greater than the input, returning that value as a double.
The CEILING() function rounds x up (away from zero) to the nearest multiple of Significance which is greater than the input. The default value for Significance is 1 (or -1 if the value is negative), which means rounding up to the nearest integer. If the Mode parameter is non-zero, the function rounds away from zero, instead of up towards the positive infinity.
- Syntax
CEILING(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Significance (optional)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Mode (optional)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
CEILING(12.5) equals 13
- Examples
CEILING(6.43; 4) equals 8
- Examples
CEILING(-6.43; -4; 1) equals -8
- Examples
CEILING(-6.43; -4; 0) equals -4
- Related Functions
CEIL FLOOR ROUND ROUNDUP
This function returns the count of integer or floating arguments passed. You can count using a range: COUNT(A1:B5) or using a list of values like COUNT(12;5;12.5).
This function returns the count of all non empty arguments passed. You can count using a range: COUNTA(A1:B5) or using a list of values like COUNTA(12;5;12.5).
This function returns the count of all empty cells within the range.
The COUNTIF() function returns the number of cells in the given range that meet the given criteria.
The CUR() function returns the non-negative cube root of x.
- Syntax
CUR(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
CUR(27) equals 3
- Related Functions
SQRT
The DIV() function divides the first value by the other values in turn.
EPS() returns the machine epsilon; this is the difference between 1 and the next largest floating-point number. Because computers use a finite number of digits, roundoff error is inherent (but usually insignificant) in all calculations.
- Syntax
EPS()
- Parameters
- Examples
On most systems, this returns 2^-52=2.2204460492503131e-16
- Examples
0.5*EPS() returns the "unit round"; this value is interesting because it is the largest number x where (1+x)-1=0 (due to roundoff errors).
- Examples
EPS() is so small that Calligra Sheets displays 1+eps() as 1
- Examples
Pick a number x between 0 and EPS(). Observe that 1+x rounds x to either 0 or EPS() by using the equation (1+x)-1
The EVEN() function returns the number rounded up to the nearest even integer.
- Syntax
EVEN(value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
EVEN(1.2) returns 2
- Examples
EVEN(2) returns 2
- Related Functions
ODD
The EXP() function returns the value of e (the base of natural logarithms) raised to the power of x.
- Syntax
EXP(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
EXP(9) equals 8 103.08392758
- Examples
EXP(-9) equals 0.00012341
- Related Functions
LN
The FACT() function calculates the factorial of the parameter. The mathematical expression is (value)!.
- Syntax
FACT(number)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FACT(10) returns 3628800
- Examples
FACT(0) returns 1
The FACTDOUBLE() function calculates the double factorial of a number, i.e. x!!.
- Syntax
FACTDOUBLE(number)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FACTDOUBLE(6) returns 48
- Examples
FACTDOUBLE(7) returns 105
Function FIB calculates the Nth term of a Fibonacci sequence (1, 1, 2, 3, 5, 8, 13, 21...), in which each number, after the first two, is the sum of the two numbers immediately preceding it. FIB(0) is defined to be 0.
- Syntax
FIB(n)
- Parameters
- Comment: Nth termType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FIB(9) returns 34
- Examples
FIB(26) returns 121393
Round a number x down to the nearest multiple of the second parameter, Significance.
The FLOOR() function rounds x down (towards zero) to the nearest multiple of Significance which is smaller than the input. The default value for Significance is 1, if x is positive. It is -1, if the value is negative, which means rounding up to the nearest integer. If mode is given and not equal to zero, the amount of x is rounded toward zero to a multiple of significance and then the sign applied. Otherwise, it rounds toward negative infinity. If any of the two parameters x or Significance is zero, the result is zero.
- Syntax
FLOOR(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Significance (optional)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Mode (optional)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
FLOOR(12.5) equals 12
- Examples
FLOOR(-12.5) equals -13
- Examples
FLOOR(5; 2) equals 4
- Examples
FLOOR(5; 2.2) equals 4.4
- Related Functions
CEIL CEILING ROUND ROUNDDOWN
The GAMMA() function returns the gamma function value.
- Syntax
GAMMA(value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
GAMMA(1) returns 1
- Related Functions
FACT
The GCD() function returns the greatest common denominator for two or more integer values.
- Syntax
GCD(value; value)
- Parameters
- Comment: First numberType: A range of whole numbers (like 1, 132, 2344)Comment: Second numberType: A range of whole numbers (like 1, 132, 2344)Comment: Third numberType: A range of whole numbers (like 1, 132, 2344)
- Examples
GCD(6;4) returns 2
- Examples
GCD(10;20) returns 10
- Examples
GCD(20;15;10) returns 5
- Related Functions
LCM
The G_PRODUCT() function is the same as KPRODUCT. It is provided for Gnumeric compatibility.
- Syntax
G_PRODUCT(value;value;...)
- Parameters
- Comment: ValuesType: FLOAT
- Related Functions
KPRODUCT
The INT() function returns the integer part of the value.
This function multiplies each value by -1.
- Syntax
INV(value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
INV(-5) equals 5
- Examples
INV(5) equals -5
- Examples
INV(0) equals 0
The KPRODUCT() function calculates the product of all the values given as parameters. You can calculate the product of a range: KPRODUCT(A1:B5) or a list of values like KPRODUCT(12;5;12.5). If no numeric values are found 1 is returned.
The LCM() function returns the least common multiple for two or more float values
- Syntax
LCM(value; value)
- Parameters
- Comment: First numberType: FLOATComment: Second numberType: FLOAT
- Examples
LCM(6;4) returns 12
- Examples
LCM(1.5;2.25) returns 4.5
- Examples
LCM(2;3;4) returns 12
- Related Functions
GCD
The LN() function returns the natural logarithm of x.
The LOG() function returns the base-10 logarithm of x.
The LOG10() function returns the base-10 logarithm of the argument.
The LOG2() function returns the base-2 logarithm of x.
The LOGn() function returns the base n logarithm of x.
The MAX() function returns the largest value given in the parameters. String and logical values are ignored.
The MAXA() function returns the largest value given in the parameters. TRUE evaluates to 1, FALSE evaluates to 0. String values are ignored.
Function MDETERM returns the determinant of a given matrix. The matrix must be of type n x n.
- Syntax
MDETERM(matrix)
- Parameters
- Comment: RangeType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
MDETERM(A1:C3)
- Related Functions
MMULT
The MIN() function returns the smallest value given in the parameters. String and logical values are ignored.
The MINA() function returns the smallest value given in the parameters. TRUE evaluates to 1, FALSE to 0. String values are ignored.
Calculates the inverse of the matrix.
The matrix multiplied with its inverse results in the unity matrix of the same dimension.
Invertible matrices have a non-zero determinant.
Function MMULT multiplies two matrices. Number of columns of the first matrix must be the same as row count of the second one. The result is a matrix.
- Syntax
MMULT(matrix1;matrix2)
- Parameters
- Comment: First matrixType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Second matrixType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
MMULT(A1:C3)
- Related Functions
MDETERM
The MOD() function returns the remainder after division. If the second parameter is null the function returns #DIV/0.
- Syntax
MOD(value;value)
- Parameters
- Comment: Floating point valueType: Whole number (like 1, 132, 2344)Comment: Floating point valueType: Whole number (like 1, 132, 2344)
- Examples
MOD(12;5) returns 2
- Examples
MOD(5;5) returns 0
- Related Functions
DIV
The MROUND() function returns the value rounded to the specified multiple. The value and the multiple must have the same sign
- Syntax
MROUND(value; multiple)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: MultipleType: A floating point value (like 1.3, 0.343, 253 )
- Examples
MROUND(1.252; 0.5) equals 1.5
- Examples
MROUND(-1.252; -0.5) equals -1.5
- Related Functions
ROUND
The MULTINOMIAL() function returns the multinomial of each number in the parameters. It uses this formula for MULTINOMIAL(a,b,c):
(a+b+c)! / a!b!c!
- Syntax
MULTINOMIAL(value;value;...)
- Parameters
- Comment: ValuesType: FLOAT
- Examples
MULTINOMIAL(3;4;5) equals 27720
The MULTIPLY() function multiplies all the values given in the parameters. You can multiply values given by a range MULTIPLY(A1:B5) or a list of values like MULTIPLY(12;5;12.5). It's equivalent to PRODUCT.
Creates the unity matrix of the given dimension.
- Syntax
MUNIT(dimension)
- Parameters
- Comment: DimensionType: Whole number (like 1, 132, 2344)
- Examples
MUNIT(3) creates a 3x3 unity matrix
- Related Functions
MINVERSE
The ODD() function returns the number rounded up (or down, for negative values) to the nearest odd integer. By definition, ODD(0) is 1.
- Syntax
ODD(value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
ODD(1.2) returns 3
- Examples
ODD(2) returns 3
- Examples
ODD(-2) returns -3
- Related Functions
EVEN
The POW(x;y) function returns the value of x raised to the power of y. It's the same as POWER.
- Syntax
POW(value;value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
POW(1.2;3.4) equals 1.8572
- Examples
POW(2;3) equals 8
- Related Functions
POWER
The POWER(x;y) function returns the value of x raised to the power of y.
- Syntax
POWER(value;value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
POWER(1.2;3.4) equals 1.8572
- Examples
POWER(2;3) equals 8
- Related Functions
POW
The PRODUCT() function calculates the product of all the values given as parameters. You can calculate the product of a range: PRODUCT(A1:B5) or a list of values like product(12;5;12.5). If no numeric values are found 0 is returned.
Function QUOTIENT returns the integer portion of numerator/denumerator.
- Syntax
QUOTIENT(numerator;denumerator)
- Parameters
- Comment: NumeratorType: A floating point value (like 1.3, 0.343, 253 )Comment: DenumeratorType: A floating point value (like 1.3, 0.343, 253 )
- Examples
QUOTIENT(21;4) returns 5
- Related Functions
INT
The RAND() function returns a pseudo-random number between 0 and 1.
- Syntax
RAND()
- Parameters
- Examples
RAND() equals for example 0.78309922...
- Related Functions
RANDBETWEEN RANDEXP
The RANDBERNOULLI() function returns a Bernoulli-distributed pseudo-random number.
- Syntax
RANDBERNOULLI(x)
- Parameters
- Comment: A floating point value (between 0 and 1)Type: A floating point value (like 1.3, 0.343, 253 )Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
RANDBERNOULLI(0.45)
- Related Functions
RAND
The RANDBETWEEN() function returns a pseudo-random number between bottom and top value. If bottom > top this function returns Err.
- Syntax
RANDBETWEEN(bottom;top)
- Parameters
- Comment: Bottom valueType: Whole number (like 1, 132, 2344)Comment: Top valueType: Whole number (like 1, 132, 2344)
- Examples
RANDBETWEEN(12;78) equals for example 61.0811...
- Related Functions
RAND
The RANDBINOM() function returns a binomially-distributed pseudo-random number.
- Syntax
RANDBINOM(x)
- Parameters
- Comment: A floating point value (between 0 and 1)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Trials (greater 0)Type: Whole number (like 1, 132, 2344)
- Examples
RANDBINOM(4)
- Related Functions
RAND RANDNEGBINOM
The RANDEXP() function returns an exponentially-distributed pseudo-random number.
- Syntax
RANDEXP(x)
- Parameters
- Comment: A floating point value (greater 0)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
RANDEXP(0.88)
- Related Functions
RAND
The RANDNEGBINOM() function returns a negative binomially-distributed pseudo-random number.
The RANDNORM() function returns a Normal(Gaussian)-distributed pseudo-random number.
- Syntax
RANDNORM(mu; sigma)
- Parameters
- Comment: Mean value of the normal distributionType: A floating point value (like 1.3, 0.343, 253 )Comment: Dispersion of the normal distributionType: A floating point value (like 1.3, 0.343, 253 )
- Examples
RANDNORM(0; 1)
- Related Functions
RAND
The RANDPOISSON() function returns a poisson-distributed pseudo-random number.
- Syntax
RANDPOISSON(x)
- Parameters
- Comment: A floating point value (greater 0)Type: A floating point value (like 1.3, 0.343, 253 )Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
RANDPOISSON(4)
- Related Functions
RAND
The ROOTN() function returns the non-negative nth root of x.
- Syntax
ROOTN(x;n)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: ValueType: Whole number (like 1, 132, 2344)
- Examples
ROOTN(9;2) equals 3
- Related Functions
SQRT
The ROUND(value;[digits]) function returns value rounded. Digits is the number of digits to which you want to round that number. If digits is zero or omitted, value is rounded up to the nearest integer. If digits is smaller than zero, the corresponding integer part of the number is rounded.
- Syntax
ROUND(value;[digits])
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: DigitsType: Whole number (like 1, 132, 2344)
- Examples
ROUND(1.252;2) equals 1.25
- Examples
ROUND(-1.252;2) equals -1.25
- Examples
ROUND(1.258;2) equals 1.26
- Examples
ROUND(-12.25;-1) equals -10
- Examples
ROUND(-1.252;0) equals -1
- Related Functions
MROUND ROUNDDOWN ROUNDUP
The ROUNDDOWN(value;[digits]) function returns value rounded so that its absolute value is lesser. Digits is the number of digits to which you want to round that number. If digits is zero or omitted, value is rounded down to the nearest integer.
- Syntax
ROUNDDOWN(value;[digits])
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: DigitsType: Whole number (like 1, 132, 2344)
- Examples
ROUNDDOWN(1.252) equals 1
- Examples
ROUNDDOWN(1.252;2) equals 1.25
- Examples
ROUNDDOWN(-1.252;2) equals -1.25
- Examples
ROUNDDOWN(-1.252) equals -1
- Related Functions
ROUND ROUNDUP
The ROUNDUP(value;[digits]) function returns value rounded so that its absolute value is greater. Digits is the number of digits to which you want to round that number. If digits is zero or omitted, value is rounded up to the nearest integer.
- Syntax
ROUNDUP(value;[digits])
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )Comment: DigitsType: Whole number (like 1, 132, 2344)
- Examples
ROUNDUP(1.252) equals 2
- Examples
ROUNDUP(1.252;2) equals 1.26
- Examples
ROUNDUP(-1.252;2) equals -1.26
- Examples
ROUNDUP(-1.252) equals -2
- Related Functions
ROUND ROUNDDOWN
The SERIESSUM() function returns the sum of a power series.
- Syntax
SERIESSUM( X; N; M; Coefficients)
- Parameters
- Comment: X the independent variable of the power seriesType: A floating point value (like 1.3, 0.343, 253 )Comment: N the initial power to which X is to be raisedType: A floating point value (like 1.3, 0.343, 253 )Comment: M the increment by which to increase N for each term in the seriesType: A floating point value (like 1.3, 0.343, 253 )Comment: Coefficients a set of coefficients by which each successive power of the variable X is multipliedType: FLOAT
- Examples
SERIESSUM(2;0;2;{1;2}) return 9
This function returns -1 if the number is negative, 0 if the number is null and 1 if the number is positive.
- Syntax
SIGN(value)
- Parameters
- Comment: Floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
SIGN(5) equals 1
- Examples
SIGN(0) equals 0
- Examples
SIGN(-5) equals -1
The SQRT() function returns the non-negative square root of the argument. It is an error if the argument is negative.
- Syntax
SQRT(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
SQRT(9) equals 3
- Examples
SQRT(-9) is an error
- Related Functions
IMSQRT
The SQRTPI() function returns the non-negative square root of x * PI. It is an error if the argument is negative.
- Syntax
SQRTPI(x)
- Parameters
- Comment: A floating point valueType: A floating point value (like 1.3, 0.343, 253 )
- Examples
SQRTPI(2) equals 2.506628
The SUBTOTAL() function returns a subtotal of a given list of arguments ignoring other subtotal results in there. Function can be one of the following numbers: 1 - Average, 2 - Count, 3 - CountA, 4 - Max, 5 - Min, 6 - Product, 7 - StDev, 8 - StDevP, 9 - Sum, 10 - Var, 11 - VarP.
- Syntax
SUBTOTAL(function; value)
- Parameters
- Comment: FunctionType: Whole number (like 1, 132, 2344)Comment: ValuesType: FLOAT
- Examples
If A1:A5 contains 7, 24, 23, 56 and 9:
- Examples
SUBTOTAL(1; A1:A5) returns 23.8
- Examples
SUBTOTAL(4; A1:A5) returns 56
- Examples
SUBTOTAL(9; A1:A5) returns 119
- Examples
SUBTOTAL(11; A1:A5) returns 307.76
- Related Functions
AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV STDEVP SUM VAR VARP
The SUM() function calculates the sum of all the values given as parameters. You can calculate the sum of a range SUM(A1:B5) or a list of values like SUM(12;5;12.5).
The SUMA() function calculates the sum of all the values given as parameters. You can calculate the sum of a range SUMA(A1:B5) or a list of values like SUMA(12;5;12.5). If a parameter contains text or the boolean value FALSE it is counted as 0, if a parameter evaluates to TRUE it is counted as 1.
The SUMIF() function calculates the sum of all values given as parameters which match the criteria. The sum range is optional. If not supplied, the values in the check range are summed. The length of the check range should be equal or less than the length of the sum range.
- Syntax
SUMIF(checkrange;criteria;sumrange)
- Parameters
- Comment: Check rangeType: A range of floating point values (like 1.3, 0.343, 253 )Comment: CriteriaType: TextComment: Sum rangeType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
SUMIF(A1:A4;">1") sums all values in range A1:A4 which match >1
- Examples
SUMIF(A1:A4;"=0";B1:B4) sums all values in range B1:B4 if the corresponding value in A1:A4 matches =0
- Related Functions
SUM COUNTIF
The SUMSQ() function calculates the sum of all the squares of values given as parameters. You can calculate the sum of a range SUMSQ(A1:B5) or a list of values like SUMSQ(12;5;12.5).
- Syntax
SUMSQ(value;value;...)
- Parameters
- Comment: ValuesType: FLOAT
- Examples
SUMSQ(12;5;7) equals 218
- Examples
SUMSQ(12.5;2) equals 173
- Related Functions
SUM
Returns the transpose of a matrix, i.e. rows and columns of the matrix are exchanged.
- Syntax
TRANSPOSE(matrix)
- Parameters
- Comment: MatrixType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
TRANSPOSE(A1:C3)
The TRUNC() function truncates a numeric value to a certain precision. If the precision is omitted 0 is assumed.
The AVEDEV() function calculates the average of the absolute deviations of a data set from their mean.
- Syntax
AVEDEV(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
AVEDEV(11.4;17.3;21.3;25.9;40.1) returns 7.84
- Examples
AVEDEV(A1:A5) ...
The AVERAGE() function calculates the average of all the values given as parameters. You can calculate the average of a range AVERAGE(A1:B5) or a list of values like AVERAGE(12;5;12.5).
- Syntax
AVERAGE(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
AVERAGE(12;5;7) equals 8
- Examples
AVERAGE(12.5;2) equals 7.25
The AVERAGEA() calculates the average of the given arguments. Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.
- Syntax
AVERAGEA(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: String valuesType: Text
- Examples
AVERAGEA(11.4;17.3;"sometext";25.9;40.1) equals 18.94
The BETADIST() function returns the cumulative beta probability density function.
The third and fourth parameters are optional. They set the lower and upper bounds, otherwise defaulting to 0.0 and 1.0 respectively.
- Syntax
BETADIST(number;alpha;beta;start;end;[cumulative=TRUE])
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Alpha parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: Beta parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: StartType: A floating point value (like 1.3, 0.343, 253 )Comment: EndType: A floating point value (like 1.3, 0.343, 253 )Comment: CumulativeType: A truth value (TRUE or FALSE)
- Examples
BETADIST(0.2859;0.2606;0.8105) equals 0.675444
- Examples
BETADIST(0.2859;0.2606;0.8105;0.2;0.9) equals 0.537856
The BETAINV() function returns the inverse of BETADIST(x;alpha;beta;a;b;TRUE()).
The start and end parameters are optional. They set the lower and upper bounds, otherwise defaulting to 0.0 and 1.0 respectively.
- Syntax
BETAINV(number;alpha;beta [; start=0 [; end=1]])
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Alpha parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: Beta parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: StartType: A floating point value (like 1.3, 0.343, 253 )Comment: EndType: A floating point value (like 1.3, 0.343, 253 )
- Examples
BETADIST(BETAINV(0.1;3;4);3;4) equals 0.1
- Examples
BETADIST(BETAINV(0.3;3;4);3;4) equals 0.3
The BINO() function returns the binomial distribution.
The first parameter is the number of trials, the second parameter is the number of successes, and the third is the probability of success. The number of trials should be greater than the number of successes and the probability should be smaller or equal to 1.
- Syntax
BINO(trials;success;prob_of_success)
- Parameters
- Comment: Number of trialsType: Whole number (like 1, 132, 2344)Comment: Number of successful trialsType: Whole number (like 1, 132, 2344)Comment: Probability of successType: Double
- Examples
BINO(12;9;0.8) returns 0.236223201
The CHIDIST() function returns the probability value from the indicated Chi square that a hypothesis is confirmed.
CHIDIST compares the Chi square value to be given for a random sample that is calculated from the sum of (observed value-expected value)^2/expected value for all values with the theoretical Chi square distribution and determines from this the probability of error for the hypothesis to be tested.
- Syntax
CHIDIST(number;degrees_freedom)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Degrees of freedomType: Whole number (like 1, 132, 2344)
- Examples
CHIDIST(13.27;5) returns 0.021
The COMBIN() function calculates the count of possible combinations. The first parameter is the total count of elements. The second parameter is the count of elements to choose. Both parameters should be positive and the first parameter should not be less than the second. Otherwise the function returns an error.
- Syntax
COMBIN(total;chosen)
- Parameters
- Comment: Total number of elementsType: Whole number (like 1, 132, 2344)Comment: Number of elements to chooseType: Whole number (like 1, 132, 2344)
- Examples
COMBIN(12;5) returns 792
- Examples
COMBIN(5;5) returns 1
The COMBINA() function calculates the count of possible combinations. The first parameter is the total count of elements. The second parameter is the count of elements to choose. Both parameters should be positive and the first parameter should not be less than the second. Otherwise the function returns an error.
- Syntax
COMBIN(total;chosen)
- Parameters
- Comment: Total number of elementsType: Whole number (like 1, 132, 2344)Comment: Number of elements to chooseType: Whole number (like 1, 132, 2344)
- Examples
COMBIN(12;5) returns 792
- Examples
COMBIN(5;5) returns 1
The CONFIDENCE() function returns the confidence interval for a population mean.
The alpha parameter must be between 0 and 1 (non-inclusive), stddev must be positive and size must be greater or equal to 1.
- Syntax
CONFIDENCE(alpha;stddev;size)
- Parameters
- Comment: Level of the confidence intervalType: A floating point value (like 1.3, 0.343, 253 )Comment: Standard deviation for the total populationType: A floating point value (like 1.3, 0.343, 253 )Comment: Size of the total populationType: Whole number (like 1, 132, 2344)
- Examples
CONFIDENCE(0.05;1.5;100) equals 0.294059
The CORREL() function calculates the correlation coefficient of two cell ranges.
- Syntax
CORREL(range1; range2)
- Parameters
- Comment: Cell range of valuesType: DoubleComment: Second cell range of valuesType: Double
- Examples
CORREL(A1:A3; B1:B3)
- Related Functions
PEARSON
The COVAR() function calculates the covariance of two cell ranges.
- Syntax
COVAR(range1; range2)
- Parameters
- Comment: Cell range of valuesType: DoubleComment: Second cell range of valuesType: Double
- Examples
COVAR(A1:A3; B1:B3)
The DEVSQ() function calculates the sum of squares of deviations.
- Syntax
DEVSQ(value; value;...)
- Parameters
- Comment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: Double
- Examples
DEVSQ(A1:A5)
- Examples
DEVSQ(21; 33; 54; 23) returns 684.75
The EXPONDIST() function returns the exponential distribution.
The lambda parameter must be positive.
Cumulative = 0 calculates the density function; cumulative = 1 calculates the distribution.
- Syntax
EXPONDIST(number;lambda;cumulative)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Lambda parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: 0 = density, 1 = distributionType: Whole number (like 1, 132, 2344)
- Examples
EXPONDIST(3;0.5;0) equals 0.111565
- Examples
EXPONDIST(3;0.5;1) equals 0.776870
The FDIST() function returns the f-distribution.
- Syntax
FDIST(number;degrees_freedom_1;degrees_freedom_2)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Degrees of freedom 1Type: Whole number (like 1, 132, 2344)Comment: Degrees of freedom 2Type: Whole number (like 1, 132, 2344)
- Examples
FDIST(0.8;8;12) yields 0.61
The FINV() function returns the unique non-negative number x such that FDIST(x;r1;r2) = p.
- Syntax
FINV(number; r1; r2)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Number r1Type: Whole number (like 1, 132, 2344)Comment: Number r2Type: Whole number (like 1, 132, 2344)
- Examples
FDIST(FINV(0.1;3;4);3;4) equals 0.1
The FISHER() function returns the Fisher transformation for x and creates a function close to a normal distribution.
- Syntax
FISHER(number)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FISHER(0.2859) equals 0.294096
- Examples
FISHER(0.8105) equals 1.128485
The FISHERINV() function returns the inverse of the Fisher transformation for x and creates a function close to a normal distribution.
- Syntax
FISHERINV(number)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )
- Examples
FISHERINV(0.2859) equals 0.278357
- Examples
FISHERINV(0.8105) equals 0.669866
Counts the number of values for each interval given by the border values in the second parameter.
The values in the second parameter determine the upper boundaries of the intervals. The intervals include the upper boundaries. The returned array is a column vector and has one more element than the second parameter; the last element represents the number of all elements greater than the last value in second parameter. If the second parameter is empty, all values in the first parameter are counted.
- Syntax
FREQUENCY(Range data; Range bins)
- Parameters
- Comment: Floating point values, that should be counted.Type: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point values, representing the upper boundaries of the intervals.Type: A range of floating point values (like 1.3, 0.343, 253 )
The GAMMADIST() function returns the gamma distribution.
If the last parameter (cumulated) is 0, it calculates the density function; if it's 1, the distribution is returned.
The first three parameters must be positive.
- Syntax
GAMMADIST(number;alpha;beta;cumulated)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Alpha parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: Beta parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: Cumulated flagType: Whole number (like 1, 132, 2344)
- Examples
GAMMADIST(0.758;0.1;0.35;1) equals 0.995450
- Examples
GAMMADIST(0.758;0.1;0.35;0) equals 0.017179
The GAMMAINV() function returns the unique number x >= 0 such that GAMMAINV(x;alpha;beta;TRUE()) = p.
- Syntax
GAMMAINV(number;alpha;beta)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Alpha parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: Beta parameterType: A floating point value (like 1.3, 0.343, 253 )
- Examples
GAMMADIST(GAMMAINV(0.1;3;4);3;4) equals 0.1
- Examples
GAMMADIST(GAMMAINV(0.3;3;4);3;4) equals 0.3
The GAMMALN() function returns the natural logarithm of the gamma function: G(x). The number parameter must be positive.
- Syntax
GAMMALN(Number)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )
- Examples
GAMMALN(2) returns 0
The GAUSS() function returns the integral values for the standard normal cumulative distribution.
- Syntax
GAUSS(value)
- Parameters
- Comment: The number for which the integral value of standard normal distribution is to be calculatedType: A floating point value (like 1.3, 0.343, 253 )
- Examples
GAUSS(0.25) equals 0.098706
The GEOMEAN() function returns the geometric mean of the given arguments. This is equal to the Nth root of the product of the terms.
- Syntax
GEOMEAN(value; value;...)
- Parameters
- Comment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: Double
- Examples
GEOMEAN(A1:A5)
- Examples
GEOMEAN(21; 33; 54; 23) returns 30.45886
- Related Functions
HARMEAN
The HARMEAN() function returns the harmonic mean of the N data points (N divided by the sum of the inverses of the data points).
- Syntax
HARMEAN(value; value;...)
- Parameters
- Comment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: Double
- Examples
HARMEAN(A1:A5)
- Examples
HARMEAN(21; 33; 54; 23) returns 28.588
- Related Functions
GEOMEAN
The HYPGEOMDIST() function returns the hypergeometric distribution.
- Syntax
HYPGEOMDIST(x; n; M; N)
- Parameters
- Comment: Number of success in the sampleType: Whole number (like 1, 132, 2344)Comment: Number of trialsType: Whole number (like 1, 132, 2344)Comment: Number of success overallType: Whole number (like 1, 132, 2344)Comment: Population sizeType: Whole number (like 1, 132, 2344)
- Examples
HYPGEOMDIST(2; 5; 6; 20) returns 0.3522
The INTERCEPT() function calculates the interception of the linear regression line with the y axis.
- Syntax
INTERCEPT(y;x)
- Parameters
- Comment: y values (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: x values (array)Type: A floating point value (like 1.3, 0.343, 253 )
The INVBINO() function returns the negative binomial distribution. The first parameter is the number of trials, the second parameter is the number of failures, and the third is the probability of failure. The number of trials should be larger than the number of failures and the probability should be smaller or equal to 1.
- Syntax
INVBINO(trials;failure;prob_of_failure)
- Parameters
- Comment: Number of trialsType: Whole number (like 1, 132, 2344)Comment: Number of failuresType: Whole number (like 1, 132, 2344)Comment: Probability of failureType: Double
- Examples
INVBINO(12;3;0.2) returns 0.236223201
The KURT() function calculates an unbiased estimate of the kurtosis of a data set. You have to provide at least 4 values, otherwise an error is returned.
- Syntax
KURT(value; value;...)
- Parameters
- Comment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: Double
- Examples
KURT(A1:A5)
- Examples
KURT(21; 33; 54; 23) returns 1.344239
- Related Functions
KURTP
The KURTP() function calculates an population kurtosis of a data set. You have to provide at least 4 values, otherwise an error is returned.
- Syntax
KURTP(value; value;...)
- Parameters
- Comment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: DoubleComment: Floating point valuesType: Double
- Examples
KURTP(A1:A5)
- Examples
KURTP(21; 33; 54; 23) returns -1.021
- Related Functions
KURT
The LARGE() function returns the k-th largest value from the data set.
- Syntax
LARGE(range; k)
- Parameters
- Comment: Cell range of valuesType: DoubleComment: Position (from the largest)Type: Whole number (like 1, 132, 2344)
- Examples
A1: 3, A2: 1, A3: 5 => LARGE(A1:A3; 2) returns 3
The LEGACYFDIST() function returns the f-distribution.
- Syntax
LEGACYFDIST(number;degrees_freedom_1;degrees_freedom_2)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Degrees of freedom 1Type: Whole number (like 1, 132, 2344)Comment: Degrees of freedom 2Type: Whole number (like 1, 132, 2344)
- Examples
LEGACYFDIST(0.8;8;12) yields 0.61
The LOGINV() function returns the inverse of the lognormal cumulative distribution.
- Syntax
LOGINV(p; mean; stdev)
- Parameters
- Comment: ProbabilityType: A floating point value (like 1.3, 0.343, 253 )Comment: Mean value of the standard logarithmic distributionType: A floating point value (like 1.3, 0.343, 253 )Comment: Standard deviation of the standard logarithmic distributionType: A floating point value (like 1.3, 0.343, 253 )
- Examples
LOGINV(0.1;0;1) equals 0.2776
The LOGNORMDIST() function returns the cumulative lognormal distribution.
- Syntax
LOGNORMDIST(Number;MV;STD)
- Parameters
- Comment: Probability value for which the standard logarithmic distribution is to be calculatedType: A floating point value (like 1.3, 0.343, 253 )Comment: Mean value of the standard logarithmic distributionType: A floating point value (like 1.3, 0.343, 253 )Comment: Standard deviation of the standard logarithmic distributionType: A floating point value (like 1.3, 0.343, 253 )
- Examples
LOGNORMDIST(0.1;0;1) equals 0.01
The MEDIAN() function calculates the median of all the values given as parameters. You can calculate the median of a range like MEDIAN(A1:B5) or a list of values like MEDIAN(12; 5; 12.5). Blank cells will be considered as a zero, and cells with text will be ignored.
- Syntax
MEDIAN(value;value;...)
- Parameters
- Comment: Floating point value or range of valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point values or range of valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point values or range of valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point values or range of valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point values or range of valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
MEDIAN(12; 5; 5.5) equals 5.5
- Examples
MEDIAN(12; 7; 8;2) equals 7.5
The MODE() function returns the most frequently occurring value in the data set.
- Syntax
MODE(number; number2; ...)
- Parameters
- Comment: FloatType: DoubleComment: FloatType: DoubleComment: FloatType: DoubleComment: FloatType: Double
- Examples
MODE(12; 14; 12; 15) returns 12
The NEGBINOMDIST() function returns the negative binomial distribution.
- Syntax
NEGBINOMDIST(failures; success; prob_of_success)
- Parameters
- Comment: Number of failuresType: Whole number (like 1, 132, 2344)Comment: Number of successful trialsType: Whole number (like 1, 132, 2344)Comment: Probability of successType: Double
- Examples
NEGBINOMDIST(2;5;0.55) returns 0.152872629
The NORMDIST() function returns the normal cumulative distribution.
Number is the value of the distribution based on which the normal distribution is to be calculated.
MV is the linear middle of the distribution.
STD is the standard deviation of the distribution.
K = 0 calculates the density function; K = 1 calculates the distribution.
- Syntax
NORMDIST(Number;MV;STD;K)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Linear middle of the distributionType: A floating point value (like 1.3, 0.343, 253 )Comment: Standard deviation of the distributionType: A floating point value (like 1.3, 0.343, 253 )Comment: 0 = density, 1 = distributionType: Whole number (like 1, 132, 2344)
- Examples
NORMDIST(0.859;0.6;0.258;0) equals 0.934236
- Examples
NORMDIST(0.859;0.6;0.258;1) equals 0.842281
The NORMINV() function returns the inverse of the normal cumulative distribution. The number must be between 0 and 1 (non-inclusive) and STD must be positive.
- Syntax
NORMINV(number;MV;STD)
- Parameters
- Comment: Probability value for which the standard logarithmic distribution is to be calculatedType: A floating point value (like 1.3, 0.343, 253 )Comment: Middle value in the normal distributionType: A floating point value (like 1.3, 0.343, 253 )Comment: Standard deviation of the normal distributionType: A floating point value (like 1.3, 0.343, 253 )
- Examples
NORMINV(0.9;63;5) equals 69.41
The NORMSDIST() function returns the standard normal distribution.
- Syntax
NORMSDIST(Number)
- Parameters
- Comment: Value to which the standard normal distribution is calculatedType: A floating point value (like 1.3, 0.343, 253 )
- Examples
NORMSDIST(1) equals 0.84
The NORMSINV() function returns the inverse of the standard normal cumulative distribution. The number must be between 0 and 1 (non-inclusive).
- Syntax
NORMSINV(Number)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )
- Examples
NORMSINV(0.908789) returns 1.3333
The PEARSON() function calculates the correlation coefficient of two cell ranges. It is the same as the CORREL function.
- Syntax
PEARSON(range1; range2)
- Parameters
- Comment: Cell range of valuesType: DoubleComment: Second cell range of valuesType: Double
- Examples
PEARSON(A1:A3; B1:B3)
- Related Functions
CORREL
The PERCENTILE() function returns the x-th sample percentile of data values in Data. A percentile returns the scale value for a data series which goes from the smallest (alpha=0) to the largest value (alpha=1) of a data series. For alpha = 25%, the percentile means the first quartile; alpha = 50% is the MEDIAN. Blank cells will be considered as a zero, and cells with text will be ignored.
- Syntax
PERCENTILE(data;alpha)
- Parameters
- Comment: Range of valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: The percentile value between 0 and 1, inclusive.Type: A floating point value (like 1.3, 0.343, 253 )
- Related Functions
MEDIAN
The PERMUT() function returns the number of permutations. The first parameter is the number of elements, and the second parameter is the number of elements used in the permutation.
- Syntax
PERMUT(total;permutated)
- Parameters
- Comment: Total number of elementsType: Whole number (like 1, 132, 2344)Comment: Number of elements to permutateType: Whole number (like 1, 132, 2344)
- Examples
PERMUT(8;5) equals 6720
- Examples
PERMUT(1;1) equals 1
The PERMUTATIONA() function returns the number of ordered permutations when allowing repetition. The first parameter is the number of elements, and the second parameter is the number of elements to choose. Both parameters must be positive.
- Syntax
PERMUTATIONA(total;chosen)
- Parameters
- Comment: Total number of elementsType: Whole number (like 1, 132, 2344)Comment: Number of elements to chooseType: Whole number (like 1, 132, 2344)
- Examples
PERMUTATIONA(2,3) returns 8
- Examples
PERMUTATIONA(0,0) returns 1
The PHI() function returns value of the distribution function for a standard normal distribution.
- Syntax
PHI(value)
- Parameters
- Comment: The number for which the standard normal distribution is to be calculatedType: A floating point value (like 1.3, 0.343, 253 )
- Examples
PHI(0.25) equals 0.386668
The POISSON() function returns the Poisson distribution.
The lambda and number parameters must be positive.
Cumulative = 0 calculates the density function; cumulative = 1 calculates the distribution.
- Syntax
POISSON(number;lambda;cumulative)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Lambda parameter (the middle value)Type: A floating point value (like 1.3, 0.343, 253 )Comment: 0 = density, 1 = distributionType: Whole number (like 1, 132, 2344)
- Examples
POISSON(60;50;0) equals 0.020105
- Examples
POISSON(60;50;1) equals 0.927840
The RANK() function returns the rank of a number in a list of numbers.
Order specifies how to rank the numbers:
If 0 or omitted, Data is ranked in descending order.
If not 0, Data is ranked in ascending order.
- Syntax
RSQ(Value; Data; Order)
- Parameters
- Comment: ValueType: A floating point value (like 1.3, 0.343, 253 )Comment: Data (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: OrderType: A floating point value (like 1.3, 0.343, 253 )
- Examples
RANK (2;{1;2;3}) equals 2
The RSQ() function returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
If "arrayY" and "arrayX" are empty or have a different number of data points, then #N/A is returned.
- Syntax
RSQ(known Y; known X)
- Parameters
- Comment: known Y (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: known X (array)Type: A floating point value (like 1.3, 0.343, 253 )
The SKEW() function returns an estimate for skewness of a distribution
- Syntax
SKEW(number; number2; ...)
- Parameters
- Comment: FloatType: DoubleComment: FloatType: DoubleComment: FloatType: DoubleComment: FloatType: Double
- Examples
SKEW(11.4; 17.3; 21.3; 25.9; 40.1) returns 0.9768
- Related Functions
SKEWP
The SKEWP() function returns the population skewness of a distribution
- Syntax
SKEWP(number; number2; ...)
- Parameters
- Comment: FloatType: DoubleComment: FloatType: DoubleComment: FloatType: DoubleComment: FloatType: Double
- Examples
SKEWP(11.4; 17.3; 21.3; 25.9; 40.1) returns 0.6552
- Related Functions
SKEW
The SLOPE() function calculates the slope of the linear regression line.
- Syntax
SLOPE(y;x)
- Parameters
- Comment: y values (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: x values (array)Type: A floating point value (like 1.3, 0.343, 253 )
The SMALL() function returns the k-th smallest value from the data set.
- Syntax
SMALL(range; k)
- Parameters
- Comment: Cell range of valuesType: DoubleComment: Position (from the smallest)Type: Whole number (like 1, 132, 2344)
- Examples
A1: 3, A2: 1, A3: 5 => SMALL(A1:A3; 1) returns 1
The STANDARDIZE() function calculates a normalized value.
- Syntax
STANDARDIZE(x; mean, stdev)
- Parameters
- Comment: Number to be normalizedType: DoubleComment: Mean of the distributionType: DoubleComment: Standard deviationType: Double
- Examples
STANDARDIZE(4; 3; 7) returns 0.1429
The STDEV() function returns the estimate standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value.
- Syntax
STDEV(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
STDEV(6;7;8) equals 1
- Related Functions
STDEVP
The STDEVA() function returns the estimate standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value. If a referenced cell contains text or contains the boolean value FALSE, it is counted as 0. If the boolean value is TRUE it is counted as 1.
- Syntax
STDEVA(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
STDEVA(6; 7; A1; 8) equals 1, if A1 is empty
- Examples
STDEVA(6; 7; A1; 8) equals 3.109, if A1 is TRUE
- Related Functions
STDEV STDEVP
The STDEVP() function returns the standard deviation based on an entire population
- Syntax
STDEVP(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
STDEVP(6;7;8) equals 0.816497...
- Related Functions
STDEV
The STDEVPA() function returns standard deviation based on an entire population. If a referenced cell contains text or contains the boolean value FALSE, it is counted as 0. If the boolean value is TRUE it is counted as 1.
- Syntax
STDEVPA(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
STDEVPA(6; 7; A1; 8) equals 0.816497..., if A1 is empty
- Examples
STDEVPA(6; 7; A1; 8) equals 2.69..., if A1 is TRUE
- Examples
STDEVPA(6; 7; A1; 8) equals 3.11..., if A1 is FALSE
- Related Functions
STDEV STDEVP
The STEYX() function calculates the standard error of the predicted y value for each x in the regression.
- Syntax
SLOPE(y;x)
- Parameters
- Comment: y values (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: x values (array)Type: A floating point value (like 1.3, 0.343, 253 )
The SUMPRODUCT() function (SUM(X*Y)) returns the sum of the product of these values. The number of values in the two arrays should be equal. Otherwise this function returns Err.
- Syntax
SUMPRODUCT(array1;array2)
- Parameters
- Comment: Value (array)Type: DoubleComment: Value (array)Type: Double
- Examples
SUMPRODUCT(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns 31
The SUMX2MY2() function (SUM(X^2-Y^2)) returns the difference of the squares of these values. The number of values in the two arrays should be equal. Otherwise this function returns Err.
- Syntax
SUMX2MY2(array1;array2)
- Parameters
- Comment: Value (array)Type: DoubleComment: Value (array)Type: Double
- Examples
SUMX2MY2(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns -5
The SUMX2PY2() function (SUM(X^2+Y^2)) returns the sum of the squares of these values. The number of values in the two arrays should be equal. Otherwise this function returns Err.
- Syntax
SUMX2PY2(array1;array2)
- Parameters
- Comment: Value (array)Type: DoubleComment: Value (array)Type: Double
- Examples
SUMX2PY2(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns 63
The SUMXMY2() function (SUM((X-Y)^2)) returns the square of the differences of these values. The number of values in the two arrays should be equal. Otherwise this function returns Err.
- Syntax
SUMXMY2(array1;array2)
- Parameters
- Comment: Value (array)Type: DoubleComment: Value (array)Type: Double
- Examples
SUMXMY2(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns 1
The TDIST() function returns the t-distribution.
Mode = 1 returns the one-tailed test, Mode = 2 returns the two-tailed test.
- Syntax
TDIST(number;degrees_freedom;mode)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Degrees of freedom for the t-distributionType: Whole number (like 1, 132, 2344)Comment: Mode (1 or 2)Type: Whole number (like 1, 132, 2344)
- Examples
TDIST(12;5;1) returns 0.000035
The TREND() function calculates a sequence of values based on a linear regression of known value pairs.
Constraints: COUNT(knownY) = COUNT(knownX).
- Syntax
TREND(knownY[;knownX[;newX[;allowOffset = TRUE]]])
- Parameters
- Comment: KnownYType: A floating point value (like 1.3, 0.343, 253 )Comment: KnownXType: A floating point value (like 1.3, 0.343, 253 )Comment: NumberSequence newXType: A floating point value (like 1.3, 0.343, 253 )Comment: allowOffsetType: A truth value (TRUE or FALSE)
The TRIMMEAN() function calculates the mean of a data set's fraction.
- Syntax
TRIMMEAN(dataSet; cutOffFraction)
- Parameters
- Comment: dataSetType: A floating point value (like 1.3, 0.343, 253 )Comment: cutOffFractionType: A floating point value (like 1.3, 0.343, 253 )
The TTEST() function calculates the probability of a t-test.
- Syntax
TTEST(x; y; type; mode)
- Parameters
- Comment: x (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: y (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: typeType: Whole number (like 1, 132, 2344)Comment: modeType: Whole number (like 1, 132, 2344)
The VAR() function calculates the estimates variance based on a sample.
- Syntax
VAR(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
VAR(12;5;7) equals 13
- Examples
VAR(15;80;3) equals 1716.333...
- Examples
VAR(6;7;8) equals 1
- Related Functions
VARIANCE VARA VARP VARPA
The VARA() function calculates the variance based on a sample.
- Syntax
VARA(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
VARA(12;5;7) equals 13
- Examples
VARA(15;80;3) equals 1716.333...
- Examples
VARA(6;7;8) equals 1
- Related Functions
VAR VARP VARPA
The VARIANCE() function calculates the estimates variance based on a sample. It's the same as the VAR function.
- Syntax
VARIANCE(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
VARIANCE(12;5;7) equals 13
- Examples
VARIANCE(15;80;3) equals 1716.333...
- Examples
VARIANCE(6;7;8) equals 1
- Related Functions
VAR VARA VARP VARPA
The VARP() function calculates the variance based on an entire population.
- Syntax
VARP(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
VARP(12;5;7) equals 8.666...
- Examples
VARP(15;80;3) equals 1144.22...
- Examples
VARP(6;7;8) equals 0.6666667...
- Related Functions
VAR VARA VARPA
The VARPA() function calculates the variance based on an entire population. Text and boolean values that evaluate to FALSE are counted as 0, boolean value that evaluate to TRUE are counted as 1.
- Syntax
VARPA(value;value;...)
- Parameters
- Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )Comment: Floating point valuesType: A range of floating point values (like 1.3, 0.343, 253 )
- Examples
VARPA(12;5;7) equals 8.666...
- Examples
VARPA(15;80;3) equals 1144.22...
- Examples
VARPA(6;7;8) equals 0.6666667...
- Related Functions
VAR VARA VARP
The WEIBULL() function returns the Weibull distribution.
The alpha and beta parameters must be positive, the number (first parameter) must be non-negative.
Cumulative = 0 calculates the density function; cumulative = 1 calculates the distribution.
- Syntax
WEIBULL(number;alpha;beta;cumulative)
- Parameters
- Comment: NumberType: A floating point value (like 1.3, 0.343, 253 )Comment: Alpha parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: Beta parameterType: A floating point value (like 1.3, 0.343, 253 )Comment: 0 = density, 1 = distributionType: Whole number (like 1, 132, 2344)
- Examples
WEIBULL(2;1;1;0) equals 0.135335
- Examples
WEIBULL(2;1;1;1) equals 0.864665
The ZTEST() function calculates the two tailed probability of a z-test with normal distribution.
Performs a test of the null hypothesis, that sample is a sample of a normal distributed random variable with mean mean and standard deviation sigma. A return value of 1 indicates, that the null hypothesis is rejected, i.e. the sample is not a random sample of the normal distribution. If sigma is omitted, it is estimated from sample, using STDEV.
- Syntax
ZTEST(x; mean; standardDeviation)
- Parameters
- Comment: x (array)Type: A floating point value (like 1.3, 0.343, 253 )Comment: meanType: A floating point value (like 1.3, 0.343, 253 )Comment: standardDeviationType: A floating point value (like 1.3, 0.343, 253 )
The ASC() function returns the half-width characters corresponding to the full-width argument.
- Syntax
ASC(text)
- Parameters
- Comment: Full width charactersType: Text
- Related Functions
JIS
The BAHTTEXT() function converts a number to a text in Thai characters (baht).
- Syntax
BAHTTEXT(number)
- Parameters
- Comment: NumberType: Whole number (like 1, 132, 2344)
- Examples
BAHTTEXT(23) returns "ยสบสามบาทถวน"
The CHAR() function returns the character specified by a number.
- Syntax
CHAR(code)
- Parameters
- Comment: Character codeType: Whole number (like 1, 132, 2344)
- Examples
CHAR(65) returns "A"
- Related Functions
CODE
The CLEAN() function removes every non-printable character from the string
- Syntax
CLEAN(text)
- Parameters
- Comment: Source stringType: Text
- Examples
CLEAN(AsciiToChar(7) + "HELLO") returns "HELLO"
The CODE() function returns a numeric code for the first character in a text string.
- Syntax
CODE(text)
- Parameters
- Comment: TextType: Text
- Examples
CODE("KDE") returns 75
- Related Functions
CHAR
The COMPARE() function returns 0 if the two strings are equal; -1 if the first one is lower in value than the second one; otherwise it returns 1.
- Syntax
COMPARE(string1; string2; true|false)
- Parameters
- Comment: First stringType: TextComment: String to compare withType: TextComment: Compare case-sensitive (true/false)Type: A truth value (TRUE or FALSE)
- Examples
COMPARE("Calligra"; "Calligra"; true) returns 0
- Examples
COMPARE("calligra"; "Calligra"; true) returns 1
- Examples
COMPARE("kspread"; "Calligra"; false) returns 1
- Related Functions
EXACT
The CONCATENATE() function returns a string which is the concatenation of the strings passed as parameters.
- Syntax
CONCATENATE(value;value;...)
- Parameters
- Comment: String valuesType: A range of stringsComment: String valuesType: A range of stringsComment: String valuesType: A range of stringsComment: String valuesType: A range of stringsComment: String valuesType: A range of strings
- Examples
CONCATENATE("Sheets";"Calligra";"KDE") returns "SheetsCalligraKDE"
The DOLLAR() function converts a number to text using currency format, with the decimals rounded to the specified place. Although the name is DOLLAR, this function will do the conversion according to the current locale.
- Syntax
DOLLAR(number;decimals)
- Parameters
- Comment: NumberType: DoubleComment: DecimalsType: Whole number (like 1, 132, 2344)
- Examples
DOLLAR(1403.77) returns "$ 1,403.77"
- Examples
DOLLAR(-0.123;4) returns "$-0.1230"
The EXACT() function returns True if these two strings are equal. Otherwise, it returns False.
- Syntax
EXACT(string1;string2)
- Parameters
- Comment: StringType: TextComment: StringType: Text
- Examples
EXACT("Calligra";"Calligra") returns True
- Examples
EXACT("KSpread";"Calligra") returns False
- Related Functions
COMPARE
The FIND() function finds one text string (find_text) within another text string (within_text) and returns the number of the starting point of find_text, from the leftmost character of within_text.
Parameter start_num specifies the character at which to start the search. The first character is character number 1. If start_num is omitted, it is assumed to be 1.
You can also use function SEARCH, but unlike SEARCH, FIND is case-sensitive and does not allow wildcard characters.
- Syntax
FIND(find_text;within_text;start_num)
- Parameters
- Comment: The text you want to findType: TextComment: The text which may contain find_textType: TextComment: Specifies index to start the searchType: Whole number (like 1, 132, 2344)
- Examples
FIND("Cal";"Calligra") returns 1
- Examples
FIND("i";"Calligra") returns 5
- Examples
FIND("a";"Sheets in Calligra";4) returns 12
- Related Functions
FINDB SEARCH REPLACE SEARCHB REPLACEB
The FINDB() function finds one text string (find_text) within another text string (within_text) and returns the number of the starting point of find_text, from the leftmost character of within_text using byte positions.
Parameter BytePosition specifies the character at which to start the search. The first character is character number 2. If start_num is omitted, it is assumed to be 2.
- Syntax
FINDB(find_text;within_text;BytePosition Start)
- Parameters
- Comment: The text you want to findType: TextComment: The text which may contain find_textType: TextComment: Specifies byte position to start the searchType: Whole number (like 1, 132, 2344)
- Related Functions
FIND SEARCH REPLACE SEARCHB REPLACEB
The FIXED() function rounds a number to the specified number of decimals, formats the number in decimal format string, and returns the result as text. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2. If optional parameter no_commas is True, thousand separators will not show up.
- Syntax
FIXED(number;decimals;no_commas)
- Parameters
- Comment: NumberType: DoubleComment: DecimalsType: Whole number (like 1, 132, 2344)Comment: No_commasType: A truth value (TRUE or FALSE)
- Examples
FIXED(1234.567;1) returns "1,234.6"
- Examples
FIXED(1234.567;1;FALSE) returns "1234.6"
- Examples
FIXED(44.332) returns "44.33"
The JIS() function returns the full-width characters corresponding to the half-width argument.
- Syntax
JIS(text)
- Parameters
- Comment: Half-width charactersType: Text
- Related Functions
ASC
The LEFT() function returns a substring that contains the 'length' leftmost characters of the string. The whole string is returned if 'length' exceeds the length of the string. It is an error for the number of characters to be less than 0.
The LEFTB() function returns a substring that contains the 'length' leftmost characters of the string using byte positions. The whole string is returned if 'length' exceeds the length of the string. It is an error for the number of characters to be less than 0.
The LEN() function returns the length of the string.
- Syntax
LEN(text)
- Parameters
- Comment: StringType: Text
- Examples
LEN("hello") returns 5
- Examples
LEN("KSpread") returns 7
- Related Functions
LENB
The LENB() function returns the length of the string using byte positions.
- Syntax
LENB(text)
- Parameters
- Comment: StringType: Text
The LOWER() function converts a string to lower case.
The MID() function returns a substring that contains 'length' characters of the string, starting at 'position' index.
- Syntax
MID(text;position;length)
- Parameters
- Comment: Source stringType: TextComment: PositionType: Whole number (like 1, 132, 2344)Comment: LengthType: Whole number (like 1, 132, 2344)
- Examples
MID("Calligra";2;3) returns "all"
- Examples
MID("Calligra";2) returns "alligra"
- Related Functions
LEFT RIGHT LEFTB RIGHTB MIDB
The MIDB() function returns a substring that contains 'length' characters of the string, starting at 'position' index using byte positions.
The PROPER() function converts the first letter of each word to uppercase and the rest of the letters to lowercase.
- Syntax
PROPER(string)
- Parameters
- Comment: StringType: Text
- Examples
PROPER("this is a title") returns "This Is A Title"
Returns a part of the string that matches a regular expression. If the string does not match the given regular expression, value specified as default is returned.
If a back-reference is provided, then the value of that back-reference is returned.
If no default value is given, an empty string is assumed. If no back-reference is given, 0 is assumed (so that entire matching part is returned).
- Syntax
REGEXP(text; regexp; default; backref)
- Parameters
- Comment: Searched textType: TextComment: Regular expressionType: TextComment: Default value (optional)Type: TextComment: Back-reference (optional)Type: Number
- Examples
REGEXP("Number is 15.";"[0-9]+") = "15"
- Examples
REGEXP("15, 20, 26, 41";"([0-9]+), *[0-9]+$";"";1) = "26"
Replaces all matches of a regular expression with the replacement text
- Syntax
REGEXPRE(text; regexp; replacement)
- Parameters
- Comment: Searched textType: TextComment: Regular expressionType: TextComment: ReplacementType: Text
- Examples
REGEXPRE("14 and 15 and 16";"[0-9]+";"num") returns "num and num and num"
The REPLACE() function replaces part of a text string with a different text string.
- Syntax
REPLACE(text;position;length;new_text)
- Parameters
- Comment: Text which you want to replace some charactersType: TextComment: Position of the characters to replaceType: Whole number (like 1, 132, 2344)Comment: Number of characters to replaceType: Whole number (like 1, 132, 2344)Comment: The text that will replace characters in old textType: Text
- Examples
REPLACE("abcdefghijk";6;5;"-") returns "abcde-k"
- Examples
REPLACE("2002";3;2;"03") returns "2003"
- Related Functions
FIND MID FINDB MIDB
The REPLACEB() function replaces part of a text string with a different text string using byte positions.
- Syntax
REPLACEB(text;BytePosition;ByteLength Len;new_text)
- Parameters
- Comment: Text which you want to replace some characters using byte positionType: TextComment: Byte position of the characters to replaceType: Whole number (like 1, 132, 2344)Comment: The byte length of characters to replaceType: Whole number (like 1, 132, 2344)Comment: The text that will replace characters in old textType: Text
- Related Functions
FINDB MIDB FIND MID
The REPT() function repeats the first parameter as many times as by the second parameter. The second parameter must not be negative, and this function will return an empty string if the second parameter is zero (or rounds down to zero).
- Syntax
REPT(text;count)
- Parameters
- Comment: Source stringType: TextComment: Count of repetitionsType: Whole number (like 1, 132, 2344)
- Examples
REPT("KSpread";3) returns "KSpreadKSpreadKSpread"
- Examples
REPT("KSpread";0) returns ""
The RIGHT() function returns a substring that contains the 'length' rightmost characters of the string. The whole string is returned if 'length' exceeds the length of the string.
The RIGHTB() function returns a substring that contains the 'length' rightmost characters of the string using byte positions. The whole string is returned if 'length' exceeds the length of the string.
The ROT13() function encrypts text by replacing each letter with the one 13 places along in the alphabet. If the 13th position is beyond the letter Z, it begins again at A (rotation).
By applying the encryption function again to the resulting text, you can decrypt the text.
- Syntax
ROT13(Text)
- Parameters
- Comment: TextType: Text
- Examples
ROT13("KSpread") returns "XFcernq"
- Examples
ROT13("XFcernq") returns "KSpread"
The SEARCH() function finds one text string (find_text) within another text string (within_text) and returns the number of the starting point of find_text, from the leftmost character of within_text.
You can use wildcard characters, question mark (?) and asterisk (*). A question mark matches any single character, an asterisk matches any sequences of characters.
Parameter start_num specifies the character at which to start the search. The first character is character number 1. If start_num is omitted, it is assumed to be 1. SEARCH does not distinguish between uppercase and lowercase letters.
- Syntax
SEARCH(find_text;within_text;start_num)
- Parameters
- Comment: The text you want to findType: TextComment: The text which may contain find_textType: TextComment: Specified index to start the searchType: Whole number (like 1, 132, 2344)
- Examples
SEARCH("e";"Statements";6) returns 7
- Examples
SEARCH("margin";"Profit Margin") returns 8
- Related Functions
FIND FINDB SEARCHB
The SEARCHB() function finds one text string (find_text) within another text string (within_text) and returns the number of the starting point of find_text, from the leftmost character of within_text using byte positions.
You can use wildcard characters, question mark (?) and asterisk (*). A question mark matches any single character, an asterisk matches any sequences of characters.
Parameter BytePosition specifies the character at which to start the search. The first character is character number 2. If BytePosition is omitted, it is assumed to be 2. SEARCHB does not distinguish between uppercase and lowercase letters.
The SLEEK() function removes all spaces from the string.
- Syntax
SLEEK(text)
- Parameters
- Comment: Source stringType: Text
- Examples
SLEEK("This is some text ") returns "Thisissometext"
- Related Functions
TRIM
The SUBSTITUTE() substitutes new_text for old_text in a text string. If instance_num is specified, only that instance of old_text is replaced. Otherwise, every occurrence of old_text is changed to new_text. Use SUBSTITUTE when you want to replace specific text, use REPLACE when you want to replace any text that occurs in a specific location.
- Syntax
SUBSTITUTE(text; old_text; new_text; instance_num)
- Parameters
- Comment: Text for which you want to substituteType: TextComment: Part of text you want to replaceType: TextComment: New text which will be replacementType: TextComment: Which occurrence to replaceType: Whole number (like 1, 132, 2344)
- Examples
SUBSTITUTE("Cost Data";"Cost";"Sales") returns "Sales Data"
- Examples
SUBSTITUTE("Qtr 1, 2001";"1";"3";1) returns "Qtr 3, 2001"
- Examples
SUBSTITUTE("Qtr 1, 2001";"1";"3";4) returns "Qtr 3, 2003"
- Related Functions
REPLACE REPLACEB FIND FINDB
The T() function returns the text referred to by value. If value is, or refers to, text then T returns value. If value does not refer to text then T returns empty text.
- Syntax
T(value)
- Parameters
- Comment: ValueType: Any kind of value
- Examples
T("Calligra") returns "Calligra"
- Examples
T(1.2) returns "" (empty text)
The TEXT() function converts a value to text.
- Syntax
TEXT(value)
- Parameters
- Comment: ValueType: Any kind of value
- Examples
TEXT(1234.56) returns "1234.56"
- Examples
TEXT("KSpread") returns "KSpread"
The TOGGLE() function changes lowercase characters to uppercase and uppercase characters to lowercase.
The TRIM() function returns text with only single spaces between words.
- Syntax
TRIM(text)
- Parameters
- Comment: StringType: Text
- Examples
TRIM(" hello KSpread ") returns "hello KSpread"
The UNICHAR() function returns the character specified by a unicode code point.
The UNICODE() function returns a unicode code point for the first character in a text string.
The UPPER() function converts a string to upper case.
The ACOS() function returns the arc cosine in radians and the value is mathematically defined to be 0 to PI (inclusive).
- Syntax
ACOS(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ACOS(0.8) equals 0.6435011
- Examples
ACOS(0) equals 1.57079633
- Related Functions
COS
The ACOSH() function calculates the inverse hyperbolic cosine of x. That is the value whose hyperbolic cosine is x. If x is less than 1.0, acosh() returns not-a-number (NaN) and errno is set.
- Syntax
ACOSH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ACOSH(5) equals 2.29243167
- Examples
ACOSH(0) equals NaN
- Related Functions
COSH
The ACOT() function returns the inverse cotangent of a number.
- Syntax
ACOT(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ACOT(0) equals 1.57079633
The ASIN() function returns the arc sine in radians and the value is mathematically defined to be -PI/2 to PI/2 (inclusive).
- Syntax
ASIN(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ASIN(0.8) equals 0.92729522
- Examples
ASIN(0) equals 0
- Related Functions
SIN
The ASINH() function calculates the inverse hyperbolic sine of x; that is the value whose hyperbolic sine is x.
- Syntax
ASINH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ASINH(0.8) equals 0.73266826
- Examples
ASINH(0) equals 0
- Related Functions
SINH
The ATAN() function returns the arc tangent in radians and the value is mathematically defined to be -PI/2 to PI/2 (inclusive).
This function calculates the arc tangent of the two variables x and y. It is similar to calculating the arc tangent of y/x, except that the signs of both arguments are used to determine the quadrant of the result.
- Syntax
ATAN2(value;value)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ATAN2(0.5;1.0) equals 1.107149
- Examples
ATAN2(-0.5;2.0) equals 1.815775
- Related Functions
ATAN
The ATANH() function calculates the inverse hyperbolic tangent of x; that is the value whose hyperbolic tangent is x. If the absolute value of x is greater than 1.0, ATANH() returns not-a-number (NaN).
- Syntax
ATANH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
ATANH(0.8) equals 1.09861229
- Examples
ATANH(0) equals 0
- Related Functions
TANH
The COS() function returns the cosine of x, where x is given in radians.
The COSH() function returns the hyperbolic cosine of x, which is defined mathematically as (exp(x) + exp(-x)) / 2.
- Syntax
COSH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
COSH(0.8) equals 1.33743495
- Examples
COSH(0) equals 1
- Related Functions
ACOSH
The CSC() function returns the cosecant of x, where x is given in radians.
- Syntax
CSC(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
CSC(PI()/2) equals 1
The CSCH() function returns the hyperbolic cosecant of x, where x is given in radians.
- Syntax
CSCH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
CSCH(PI()/2) equals 0.434537208...
This function transforms a radian angle to a degree angle.
- Syntax
DEGREES(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
DEGREES(0.78) equals 44.69
- Examples
DEGREES(1) equals 57.29
- Related Functions
RADIANS
The PI() function returns the value of PI.
- Syntax
PI()
- Parameters
- Examples
PI() equals 3.141592654...
This function transforms a degree angle to a radian angle.
- Syntax
RADIANS(Float)
- Parameters
- Comment: Angle (degrees)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
RADIANS(75) equals 1.308
- Examples
RADIANS(90) equals 1.5707
- Related Functions
DEGREES
The SEC() function returns the secant of x, where x is given in radians.
- Syntax
SEC(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
SEC(0) equals 1
The SECH() function returns the hyperbolic secant of x, where x is given in radians.
- Syntax
SECH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
SECH(0) equals 1
The SIN() function returns the sine of x, where x is given in radians.
The SINH() function returns the hyperbolic sine of x, which is defined mathematically as (exp(x) - exp(-x)) / 2.
- Syntax
SINH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
SINH(0.8) equals 0.88810598
- Examples
SINH(0) equals 0
- Related Functions
ASINH
The TAN() function returns the tangent of x, where x is given in radians.
- Syntax
TAN(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
TAN(0.7) equals 0.84228838
- Examples
TAN(0) equals 0
- Related Functions
ATAN
The TANH() function returns the hyperbolic tangent of x, which is defined mathematically as sinh(x)/cosh(x).
- Syntax
TANH(Float)
- Parameters
- Comment: Angle (radians)Type: A floating point value (like 1.3, 0.343, 253 )
- Examples
TANH(0.8) equals 0.66403677
- Examples
TANH(0) equals 0
- Related Functions
ATANH