Chapter 8. Functions

Calligra Sheets has a huge range of built in mathematical and other functions that can be used in a formula cell.

Supported Functions

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 

Bit Operations

BITAND

The BITAND() function performs a bit-wise AND operation for the two integer parameters.

Return type: Whole number (like 1, 132, 2344)
Syntax

BITAND(value; value)

Parameters
Comment: First number
Type: Whole number (like 1, 132, 2344)
Comment: Second number
Type: 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 

BITLSHIFT

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

BITLSHIFT(value; shift size)

Parameters
Comment: First number
Type: Whole number (like 1, 132, 2344)
Comment: Amount to left shift by
Type: Whole number (like 1, 132, 2344)
Related Functions
BITLSHIFT 

BITOR

The BITOR() function performs a bit-wise OR operation for the two integer parameters.

Return type: Whole number (like 1, 132, 2344)
Syntax

BITOR(value; value)

Parameters
Comment: First number
Type: Whole number (like 1, 132, 2344)
Comment: Second number
Type: 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 

BITRSHIFT

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

BITRSHIFT(value; shift size)

Parameters
Comment: First number
Type: Whole number (like 1, 132, 2344)
Comment: Amount to right shift by
Type: Whole number (like 1, 132, 2344)
Related Functions
BITLSHIFT 

BITXOR

The BITXOR() function performs a bit-wise exclusive-OR operation for the two integer parameters.

Return type: Whole number (like 1, 132, 2344)
Syntax

BITXOR(value; value)

Parameters
Comment: First number
Type: Whole number (like 1, 132, 2344)
Comment: Second number
Type: 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 

Conversion

ARABIC

The ARABIC() function converts a roman numeral into a number.

Return type: Whole number (like 1, 132, 2344)
Syntax

ARABIC(Numeral)

Parameters
Comment: Numeral
Type: Text
Examples

ARABIC("IV") returns 4

Examples

ARABIC("XCIX") returns 99

Related Functions
ROMAN 

ASCIITOCHAR

The ASCIITOCHAR() function returns the character for each given ASCII code

Return type: Text
Syntax

ASCIITOCHAR(value)

Parameters
Comment: The ASCII values to convert
Type: Whole number (like 1, 132, 2344)
Examples

ASCIITOCHAR(118) returns "v"

Examples

ASCIITOCHAR(75; 68; 69) returns "KDE"

BOOL2INT

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

BOOL2INT(value)

Parameters
Comment: Bool value to convert
Type: A truth value (TRUE or FALSE)
Examples

BOOL2INT(True) returns 1

Examples

BOOL2INT(False) returns 0

Related Functions
INT2BOOL 

BOOL2STRING

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

Return type: Text
Syntax

BOOL2STRING(value)

Parameters
Comment: Bool value to convert
Type: A truth value (TRUE or FALSE)
Examples

BOOL2STRING(true) returns "True"

Examples

BOOL2STRING(false) returns "False"

Examples

upper(BOOL2STRING(find("nan";"banana"))) returns TRUE

CARX

The CARX() function returns the X position corresponding to the position of a point in a polar landmark.

Return type: Double
Syntax

CARX(Radius;Angle)

Parameters
Comment: Radius
Type: Double
Comment: Angle (radians)
Type: Double
Examples

CARX(12;1.5707) returns 0.00115592

Examples

CARX(12;0) returns 12

Related Functions
CARY 
POLA 
POLR 

CARY

The CARY() function returns the Y position corresponding to the position of a point in a polar landmark.

Return type: Double
Syntax

CARY(Radius;Angle)

Parameters
Comment: Radius
Type: Double
Comment: Angle (radians)
Type: Double
Examples

CARY(12;1.5707) returns 12

Examples

CARY(12;0) returns 0

Related Functions
CARX 
POLA 
POLR 

CHARTOASCII

The CHARTOASCII() function returns the ASCII code for the given character.

Return type: Whole number (like 1, 132, 2344)
Syntax

CHARTOASCII(value)

Parameters
Comment: A one character string to convert
Type: Text
Examples

CHARTOASCII("v") returns 118

Examples

CHARTOASCII(r) is an error. The character must be in quotes.

DECSEX

The DECSEX() function converts a double value to a time value.

Return type: Double
Syntax

DECSEX(double)

Parameters
Comment: Value
Type: Double
Examples

DECSEX(1.6668) returns 1:40

Examples

DECSEX(7.8) returns 7:47

INT2BOOL

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.

Return type: A truth value (TRUE or FALSE)
Syntax

INT2BOOL(value)

Parameters
Comment: Integer value to convert
Type: 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 

NUM2STRING

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.

Return type: Text
Syntax

NUM2STRING(value)

Parameters
Comment: Number to convert into string
Type: 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 

POLA

The POLA() function returns the angle (in radians) corresponding to the position of a point in a cartesian landmark.

Return type: Double
Syntax

POLA(X;Y)

Parameters
Comment: Value in X
Type: Double
Comment: Value in Y
Type: Double
Examples

POLA(12;12) returns 0.78539816

Examples

POLA(12;0) returns 0

Examples

POLA(0;12) returns 1.5707

Related Functions
POLR 
CARX 
CARY 

POLR

The POLR() function returns the radius corresponding to the position of a point in a cartesian landmark.

Return type: Double
Syntax

POLR(X;Y)

Parameters
Comment: Value in X
Type: Double
Comment: Value in Y
Type: Double
Examples

POLR(12;12) returns 16.9705

Examples

POLR(12;0) returns 12

Related Functions
POLA 
CARX 
CARY 

ROMAN

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.

Return type: Text
Syntax

ROMAN(Number)

Parameters
Comment: Number
Type: Whole number (like 1, 132, 2344)
Comment: Format
Type: Whole number (like 1, 132, 2344)
Examples

ROMAN(99) returns "XCIX"

Examples

ROMAN(-55) returns "Err"

Related Functions
ARABIC 

SEXDEC

The SEXDEC() function returns a decimal value. You can also supply a time value.

Return type: Double
Syntax

SEXDEC(time value) or SEXDEC(hours;minutes;seconds)

Parameters
Comment: Hours
Type: Whole number (like 1, 132, 2344)
Comment: Minutes
Type: Whole number (like 1, 132, 2344)
Comment: Seconds
Type: Whole number (like 1, 132, 2344)
Examples

SEXDEC(1;5;7) returns 1.0852778

Examples

DECSEX("8:05") returns 8.08333333

STRING

The STRING() function returns a string value for a given number. It is the same as the NUM2STRING function.

Return type: Text
Syntax

Parameters
Comment: Number to convert into string
Type: A floating point value (like 1.3, 0.343, 253 )
Related Functions
NUM2STRING 

Database

DAVERAGE

Calculates the average in a column of a database specified by a set of conditions for values that are numbers

Return type: FLOAT
Syntax

DAVERAGE(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DAVERAGE(A1:C5; "Salary"; A9:A11)

DCOUNT

Counts the cells containing numeric values in a column of a database specified by a set of conditions.

Return type: FLOAT
Syntax

DCOUNT(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DCOUNT(A1:C5; "Salary"; A9:A11)

Related Functions
DCOUNTA 

DCOUNTA

Counts the cells containing numeric or alphanumeric values in a column of a database specified by a set of conditions.

Return type: FLOAT
Syntax

DCOUNTA(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DCOUNTA(A1:C5; "Salary"; A9:A11)

Related Functions
DCOUNT 

DGET

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.

Return type: FLOAT
Syntax

DGET(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DGET(A1:C5; "Salary"; A9:A11)

DMAX

Returns the largest value in a column of a database specified by a set of conditions.

Return type: FLOAT
Syntax

DMAX(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DMAX(A1:C5; "Salary"; A9:A11)

Related Functions
DMIN 

DMIN

Returns the smallest values in a column of a database specified by a set of conditions.

Return type: FLOAT
Syntax

DMIN(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DMIN(A1:C5; "Salary"; A9:A11)

Related Functions
DMAX 

DPRODUCT

Returns the product of all numeric values in a column of a database specified by a set of conditions.

Return type: FLOAT
Syntax

DPRODUCT(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DPRODUCT(A1:C5; "Salary"; A9:A11)

DSTDEV

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.

Return type: FLOAT
Syntax

DSTDEV(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DSTDEV(A1:C5; "Salary"; A9:A11)

Related Functions
DSTDEVP 

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.

Return type: FLOAT
Syntax

DSTDEVP(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DSTDEVP(A1:C5; "Salary"; A9:A11)

Related Functions
DSTDEV 

DSUM

Sums up the numbers in a column of a database specified by a set of conditions.

Return type: FLOAT
Syntax

DSUM(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DSUM(A1:C5; "Salary"; A9:A11)

DVAR

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.

Return type: FLOAT
Syntax

DVAR(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DVAR(A1:C5; "Salary"; A9:A11)

Related Functions
DVARP 

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.

Return type: FLOAT
Syntax

DVARP(Database; "Header"; Conditions)

Parameters
Comment: Range marking the database
Type: A range of strings
Comment: String marking the column in the database
Type: Text
Comment: Range marking the conditions
Type: A range of strings
Examples

DVARP(A1:C5; "Salary"; A9:A11)

Related Functions
DVAR 

GETPIVOTDATA

Fetches summary data from a pivot table.

Return type: FLOAT
Syntax

GETPIVOTDATA(Database; "Sales")

Parameters
Comment: Range containing the pivot table
Type: A range of strings
Comment: Name of the field of which you want the summary data
Type: Text

Date & Time

CURRENTDATE

The CURRENTDATE() function returns the current date. It is equivalent to the TODAY function.

Return type: Date
Syntax

CURRENTDATE()

Parameters

Examples

CURRENTDATE() returns "Saturday 13 April 2002"

Related Functions
CURRENTTIME 
TODAY 

CURRENTDATETIME

The CURRENTDATETIME() function returns the current date and time.

Return type: Date
Syntax

CURRENTDATETIME()

Parameters

Examples

CURRENTDATETIME() returns "Saturday 13 April 2002 19:12:01"

CURRENTTIME

The CURRENTTIME() function returns the current time formatted with local parameters.

Return type: Date
Syntax

CURRENTTIME()

Parameters

Examples

CURRENTTIME() returns "19:12:01"

DATE

The DATE() function returns the date formatted with local parameters.

Return type: Text
Syntax

DATE(year;month;date)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Comment: Month
Type: Whole number (like 1, 132, 2344)
Comment: Day
Type: Whole number (like 1, 132, 2344)
Examples

DATE(2000;5;5) returns Friday 05 May 2000

DATE2UNIX

DATE2UNIX() function converts a date and time value to unix time.

A unix time is the number of seconds after midnight January 1st, 1970.

Return type: Whole number (like 1, 132, 2344)
Syntax

DATE2UNIX(date)

Parameters
Comment: Date
Type: Text
Examples

DATE2UNIX("01/01/2000") returns 946,684,800

DATEDIF

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

Return type: Whole number (like 1, 132, 2344)
Syntax

DATEDIF(first date; second date; interval)

Parameters
Comment: First date
Type: Text
Comment: Second date
Type: Text
Comment: interval
Type: 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

DATEVALUE

The DATEVALUE function returns a number representing the day, i.e the number of days elapsed since December 31, 1899.

Return type: Whole number (like 1, 132, 2344)
Syntax

DATEVALUE(date)

Parameters
Comment: Date
Type: Text
Examples

DATEVALUE("2/22/2002") returns 37309

Related Functions
TIMEVALUE 

DAY

The DAY functions returns the day of a date. If no parameter is specified the current day gets returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

DAY(date)

Parameters
Comment: Date
Type: Text
Examples

DAY("2/22/2002") returns 22

Examples

DAY(2323.1285) returns 11

Related Functions
MONTH 
YEAR 

DAYNAME

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.

Return type: Text
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 

DAYOFYEAR

The DAYOFYEAR() function returns the number of the day in the year (1...365).

Return type: Whole number (like 1, 132, 2344)
Syntax

DAYOFYEAR(year;month;date)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Comment: Month
Type: Whole number (like 1, 132, 2344)
Comment: Day
Type: Whole number (like 1, 132, 2344)
Examples

DAYOFYEAR(2000;12;1) returns 336

Examples

DAYOFYEAR(2000;2;29) returns 60

DAYS

The DAYS() function returns the difference between two dates in days.

Return type: Whole number (like 1, 132, 2344)
Syntax

DAYS(date2; date1)

Parameters
Comment: First (earlier) date value
Type: Text
Comment: Second date value
Type: Text
Examples

DAYS("2002-02-22"; "2002-02-26") returns 4

DAYS360

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

DAYS360(date1; date2; method)

Parameters
Comment: Date1
Type: Text
Comment: Date2
Type: Text
Comment: Method
Type: A truth value (TRUE or FALSE)
Examples

DAYS360("2/22/2002"; "4/21/2002"; FALSE) returns 59

Related Functions
DAYS 
MONTHS 
WEEKS 
YEARS 

DAYSINMONTH

The function DAYSINMONTH() returns the number of days in the given year and month.

Return type: Whole number (like 1, 132, 2344)
Syntax

DAYSINMONTH(year;month)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Comment: Month
Type: Whole number (like 1, 132, 2344)
Examples

DAYSINMONTH(2000;2) returns 29

DAYSINYEAR

The function DAYSINYEAR() returns the number of days in the given year.

Return type: Whole number (like 1, 132, 2344)
Syntax

DAYSINYEAR(year)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Examples

DAYSINYEAR(2000) returns 366

EASTERSUNDAY

The EASTERSUNDAY() function returns the date which corresponds to Easter Sunday in the year given as the parameter.

Return type: Date
Syntax

EASTERSUNDAY(year)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Examples

EASTERSUNDAY(2003) returns "20th April 2003"

EDATE

The EDATE functions returns the date that is specified by a given date and a number of months before or after that date.

Return type: Date
Syntax

EDATE(date; months)

Parameters
Comment: Date
Type: Text
Comment: Months
Type: Whole number (like 1, 132, 2344)
Examples

EDATE("2/22/2002"; 3) returns "5/22/2002"

Examples

EDATE("3/31/2002"; -1) returns "2/28/2002"

Related Functions
DATE 
EOMONTH 

EOMONTH

The EOMONTH functions returns the last day in the month specified by a date and the number of months from that date.

Return type: Date
Syntax

EOMONTH(date; months)

Parameters
Comment: Date
Type: Text
Comment: Months
Type: Whole number (like 1, 132, 2344)
Examples

EOMONTH("2/22/2002"; 3) returns "5/31/2002"

Examples

EOMONTH("3/12/2002"; -1) returns "2/28/2002"

Examples

EOMONTH("3/12/2002"; 0) returns "3/31/2002"

Related Functions
EDATE 
MONTH 

HOUR

The HOUR functions returns the hour of a time. If no parameter is specified the current hour gets returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

HOUR(time)

Parameters
Comment: Time
Type: Text
Examples

HOUR("22:10:12") returns 22

Examples

HOUR(0.1285) returns 3

Related Functions
MINUTE 
SECOND 

HOURS

The HOURS() function returns the value of the hours in a time expression.

Return type: Whole number (like 1, 132, 2344)
Syntax

HOURS(time)

Parameters
Comment: Time
Type: Text
Examples

HOURS("10:5:2") returns 10

ISLEAPYEAR

The function ISLEAPYEAR() returns True if the given year is leap.

Return type: A truth value (TRUE or FALSE)
Syntax

ISLEAPYEAR(year)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Examples

ISLEAPYEAR(2000) returns True

ISOWEEKNUM

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

ISOWEEKNUM(date)

Parameters
Comment: Date
Type: Text
Examples

ISOWEEKNUM(A1) returns 51 when A1 is "21st of Dec".

Related Functions
WEEKNUM 

MINUTE

The MINUTE functions returns the minutes of a time. If no parameter is specified the current minute is returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

MINUTE(time)

Parameters
Comment: Time
Type: Text
Examples

MINUTE("22:10:12") returns 10

Examples

MINUTE(0.1234) returns 57

Related Functions
HOUR 
SECOND 

MINUTES

The MINUTES() function returns the value of the minutes in a time expression.

Return type: Whole number (like 1, 132, 2344)
Syntax

MINUTES(time)

Parameters
Comment: Time
Type: Text
Examples

MINUTES("10:5:2") returns 5

MONTH

The MONTH functions returns the month of a date. If no parameter is specified the current month gets returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

MONTH(date)

Parameters
Comment: Date
Type: Text
Examples

MONTH("2/22/2002") returns 2

Examples

MONTH(2323.1285) returns 5

Related Functions
DAY 
YEAR 

MONTHNAME

The MONTHNAME() function returns the name of the month (1...12).

Return type: Text
Syntax

MONTHNAME(number)

Parameters
Comment: Number of month (1..12)
Type: Whole number (like 1, 132, 2344)
Examples

MONTHNAME(5) returns May

MONTHS

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

MONTHS(date2; date1; mode)

Parameters
Comment: First (earlier) date value
Type: Text
Comment: Second date value
Type: Text
Comment: Calculation mode
Type: 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

NETWORKDAY

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

NETWORKDAY(start date; end date; holidays)

Parameters
Comment: Start date
Type: Text
Comment: End date
Type: Text
Comment: Holidays
Type: Text
Examples

NETWORKDAY("01/01/2001";"01/08/2001") returns 5 workdays

Examples

NETWORKDAY("01/01/2001";"01/08/2001";2) returns 3 workdays

NOW

The NOW() function returns the current date and time. It is identical with CURRENTDATETIME and provided for compatibility with other applications.

Return type: Date
Syntax

NOW()

Parameters

Examples

NOW() returns "Saturday 13 April 2002 19:12:01"

Related Functions
CURRENTTIME 
TODAY 

SECOND

The SECOND functions returns the seconds of a time. If no parameter is specified the current second is returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

SECOND(time)

Parameters
Comment: Time
Type: Text
Examples

SECOND("22:10:12") returns 12

Examples

SECOND(0.1234) returns 42

Related Functions
HOUR 
MINUTE 

SECONDS

The SECONDS() function returns the value of the seconds in a time expression.

Return type: Whole number (like 1, 132, 2344)
Syntax

SECONDS(time)

Parameters
Comment: Time
Type: Text
Examples

SECONDS("10:5:2") returns 2

TIME

The TIME() function returns the time formatted with local parameters.

Return type: Text
Syntax

TIME(hours;minutes;seconds)

Parameters
Comment: Hours
Type: Whole number (like 1, 132, 2344)
Comment: Minutes
Type: Whole number (like 1, 132, 2344)
Comment: Seconds
Type: 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

TIMEVALUE

The TIMEVALUE() function returns a number (between 0 and 1) representing the time of day.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TIMEVALUE(time)

Parameters
Comment: Time
Type: Text
Examples

TIMEVALUE("10:05:02") returns 0.42

Related Functions
DATEVALUE 

TODAY

The TODAY() function returns the current date.

Return type: Date
Syntax

TODAY()

Parameters

Examples

TODAY() returns "Saturday 13 April 2002"

Related Functions
CURRENTTIME 
NOW 

UNIX2DATE

UNIX2DATE() function converts unix time to a date and time value.

A unix time is the number of seconds after midnight January 1st, 1970.

Return type: Date
Syntax

UNIX2DATE(unixtime)

Parameters
Comment: Unixtime
Type: Whole number (like 1, 132, 2344)
Examples

UNIX2DATE(0) returns 1970-01-01

WEEKDAY

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,...

Return type: Whole number (like 1, 132, 2344)
Syntax

WEEKDAY(date; method)

Parameters
Comment: Date
Type: Text
Comment: Method (optional)
Type: Whole number (like 1, 132, 2344)
Examples

WEEKDAY("2002-02-22"; 2) returns 5

Related Functions
DAYNAME 

WEEKNUM

The WEEKNUM() function returns the non-ISO week number in which the date falls into.

Return type: Whole number (like 1, 132, 2344)
Syntax

WEEKNUM(date; method)

Parameters
Comment: Date
Type: Text
Comment: 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 

WEEKS

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

WEEKS(date2; date1; mode)

Parameters
Comment: First (earlier) date value
Type: Text
Comment: Second date value
Type: Text
Comment: Calculation mode
Type: 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)

WEEKSINYEAR

The function WEEKSINYEAR() returns the number of weeks in the given year.

Return type: Whole number (like 1, 132, 2344)
Syntax

WEEKSINYEAR(year)

Parameters
Comment: Year
Type: Whole number (like 1, 132, 2344)
Examples

WEEKSINYEAR(2000) returns 52

WORKDAY

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.

Return type: Date
Syntax

WORKDAY(start date; days; holidays)

Parameters
Comment: Start date
Type: Text
Comment: Working days
Type: Whole number (like 1, 132, 2344)
Comment: Holidays
Type: 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"

YEAR

The YEAR functions returns the year of a date. If no parameter is specified the current year gets returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

YEAR(date)

Parameters
Comment: Date
Type: Text
Examples

YEAR("2/22/2002") returns 2002

Examples

YEAR(2323.1285) returns 1906

Related Functions
DAY 
MONTH 

YEARFRAC

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

Return type: Whole number (like 1, 132, 2344)
Syntax

YEARFRAC(start date; end date; basis)

Parameters
Comment: First date
Type: Text
Comment: Second date
Type: Text
Comment: interval
Type: Text

YEARS

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

YEARS(date2; date1; mode)

Parameters
Comment: First (earlier) date value
Type: Text
Comment: Second date value
Type: Text
Comment: Calculation mode
Type: 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

Engineering

BASE

The BASE() function converts a number from base-10 to a string value in a target base from 2 to 36.

Return type: Text
Syntax

BASE(number;base;prec)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Base
Type: Whole number (like 1, 132, 2344)
Comment: MinLength
Type: Whole number (like 1, 132, 2344)
Examples

BASE(128;8) returns "200"

BESSELI

The BESSELI() function returns the modified Bessel function In(x).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BESSELI(X;N)

Parameters
Comment: Where the function is evaluated
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Order of the function
Type: Whole number (like 1, 132, 2344)
Examples

BESSELI(0.7;3) returns 0.007367374

Related Functions
BESSELJ 
BESSELK 
BESSELY 

BESSELJ

The BESSELJ() function returns the Bessel function.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BESSELJ(X;N)

Parameters
Comment: Where the function is evaluated
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Order of the function
Type: Whole number (like 1, 132, 2344)
Examples

BESSELJ(0.89;3) returns 0.013974004

Related Functions
BESSELI 
BESSELK 
BESSELY 

BESSELK

The BESSELK() function returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BESSELK(X;N)

Parameters
Comment: Where the function is evaluated
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Order of the function
Type: Whole number (like 1, 132, 2344)
Examples

BESSELK(3;9) returns 397.95880

Related Functions
BESSELI 
BESSELJ 
BESSELY 

BESSELY

The BESSELY() function returns the Bessel function, which is also called the Weber function or the Neumann function.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BESSELY(X;N)

Parameters
Comment: Where the function is evaluated
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Order of the function
Type: Whole number (like 1, 132, 2344)
Examples

BESSELY(4;2) equals 0.215903595

Related Functions
BESSELI 
BESSELJ 
BESSELK 

BIN2DEC

The BIN2DEC() function returns the value formatted as a decimal number.

Return type: Whole number (like 1, 132, 2344)
Syntax

BIN2DEC(value)

Parameters
Comment: The value to convert
Type: Whole number (like 1, 132, 2344)
Examples

BIN2DEC("1010") returns 10

Examples

BIN2DEC("11111") returns 31

BIN2HEX

The BIN2HEX() function returns the value formatted as a hexadecimal number.

Return type: Text
Syntax

BIN2HEX(value)

Parameters
Comment: The value to convert
Type: Text
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

BIN2HEX("1010") returns "a"

Examples

BIN2HEX("11111") returns "1f"

BIN2OCT

The BIN2OCT() function returns the value formatted as an octal number.

Return type: Text
Syntax

BIN2OCT(value)

Parameters
Comment: The value to convert
Type: Text
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

BIN2OCT("1010") returns "12"

Examples

BIN2OCT("11111") returns "37"

COMPLEX

The COMPLEX(real;imag) returns a complex number of form x+yi.

Return type: Text
Syntax

COMPLEX(real;imag)

Parameters
Comment: Real coefficient
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Imaginary coefficient
Type: 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"

CONVERT

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CONVERT(Number; From Unit; To Unit)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: From unit
Type: Text
Comment: To unit
Type: 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

DEC2BIN

The DEC2BIN() function returns the value formatted as a binary number.

Return type: Text
Syntax

DEC2BIN(value)

Parameters
Comment: The value to convert
Type: Whole number (like 1, 132, 2344)
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

DEC2BIN(12) returns "1100"

Examples

DEC2BIN(55) returns "110111"

DEC2HEX

The DEC2HEX() function returns the value formatted as a hexadecimal number.

Return type: Text
Syntax

DEC2HEX(value)

Parameters
Comment: The value to convert
Type: Whole number (like 1, 132, 2344)
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

DEC2HEX(12) returns "c"

Examples

DEC2HEX(55) returns "37"

DEC2OCT

The DEC2OCT() function returns the value formatted as an octal number.

Return type: Text
Syntax

DEC2OCT(value)

Parameters
Comment: The value to convert
Type: Whole number (like 1, 132, 2344)
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

DEC2OCT(12) returns "14"

Examples

DEC2OCT(55) returns "67"

DELTA

The DELTA() function returns 1 if x equals y, otherwise returns 0. y defaults to 0.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DELTA(x; y)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Floating point value
Type: 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

ERF

The ERF() function returns the error function. With a single argument, ERF() returns the error function between 0 and that argument.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ERF(Lower limit; Upper limit)

Parameters
Comment: Lower limit
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Upper limit
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

ERF(0.4) equals 0.42839236

Related Functions
ERFC 

ERFC

The ERFC() function returns the complementary error function.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ERFC(Lower limit; Upper limit)

Parameters
Comment: Lower limit
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Upper limit
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

ERFC(0.4) equals 0.57160764

Related Functions
ERF 

GESTEP

The GESTEP() function returns 1 if x greater or equals y, otherwise returns 0. y defaults to 0.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GESTEP(x; y)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Floating point value
Type: 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

HEX2BIN

The HEX2BIN() function returns the value formatted as a binary number.

Return type: Text
Syntax

HEX2BIN(value)

Parameters
Comment: The value to convert
Type: Text
Examples

HEX2BIN("a") returns "1010"

Examples

HEX2BIN("37") returns "110111"

HEX2DEC

The HEX2DEC() function returns the value formatted as a decimal number.

Return type: Whole number (like 1, 132, 2344)
Syntax

HEX2DEC(value)

Parameters
Comment: The value to convert
Type: Text
Examples

HEX2DEC("a") returns 10

Examples

HEX2DEC("37") returns 55

HEX2OCT

The HEX2OCT() function returns the value formatted as an octal number.

Return type: Text
Syntax

HEX2OCT(value)

Parameters
Comment: The value to convert
Type: Text
Examples

HEX2OCT("a") returns "12"

Examples

HEX2OCT("37") returns "67"

IMABS

The IMABS(complex number) returns the norm of a complex number of form x+yi.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

IMABS(complex number)

Parameters
Comment: Complex number
Type: Text
Examples

IMABS("1.2+5i") returns 5.1419

Examples

IMABS("-i") returns 1

Examples

IMABS("12") returns 12

IMAGINARY

The IMAGINARY(string) returns the imaginary coefficient of a complex.

Return type: Double
Syntax

IMAGINARY(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMAGINARY("1.2+3.4i") returns 3.4

Examples

IMAGINARY("1.2") returns 0

IMARGUMENT

The IMARGUMENT(complex number) returns the argument of a complex number of form x+yi.

Return type: Text
Syntax

IMARGUMENT(complex number)

Parameters
Comment: Complex number
Type: Text
Examples

IMARGUMENT("1.2+5i") returns 0.6072

Examples

IMARGUMENT("-i") returns -1.57079633

Examples

IMARGUMENT("12") returns "#Div/0"

IMCONJUGATE

The IMCONJUGATE(complex number) returns the conjugate of a complex number of form x+yi.

Return type: Text
Syntax

IMCONJUGATE(complex number)

Parameters
Comment: Complex number
Type: Text
Examples

IMCONJUGATE("1.2+5i") returns "1.2-5i"

Examples

IMCONJUGATE("-i") returns "i"

Examples

IMCONJUGATE("12") returns "12"

IMCOS

The IMCOS(string) returns the cosine of a complex number.

Return type: Text
Syntax

IMCOS(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMCOS("1+i") returns "0.83373-0.988898i"

Examples

IMCOS("12i") returns 81 377.4

IMCOSH

The IMCOSH(string) returns the hyperbolic cosine of a complex number.

Return type: Text
Syntax

IMCOSH(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMCOSH("1+i") returns "0.83373+0.988898i"

Examples

IMCOSH("12i") returns 0.84358

IMCOT

The IMCOT(string) returns the cotangent of a complex number.

Return type: Text
Syntax

IMCOT(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMCOT("1+i") returns "0.21762-0.86801i"

IMCSC

The IMCSC(string) returns the cosecant of a complex number.

Return type: Text
Syntax

IMCSC(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMCSC("1+i") returns "0.62151-0.30393i"

IMCSCH

The IMCSCH(string) returns the hyperbolic cosecant of a complex number.

Return type: Text
Syntax

IMCSCH(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMCSCH("1+i") returns "0.30393-i0.62151"

IMDIV

The IMDIV() returns the division of several complex numbers of form x+yi.

Return type: Text
Syntax

IMDIV(value;value;...)

Parameters
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: 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"

IMEXP

The IMEXP(string) returns the exponential of a complex number.

Return type: Text
Syntax

IMEXP(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMEXP("2-i") returns "3.99232-6.21768i"

Examples

IMEXP("12i") returns "0.843854-0.536573i"

IMLN

The IMLN(string) returns the natural logarithm of a complex number.

Return type: Text
Syntax

IMLN(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMLN("3-i") returns "1.15129-0.321751i"

Examples

IMLN("12") returns 2.48491

IMLOG10

The IMLOG10(string) returns the base-10 logarithm of a complex number.

Return type: Text
Syntax

IMLOG10(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMLOG10("3+4i") returns "0.69897+0.402719i"

IMLOG2

The IMLOG2(string) returns the base-2 logarithm of a complex number.

Return type: Text
Syntax

IMLOG2(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMLOG2("3+4i") returns "2.321928+1.337804i"

IMPOWER

The IMPOWER(string) returns a complex number raised to a power.

Return type: Text
Syntax

IMPOWER(string)

Parameters
Comment: Complex number
Type: Text
Comment: Power
Type: Whole number (like 1, 132, 2344)
Examples

IMPOWER("4-i";2) returns "15-8i"

Examples

IMPOWER("1.2";2) returns 1.44

IMPRODUCT

The IMPRODUCT() returns the product of several complex numbers of form x+yi.

Return type: Text
Syntax

IMPRODUCT(value;value;...)

Parameters
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Examples

IMPRODUCT(1.2;"3.4+5i") returns "4.08+6i"

Examples

IMPRODUCT(1.2;"1i") returns "+1.2i"

IMREAL

The IMREAL(string) returns the real coefficient of a complex.

Return type: Double
Syntax

IMREAL(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMREAL("1.2+3.4i") returns 1.2

Examples

IMREAL("1.2i") returns 0

IMSEC

The IMSEC(string) returns the secant of a complex number.

Return type: Text
Syntax

IMSEC(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMSEC("1+i") returns "0.49833+i0.59108"

IMSECH

The IMSECH(string) returns the hyperbolic secant of a complex number.

Return type: Text
Syntax

IMSECH(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMSECH("1+i") returns "0.49833-i0.59108"

IMSIN

The IMSIN(string) function returns the sine of a complex number.

Return type: Text
Syntax

IMSIN(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMSIN("1+i") returns "1.29846+0.634964i"

Examples

IMSIN("1.2") returns -0.536573

IMSINH

The IMSINH(string) function returns the hyperbolic sine of a complex number.

Return type: Text
Syntax

IMSINH(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMSINH("1+i") returns "0.63496+1.29846i"

Examples

IMSINH("1.2") returns 1.50946

IMSQRT

The IMSQRT(string) returns the square root of a complex number.

Return type: Text
Syntax

IMSQRT(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMSQRT("1+i") returns "1.09868+0.45509i"

Examples

IMSQRT("1.2i") returns "0.774597+0.774597i"

IMSUB

The IMSUB() returns the difference of several complex numbers of form x+yi.

Return type: Text
Syntax

IMSUB(value;value;...)

Parameters
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Examples

IMSUB(1.2;"3.4+5i") returns "-2.2-5i"

Examples

IMSUB(1.2;"1i") returns "1.2-i"

IMSUM

The IMSUM() returns the sum of several complex numbers of form x+yi.

Return type: Text
Syntax

IMSUM(value;value;...)

Parameters
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Comment: Complex number
Type: A range of strings
Examples

IMSUM(1.2;"3.4+5i") returns "4.6+5i"

Examples

IMSUM(1.2;"1i") returns "1.2+i"

IMTAN

The IMTAN(string) function returns the tangent of a complex number.

Return type: Text
Syntax

IMTAN(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMTAN("1+i") returns "0.27175+1.08392i"

Examples

IMTAN("1.2") returns 2.57215

IMTANH

The IMTANH(string) function returns the hyperbolic tangent of a complex number.

Return type: Text
Syntax

IMTANH(string)

Parameters
Comment: Complex number
Type: Text
Examples

IMTANH("1+i") returns "1.08392+0.27175i"

Examples

IMTANH("1.2") returns 0.83365

OCT2BIN

The OCT2BIN() function returns the value formatted as a binary number.

Return type: Text
Syntax

OCT2BIN(value)

Parameters
Comment: The value to convert
Type: Text
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

OCT2BIN("12") returns "1010"

Examples

OCT2BIN("55") returns "101101"

OCT2DEC

The OCT2DEC() function returns the value formatted as a decimal number.

Return type: Whole number (like 1, 132, 2344)
Syntax

OCT2DEC(value)

Parameters
Comment: The value to convert
Type: Text
Examples

OCT2DEC("12") returns 10

Examples

OCT2DEC("55") returns 45

OCT2HEX

The OCT2HEX() function returns the value formatted as a hexadecimal number.

Return type: Text
Syntax

OCT2HEX(value)

Parameters
Comment: The value to convert
Type: Text
Comment: The minimum length of the output
Type: Whole number (like 1, 132, 2344)
Examples

OCT2HEX("12") returns "A"

Examples

OCT2HEX("55") returns "2D"

Financial

ACCRINT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ACCRINT(issue; first interest; settlement; rate; par; frequency; basis)

Parameters
Comment: Issue date
Type: Date
Comment: First interest
Type: Date
Comment: Settlement
Type: Date
Comment: Annual rate of security
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Par value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Number of payments per year
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Day counting basis
Type: 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 

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ACCRINTM(issue; settlement; rate; par; basis)

Parameters
Comment: Issue date
Type: Date
Comment: Settlement
Type: Date
Comment: Annual rate of security
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Par value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Day counting basis
Type: Whole number (like 1, 132, 2344)
Examples

ACCRINTM("2/28/2001"; "8/31/2001"; 0.1; 100) returns 5.0278

Related Functions
ACCRINT 

AMORDEGRC

The AMORDEGRC function calculates the amortization value for the French accounting system using degressive depreciation.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

AMORDEGRC( Cost; purchaseDate; firstPeriodEndDate; salvage; period; rate; basis)

Parameters
Comment: Cost
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Pv
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Fv
Type: 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 

AMORLINC

The AMORLINC function calculates the amortization value for the French accounting system using linear depreciation.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

AMORLINC( Cost; purchaseDate; firstPeriodEndDate; salvage; period; rate; basis)

Parameters
Comment: P
Type: Whole number (like 1, 132, 2344)
Comment: Pv
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Fv
Type: 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 

COMPOUND

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COMPOUND(initial;interest;periods;periods_per_year)

Parameters
Comment: Principal
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods per year
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Years
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

COMPOUND(5000;0.12;4;5) equals 9030.56

CONTINUOUS

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CONTINOUS(principal;interest;years)

Parameters
Comment: Principal
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Years
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

CONTINUOUS(1000;0.1;1) equals 1105.17

COUPNUM

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COUPNUM(settlement; maturity; frequency; basis)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Frequency
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Day counting basis
Type: Whole number (like 1, 132, 2344)
Examples

COUPNUM("2/28/2001"; "8/31/2001"; 2; 0) returns 1

CUMIPMT

Calculates the cumulative interest payment.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CUMIPMT(rate, periods, value, start, end, type)

Parameters
Comment: rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: periods
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: start
Type: Whole number (like 1, 132, 2344)
Comment: end
Type: Whole number (like 1, 132, 2344)
Comment: type
Type: Whole number (like 1, 132, 2344)
Examples

CUMIPMT( 0.06/12; 5*12; 100000; 5; 12; 0 ) equals -3562,187023

Related Functions
IPMT 
CUMPRINC 

CUMPRINC

Calculates the cumulative principal payment.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CUMPRINC(rate, periods, value, start, end, type)

Parameters
Comment: rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: periods
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: start
Type: Whole number (like 1, 132, 2344)
Comment: end
Type: Whole number (like 1, 132, 2344)
Comment: type
Type: Whole number (like 1, 132, 2344)
Examples

CUMPRINC( 0.06/12; 5*12; 100000; 5; 12; 0 ) equals -11904.054201

Related Functions
PPMT 
CUMIPMT 

DB

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DB(cost; salvage value; life; period [;month])

Parameters
Comment: Cost
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Salvage
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Life
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Month
Type: 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 

DDB

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DDB(cost; salvage value; life; period [;factor])

Parameters
Comment: Cost
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Salvage
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Life
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Factor
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

DDB(75000;1;60;12;2) returns 1721.81

Related Functions
SLN 

DISC

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DISC(settlement; maturity; par; redemption [; basis ] )

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Price per $100 face value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Redemption
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Day counting basis
Type: Whole number (like 1, 132, 2344)
Examples

DISC("2/28/2001"; "8/31/2001"; 12; 14) returns 0.2841

Related Functions
YEARFRAC 

DOLLARDE

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

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DOLLARDE(fractional dollar; fraction)

Parameters
Comment: Fractional Dollar
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Fraction
Type: Whole number (like 1, 132, 2344)
Examples

DOLLARDE(1.02; 16) - stands for 1 and 2/16 - returns 1.125

Related Functions
DOLLARFR 
TRUNC 

DOLLARFR

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

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DOLLARFR(fractional dollar; fraction)

Parameters
Comment: Decimal Dollar
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Fraction
Type: Whole number (like 1, 132, 2344)
Examples

DOLLARFR(1.125; 16) returns 1.02. (1 + 2/16)

Related Functions
DOLLARDE 
TRUNC 

DURATION

Returns the number of periods needed for an investment to retain a desired value.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DURATION(rate; pv; fv)

Parameters
Comment: Rate
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)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

DURATION(0.1; 1000; 2000) returns 7.27

Related Functions
FV 
PV 

DURATION_ADD

Returns the Macauley duration of a fixed interest security in years.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DURATION_ADD(Settlement; Maturity; Coupon; Yield; Frequency; Basis)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Coupon
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Yield
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Frequency
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: 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 

EFFECT

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%.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EFFECT(nominal;periods)

Parameters
Comment: Nominal interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

EFFECT(0.08;12) equals 0.083

Related Functions
EFFECTIVE 
NOMINAL 

EFFECTIVE

The EFFECTIVE() function calculates the effective yield for a nominal interest rate (annual rate or APR). It is the same as the EFFECT function.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EFFECTIVE(nominal;periods)

Parameters
Comment: Nominal interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: A floating point value (like 1.3, 0.343, 253 )
Related Functions
EFFECT 

EURO

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EURO(currency)

Parameters
Comment: Currency
Type: Text
Examples

EURO("DEM") equals 1.95583

Related Functions
EUROCONVERT 

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EUROCONVERT(number; source currency; target currency)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Source currency
Type: Text
Comment: Target currency
Type: Text
Examples

EUROCONVERT(1; "EUR"; "DEM") equals 1.95583

Related Functions
EURO 

FV

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FV(present value;yield;periods)

Parameters
Comment: Present value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: 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 

FV_ANNUITY

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FV_ANNUITY(amount;interest;periods)

Parameters
Comment: Payment per period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

FV_ANNUITY(1000;0.05;5) equals 5525.63

INTRATE

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

INTRATE(settlement; maturity; investment; redemption; basis)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Investment
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Redemption
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Day counting basis
Type: Whole number (like 1, 132, 2344)
Examples

INTRATE("2/28/2001"; "8/31/2001"; 1000000; 2000000; 1) returns 1.98

IPMT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

IPMT(Rate; Period; NPer; PV; FV; Type)

Parameters
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Number of periods
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Present values
Type: 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 

IRR

The IRR function calculates the internal rate of return for a series of cash flows.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

IRR( Values[; Guess = 0.1 ] )

Parameters
Comment: Values
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Guess
Type: A floating point value (like 1.3, 0.343, 253 )
Related Functions
XIRR 

ISPMT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ISPMT(Rate; Period; NPer; PV)

Parameters
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Period
Type: Whole number (like 1, 132, 2344)
Comment: Number of periods
Type: 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 

LEVEL_COUPON

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LEVEL_COUPON(face value;coupon rate;coupons per year;years;market rate)

Parameters
Comment: Face value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Coupon rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Coupons per year
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Years
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Market interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LEVEL_COUPON(1000;.13;2;4;.1) equals 1096.95

MDURATION

The MDURATION() function will calculate the modified Macauley duration of a fixed interest security in years.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MDURATION( Settlement; Maturity; Coupon; Yield; Frequency; [ Basis=0 ])

Parameters
Comment: Settlement
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Maturity
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Coupon
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Yield
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Frequency
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: 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 

MIRR

The MIRR() function will calculate the modified internal rate of return (IRR) of a series of periodic investments.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MIRR(values; investment; reinvestment)

Parameters
Comment: Values
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Investment
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Reinvestment
Type: 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 

NOMINAL

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%.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NOMINAL(effective;periods)

Parameters
Comment: Effective interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

NOMINAL(0.08;12) equals 0.0772

Related Functions
EFFECT 

NPER

Returns the number of periods of an investment.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NPER(rate;payment;pv;fv;type)

Parameters
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Payment
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

NPER(0.1; -100; 1000) equals 11

Examples

NPER(0.06; 0; -10000; 20000 ;0) returns 11.906

Related Functions
FV 
RATE 
PMT 
PV 

NPV

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NPV(Rate; Values)

Parameters
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Values (array)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

NPV(100%;4;5;7) = 4.125

Related Functions
FV 
IRR 
NPER 
PMT 
PV 

ODDLPRICE

The ODDLPRICE function calculates the value of the security per 100 currency units of face value. The security has an irregular last interest date.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ODDLPRICE( Settlement; Maturity; Last; Rate; AnnualYield; Redemption; Frequency [; Basis = 0 ] )

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Last
Type: Date
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: AnnualYield
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Redemption
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Frequency
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: 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

ODDLYIELD

The ODDLYIELD function calculates the yield of the security which has an irregular last interest date.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ODDLYIELD( Settlement; Maturity; Last; Rate; Price; Redemption; Frequency [; Basis = 0 ] )

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Last
Type: Date
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Price
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Redemption
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Frequency
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: 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

PMT returns the amount of payment for a loan based on a constant interest rate and constant payments (each payment is equal amount).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PMT(rate; nper ; pv [; fv = 0 [; type = 0 ]] )

Parameters
Comment: Rate
Type: 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

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PPMT(Rate; Period; NPer; PV [; FV = 9 [; Type = 0 ]] )

Parameters
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Number of periods
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Present value
Type: 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

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

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PRICEMAT(settlement; maturity; issue; rate; yield [; basis = 0 ] )

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Issue
Type: Date
Comment: Discount rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Yield
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: 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

PV

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PV(future value;rate;periods)

Parameters
Comment: Future value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

PV(1166.4;0.08;2) equals 1000

PV_ANNUITY

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PV_ANNUITY(amount;interest;periods)

Parameters
Comment: Payment per period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Periods
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

PV_ANNUITY(1000;0.05;5) equals 4329.48

RATE

The RATE() function computes the constant interest rate per period of an investment.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RATE(nper;pmt;pv;fv;type;guess)

Parameters
Comment: Payment period
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Regular payments
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Present value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Future value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Type
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Guess
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

RATE(4*12;-200;8000) equals 0.007701472

RECEIVED

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RECEIVED(settlement; maturity; investment; discount; basis)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Investment
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Discount rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: Whole number (like 1, 132, 2344)
Examples

RECEIVED("2/28/2001"; "8/31/2001"; 1000; 0.05; 0) returns 1,025.787

RRI

The RRI function calculates the interest rate resulting from the profit (return) of an investment.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RRI( P; Pv; Fv)

Parameters
Comment: P
Type: Whole number (like 1, 132, 2344)
Comment: Pv
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Fv
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

RRI(1;100;200) returns 1

Related Functions
FV 
NPER 
PMT 
PV 
RATE 

SLN

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SLN(cost; salvage value; life)

Parameters
Comment: Cost
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Salvage
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Life
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SLN(10000;700;10) equals 930

Related Functions
SYD 
DDB 

SYD

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SYD(cost; salvage value; life; period)

Parameters
Comment: Cost
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Salvage
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Life
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Period
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SYD(5000; 200; 5; 2) equals 1280

Related Functions
SLN 
DDB 

TBILLEQ

The TBILLEQ functions returns the bond equivalent for a treasury bill. The maturity date must be after the settlement date but within 365 days.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TBILLEQ(settlement; maturity; discount)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Discount rate
Type: 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 

TBILLPRICE

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TBILLPRICE(settlement; maturity; discount)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Discount rate
Type: 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 

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TBILLYIELD(settlement; maturity; price)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Price per $100 face value
Type: 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

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

VDB(cost; salvage; life; start-period; end-period; [; depreciation-factor = 2 [; switch = false ]] )

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Price
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Redemption
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: Whole number (like 1, 132, 2344)
Examples

VDB(10000;600;10;0;0.875;1.5) returns 1312.5

XIRR

The XIRR function calculates the internal rate of return for a non-periodic series of cash flows.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

XIRR( Values; Dates[; Guess = 0.1 ] )

Parameters
Comment: Values
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Dates
Type: Date
Comment: Guess
Type: 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 

XNPV

The XNPV function calculates the net present value of a series of cash flows.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

XNPV( Rate; Values; Dates )

Parameters
Comment: Rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Values
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Dates
Type: 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

YIELDDISC calculates the yield of a discounted security per 100 currency units of face value.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

YIELDDISC(settlement; maturity; price, redemp, basis)

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Price
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Redemption
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: Whole number (like 1, 132, 2344)
Examples

YIELDDISC(DATE(1990;6;1);DATE(1990;12;31);941.66667;1000) returns 0.106194684

YIELDMAT

The YIELDMAT function calculates the yield of the security that pays interest on the maturity date.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

YIELDMAT( Settlement; Maturity; Issue; Rate; Price; Basis )

Parameters
Comment: Settlement
Type: Date
Comment: Maturity
Type: Date
Comment: Issue
Type: Date
Comment: Discount rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Price
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Basis
Type: 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 

ZERO_COUPON

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ZERO_COUPON(face value;rate;years)

Parameters
Comment: Face value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Interest rate
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Years
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

ZERO_COUPON(1000;.1;20) equals 148.64

Information

ERRORTYPE

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

ERRORTYPE(value)

Parameters
Comment: Error
Type: Any kind of value
Examples

ERRORTYPE(NA()) returns 7

Examples

ERRORTYPE(0) returns an error

FILENAME

Returns the current filename. If the current document is not saved, an empty string is returned.

Return type: Text
Syntax

FILENAME()

Parameters

FORMULA

The FORMULA() function returns the formula of a cell as string.

Return type: Text
Syntax

FORMULA(x)

Parameters
Comment: Reference
Type: Reference
Examples

FORMULA(A1) returns "=SUM(1+2)" if the cell A1 contains such a formula.

INFO

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.

Return type: Text
Syntax

INFO(type)

Parameters
Comment: Type of information
Type: Text

ISBLANK

The ISBLANK() function returns True if the parameter is empty. Otherwise it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISBLANK(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISBLANK(A1) returns True if A1 is empty

Examples

ISBLANK(A1) returns False if A1 holds a value

ISDATE

The ISDATE() function returns True if the parameter is a date value. Otherwise it returns False

Return type: A truth value (TRUE or FALSE)
Syntax

ISDATE(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISDATE("2000-2-2") returns True

Examples

ISDATE("hello") returns False

ISERR

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.

Return type: A truth value (TRUE or FALSE)
Syntax

ISERR(x)

Parameters
Comment: Any value
Type: Any kind of value
Related Functions
ISERROR 
ISNA 

ISERROR

The ISERROR() function returns True if its parameter is an error of any type. Otherwise, it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISERROR(x)

Parameters
Comment: Any value
Type: Any kind of value
Related Functions
ISERR 
ISNA 

ISEVEN

The ISEVEN() function returns True if the number is even. Otherwise returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISEVEN(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISEVEN(12) returns True

Examples

ISEVEN(-7) returns False

ISFORMULA

The ISFORMULA() function returns True if the referenced cell contains a formula. Otherwise it returns False

Return type: A truth value (TRUE or FALSE)
Syntax

ISFORMULA(x)

Parameters
Comment: Reference
Type: Reference

ISLOGICAL

The ISLOGICAL() function returns True if the parameter is a boolean value. Otherwise it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISLOGICAL(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISLOGICAL(A1>A2) returns True

Examples

ISLOGICAL(12) returns False

ISNA

The ISNA() function returns True if its parameter is a N/A error. In all other cases, it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISNA(x)

Parameters
Comment: Any value
Type: Any kind of value
Related Functions
ISERR 
ISERROR 

ISNONTEXT

The ISNONTEXT() function returns True if the parameter is not a string. Otherwise it returns False. It's the same as ISNOTTEXT.

Return type: A truth value (TRUE or FALSE)
Syntax

ISNONTEXT(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISNONTEXT(12) returns True

Examples

ISNONTEXT("hello") returns False

Related Functions
ISNOTTEXT 

ISNOTTEXT

The ISNOTTEXT() function returns True if the parameter is not a string. Otherwise it returns False. It's the same as ISNONTEXT.

Return type: A truth value (TRUE or FALSE)
Syntax

ISNOTTEXT(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISNOTTEXT(12) returns True

Examples

ISNOTTEXT("hello") returns False

Related Functions
ISNONTEXT 

ISNUM

The ISNUM() function returns True if the parameter is a numerical value. Otherwise it returns False. It's the same as ISNUMBER.

Return type: A truth value (TRUE or FALSE)
Syntax

ISNUM(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISNUM(12) returns True

Examples

ISNUM(hello) returns False

Related Functions
ISNUMBER 

ISNUMBER

The ISNUMBER() function returns True if the parameter is a numerical value. Otherwise it returns False. It's the same as ISNUM.

Return type: A truth value (TRUE or FALSE)
Syntax

ISNUMBER(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISNUMBER(12) returns True

Examples

ISNUMBER(hello) returns False

Related Functions
ISNUM 

ISODD

The ISODD() function returns True if the number is odd. Otherwise returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISODD(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISODD(12) returns False

Examples

ISODD(-7) returns True

ISREF

The ISREF() function returns True if the parameter refers to a reference. Otherwise it returns False

Return type: A truth value (TRUE or FALSE)
Syntax

ISREF(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISREF(A12) returns true

Examples

ISREF("hello") returns false

ISTEXT

The ISTEXT() function returns True if the parameter is a string. Otherwise it returns False

Return type: A truth value (TRUE or FALSE)
Syntax

ISTEXT(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISTEXT(12) returns False

Examples

ISTEXT("hello") returns True

ISTIME

The ISTIME() function returns True if the parameter is a time value. Otherwise it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

ISTIME(x)

Parameters
Comment: Any value
Type: Any kind of value
Examples

ISTIME("12:05") returns True

Examples

ISTIME("hello") returns False

N

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

N(value)

Parameters
Comment: Value
Type: Any kind of value
Examples

N(3.14) returns 3.14

Examples

N("7") returns 0 (because "7" is text)

NA

The NA() function returns the constant error value, N/A.

Return type: Error
Syntax

NA()

Parameters

Related Functions
ISNA 
ISERR 
ISERROR 

TYPE

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

TYPE(x)

Parameters
Comment: Any value
Type: 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()"

Logical

AND

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).

Return type: A truth value (TRUE or FALSE)
Syntax

AND(value;value;...)

Parameters
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Examples

AND(true;true;true) returns True

Examples

AND(true;false) returns False

FALSE

The FALSE() function returns the boolean value FALSE.

Return type: A truth value (TRUE or FALSE)
Syntax

FALSE()

Parameters

Examples

FALSE() returns FALSE

IF

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).

Return type: Any kind of value
Syntax

IF(condition;if_true;if_false)

Parameters
Comment: Condition
Type: A truth value (TRUE or FALSE)
Comment: If true
Type: Any kind of value
Comment: If false
Type: Any kind of value
Examples

A1=4;A2=6;IF(A1>A2;5;3) returns 3

IFERROR

Return X unless it is an Error, in which case return an alternative value.

Return type: Any kind of value
Syntax

IFERROR(AnyX;AnyAlternative)

Parameters
Comment: Any X
Type: Any kind of value
Comment: Any Alternative
Type: 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.

IFNA

Return X unless it is an NA, in which case return an alternative value.

Return type: Any kind of value
Syntax

IFNA(AnyX;AnyAlternative)

Parameters
Comment: Any X
Type: Any kind of value
Comment: Any Alternative
Type: 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.

NAND

The NAND() function returns True if at least one value is not true. Otherwise it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

NAND(value;value;...)

Parameters
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Examples

NAND(true;false;false) returns True

Examples

NAND(true;true) returns False

NOR

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.

Return type: A truth value (TRUE or FALSE)
Syntax

NOR(value;value;...)

Parameters
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Examples

NOR(true;false;false) returns False

Examples

NOR(false;false) returns True

NOT

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.

Return type: A truth value (TRUE or FALSE)
Syntax

NOT(bool)

Parameters
Comment: Boolean value
Type: A truth value (TRUE or FALSE)
Examples

NOT(false) returns True

Examples

NOT(true) returns False

OR

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).

Return type: A truth value (TRUE or FALSE)
Syntax

OR(value;value;...)

Parameters
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Examples

OR(false;false;false) returns False

Examples

OR(true;false) returns True

TRUE

The TRUE() function returns the boolean value TRUE.

Return type: A truth value (TRUE or FALSE)
Syntax

TRUE()

Parameters

Examples

TRUE() returns TRUE

XOR

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.

Return type: A truth value (TRUE or FALSE)
Syntax

XOR(value;value;...)

Parameters
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Comment: Boolean values
Type: A range of truth values (TRUE or FALSE)
Examples

XOR(false;false;false) returns True

Examples

XOR(true;false) returns True

Lookup & Reference

ADDRESS

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.

Return type: Text
Syntax

ADDRESS(row; col; absolute; style; sheet name)

Parameters
Comment: Row number
Type: Whole number (like 1, 132, 2344)
Comment: Column number
Type: 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 name
Type: 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

AREAS

Returns the number of areas in the reference string. An area can be asingle cell or a set of cells.

Return type: Whole number (like 1, 132, 2344)
Syntax

AREAS(reference)

Parameters
Comment: Reference
Type: A range of strings
Examples

AREAS(A1) returns 1

Examples

AREAS((A1; A2:A4)) returns 2

CELL

Returns information about position, formatting or contents in a reference.

Return type: Any kind of value
Syntax

CELL(type; reference)

Parameters
Comment: Type
Type: Text
Comment: Reference
Type: Reference
Examples

CELL("COL", C7) returns 3

Examples

CELL("ROW", C7) returns 7

Examples

CELL("ADDRESS", C7) returns $C$7

CHOOSE

Returns the parameter specified by the index.

Return type: Any kind of value
Syntax

CHOOSE(index; parameter1; parameter2;...)

Parameters
Comment: Index
Type: Whole number (like 1, 132, 2344)
Comment: Arguments
Type:
Examples

CHOOSE(1; "1st"; "2nd") returns "1st"

Examples

CHOOSE(2; 3; 2; 4) returns 2

COLUMN

The COLUMN function returns the column of given cell reference. If no parameter is specified the column of the current cell gets returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

COLUMN(reference)

Parameters
Comment: Reference
Type: Text
Examples

COLUMN(A1) returns 1

Examples

COLUMN(D2) returns 4

Related Functions
COLUMNS 
ROW 

COLUMNS

The COLUMNS function returns the number of columns in a reference.

Return type: Whole number (like 1, 132, 2344)
Syntax

COLUMNS(reference)

Parameters
Comment: Reference
Type: Text
Examples

COLUMNS(A1:C3) returns 3

Examples

COLUMNS(D2) returns 1

Related Functions
COLUMN 
ROWS 

HLOOKUP

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.

Return type: String/Numeric
Syntax

HLOOKUP(Lookup value; data source; Row; Sorted)

Parameters
Comment: Lookup value
Type: String/Numeric
Comment: Data source
Type: Array
Comment: Row
Type: Whole number (like 1, 132, 2344)
Comment: Sorted (optional)
Type: A truth value (TRUE or FALSE)

INDEX

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

INDEX(cell, row, column)

Parameters
Comment: Reference
Type: Text
Comment: Row
Type: Whole number (like 1, 132, 2344)
Comment: Column
Type: 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.

INDIRECT

Returns the content of the cell specified by the reference text. The second parameter is optional.

Return type: Whole number (like 1, 132, 2344)
Syntax

INDIRECT(referenceText, a1 style)

Parameters
Comment: Reference
Type: Text
Comment: 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

LOOKUP

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

LOOKUP(value; lookup vector; result vector)

Parameters
Comment: Lookup value
Type: String/Numeric
Comment: Lookup vector
Type: String/Numeric
Comment: Result vector
Type: String/Numeric
Examples

LOOKUP(1.232; A1:A6; B1:B6) for A1 = 1, A2 = 2 returns the value of B1.

MATCH

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

MATCH(Search value; Search region; Match type)

Parameters
Comment: Search value
Type: String/Numeric
Comment: Search region
Type: Reference/Array
Comment: Match type (optional)
Type: Whole number (like 1, 132, 2344)

MULTIPLE.OPERATIONS

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.

Return type: String/Numeric
Syntax

MULTIPLE.OPERATIONS(Formula cell; Row cell; Row replacement; Column cell; Column replacement)

Parameters
Comment: Formula cell
Type: Reference
Comment: Row cell
Type: Reference
Comment: Row replacement
Type: Reference
Comment: Column cell (optional)
Type: Reference
Comment: Column replacement (optional)
Type: Reference

OFFSET

Modifies a reference's position and dimension.

Return type: Reference
Syntax

OFFSET(Reference reference; Integer rowOffset; Integer columnOffset; Integer newHeight; Integer newWidth)

Parameters
Comment: Reference or range
Type: Reference
Comment: Number of rows to offset
Type: Whole number (like 1, 132, 2344)
Comment: Number of columns to offset
Type: 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)

ROW

The ROW function returns the row of given cell reference. If no parameter is specified the row of the current cell gets returned.

Return type: Whole number (like 1, 132, 2344)
Syntax

ROW(reference)

Parameters
Comment: Reference
Type: Text
Examples

ROW(A1) returns 1

Examples

ROW(D2) returns 2

Related Functions
ROWS 
COLUMN 

ROWS

The ROWS function returns the number of rows in a reference.

Return type: Whole number (like 1, 132, 2344)
Syntax

ROWS(reference)

Parameters
Comment: Reference
Type: Text
Examples

ROWS(A1:C3) returns 3

Examples

ROWS(D2) returns 1

Related Functions
ROW 
COLUMNS 

SHEET

Returns the sheet number of the reference or the string representing a sheet name.

Return type: Whole number (like 1, 132, 2344)
Syntax

SHEET(reference)

Parameters
Comment: Reference
Type: Reference
Examples

SHEET(Sheet1!C7) returns 1

Examples

SHEET(Sheet2!C7) returns 2

SHEETS

Returns the number of sheets in a reference or current document.

Return type: Whole number (like 1, 132, 2344)
Syntax

SHEETS(reference)

Parameters
Comment: Reference
Type: Reference

VLOOKUP

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.

Return type: String/Numeric
Syntax

VLOOKUP(Lookup value; data source; Column; Sorted)

Parameters
Comment: Lookup value
Type: String/Numeric
Comment: Data source
Type: Array
Comment: Column
Type: Whole number (like 1, 132, 2344)
Comment: Sorted (optional)
Type: A truth value (TRUE or FALSE)

Math

ABS

The ABS() function returns the absolute value of the floating-point number x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ABS(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

ABS(12.5) equals 12.5

Examples

ABS(-12.5) equals 12.5

CEIL

The CEIL() function rounds x up to the nearest integer which is greater than the input, returning that value as a double.

Return type: An integer (like 0, -5, 14 )
Syntax

CEIL(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

CEIL(12.5) equals 13

Examples

CEIL(-12.5) equals -12

Related Functions
CEILING 
FLOOR 
ROUND 
ROUNDUP 

CEILING

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.

Return type: An integer (like 0, -5, 14 )
Syntax

CEILING(x)

Parameters
Comment: A floating point value
Type: 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 

COUNT

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COUNT(value;value;value...)

Parameters
Comment: Values
Type: FLOAT
Examples

COUNT(-5;"KSpread";2) returns 2

Examples

COUNT(5) returns 1

Related Functions
COUNTA 
COUNTIF 
SUM 

COUNTA

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COUNTA(value;value;value...)

Parameters
Comment: Values
Type: FLOAT
Examples

COUNTA(-5;"KSpread";2) returns 3

Examples

COUNTA(5) returns 1

Related Functions
COUNT 
COUNTIF 

COUNTBLANK

This function returns the count of all empty cells within the range.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COUNTBLANK(range)

Parameters
Comment: Cell range
Type: Range
Examples

COUNTBLANK(A1:B5)

Related Functions
COUNT 
COUNTA 
COUNTIF 

COUNTIF

The COUNTIF() function returns the number of cells in the given range that meet the given criteria.

Return type: Whole number (like 1, 132, 2344)
Syntax

COUNTIF(range;criteria)

Parameters
Comment: Range
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Criteria
Type: Text
Examples

COUNTIF(A2:A3;"14") returns 1 if A2 is -4 and A3 is 14

Related Functions
COUNT 
SUMIF 

CUR

The CUR() function returns the non-negative cube root of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CUR(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

CUR(27) equals 3

Related Functions
SQRT 

DIV

The DIV() function divides the first value by the other values in turn.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DIV(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

DIV(20;2;2) returns 5

Examples

DIV(25;2.5) returns 10

Related Functions
MULTIPLY 
MOD 

EPS

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
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

EVEN

The EVEN() function returns the number rounded up to the nearest even integer.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EVEN(value)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

EVEN(1.2) returns 2

Examples

EVEN(2) returns 2

Related Functions
ODD 

EXP

The EXP() function returns the value of e (the base of natural logarithms) raised to the power of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EXP(x)

Parameters
Comment: A floating point value
Type: 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 

FACT

The FACT() function calculates the factorial of the parameter. The mathematical expression is (value)!.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FACT(number)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

FACT(10) returns 3628800

Examples

FACT(0) returns 1

FACTDOUBLE

The FACTDOUBLE() function calculates the double factorial of a number, i.e. x!!.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FACTDOUBLE(number)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

FACTDOUBLE(6) returns 48

Examples

FACTDOUBLE(7) returns 105

FIB

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FIB(n)

Parameters
Comment: Nth term
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

FIB(9) returns 34

Examples

FIB(26) returns 121393

FLOOR

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.

Return type: An integer (like 0, -5, 14 )
Syntax

FLOOR(x)

Parameters
Comment: A floating point value
Type: 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 

GAMMA

The GAMMA() function returns the gamma function value.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GAMMA(value)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

GAMMA(1) returns 1

Related Functions
FACT 

GCD

The GCD() function returns the greatest common denominator for two or more integer values.

Return type: Whole number (like 1, 132, 2344)
Syntax

GCD(value; value)

Parameters
Comment: First number
Type: A range of whole numbers (like 1, 132, 2344)
Comment: Second number
Type: A range of whole numbers (like 1, 132, 2344)
Comment: Third number
Type: 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 

G_PRODUCT

The G_PRODUCT() function is the same as KPRODUCT. It is provided for Gnumeric compatibility.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

G_PRODUCT(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Related Functions
KPRODUCT 

INT

The INT() function returns the integer part of the value.

Return type: Whole number (like 1, 132, 2344)
Syntax

INT(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

INT(12.55) equals 12

Examples

INT(15) equals 15

Related Functions
FLOOR 
QUOTIENT 

INV

This function multiplies each value by -1.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

INV(value)

Parameters
Comment: Floating point value
Type: 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

KPRODUCT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

KPRODUCT(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

KPRODUCT(3;5;7) equals 105

Examples

KPRODUCT(12.5;2) equals 25

Related Functions
G_PRODUCT 
MULTIPLY 
PRODUCT 

LCM

The LCM() function returns the least common multiple for two or more float values

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LCM(value; value)

Parameters
Comment: First number
Type: FLOAT
Comment: Second number
Type: 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 

LN

The LN() function returns the natural logarithm of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LN(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LN(0.8) equals -0.22314355

Examples

LN(0) equals -inf

Related Functions
LOG 
LOG10 
LOG2 

LOG

The LOG() function returns the base-10 logarithm of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LOG(x)

Parameters
Comment: A floating point value, greater than zero
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LOG(0.8) equals -0.09691001

Examples

LOG(0) is an error.

Related Functions
LN 
LOGN 
LOG10 
LOG2 

LOG10

The LOG10() function returns the base-10 logarithm of the argument.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LOG10(x)

Parameters
Comment: A positive floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LOG10(10) equals 1.

Examples

LOG10(0) is an error.

Related Functions
LN 
LOGN 
LOG 
LOG2 

LOG2

The LOG2() function returns the base-2 logarithm of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LOG2(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LOG2(0.8) equals -0.32192809

Examples

LOG2(0) equals -inf.

Related Functions
LN 
LOGN 
LOG 
LOG10 

LOGN

The LOGn() function returns the base n logarithm of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LOGn(value;base)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Base
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LOGn(12;10) equals 1.07918125

Examples

LOGn(12;2) equals 3.5849625

Related Functions
LOG 
LN 
LOG10 
LOG2 

MAX

The MAX() function returns the largest value given in the parameters. String and logical values are ignored.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MAX(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

MAX(12;5; 7) returns 12

Examples

MAX(12.5; 2) returns 12.5

Examples

MAX(0.5; 0.4; TRUE; 0.2) returns 0.5

Related Functions
COUNT 
COUNTA 
MAXA 
MIN 
MINA 

MAXA

The MAXA() function returns the largest value given in the parameters. TRUE evaluates to 1, FALSE evaluates to 0. String values are ignored.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MAXA(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

MAXA(12;5; 7) returns 12

Examples

MAXA(12.5; 2) returns 12.5

Examples

MAXA(0.5; 0.4; TRUE; 0.2) returns 1

Related Functions
COUNT 
COUNTA 
MAX 
MIN 
MINA 

MDETERM

Function MDETERM returns the determinant of a given matrix. The matrix must be of type n x n.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MDETERM(matrix)

Parameters
Comment: Range
Type: A range of floating point values (like 1.3, 0.343, 253 )
Examples

MDETERM(A1:C3)

Related Functions
MMULT 

MIN

The MIN() function returns the smallest value given in the parameters. String and logical values are ignored.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MIN(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

MIN(12;5; 7) returns 5

Examples

MIN(12.5; 2) returns 2

Examples

MIN(0.4; 2; FALSE; 0.7) returns 0.4

Related Functions
COUNT 
COUNTA 
MAX 
MAXA 
MINA 

MINA

The MINA() function returns the smallest value given in the parameters. TRUE evaluates to 1, FALSE to 0. String values are ignored.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MINA(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

MINA(12;5; 7) returns 5

Examples

MINA(12.5; 2) returns 2

Examples

MINA(0.4; 2; FALSE; 0.7) returns 0.

Related Functions
COUNT 
COUNTA 
MAX 
MAXA 
MIN 

MINVERSE

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.

Return type: A range of floating point values (like 1.3, 0.343, 253 )
Syntax

MINVERSE(matrix)

Parameters
Comment: Matrix
Type: A range of floating point values (like 1.3, 0.343, 253 )
Examples

MINVERSE(A1:C3)

Related Functions
MDETERM 
MUNIT 

MMULT

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.

Return type: A range of floating point values (like 1.3, 0.343, 253 )
Syntax

MMULT(matrix1;matrix2)

Parameters
Comment: First matrix
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Second matrix
Type: A range of floating point values (like 1.3, 0.343, 253 )
Examples

MMULT(A1:C3)

Related Functions
MDETERM 

MOD

The MOD() function returns the remainder after division. If the second parameter is null the function returns #DIV/0.

Return type: Whole number (like 1, 132, 2344)
Syntax

MOD(value;value)

Parameters
Comment: Floating point value
Type: Whole number (like 1, 132, 2344)
Comment: Floating point value
Type: Whole number (like 1, 132, 2344)
Examples

MOD(12;5) returns 2

Examples

MOD(5;5) returns 0

Related Functions
DIV 

MROUND

The MROUND() function returns the value rounded to the specified multiple. The value and the multiple must have the same sign

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MROUND(value; multiple)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Multiple
Type: 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 

MULTINOMIAL

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!

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MULTINOMIAL(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

MULTINOMIAL(3;4;5) equals 27720

MULTIPLY

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MULTIPLY(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

MULTIPLY(12;5;7) equals 420

Examples

MULTIPLY(12.5;2) equals 25

Related Functions
DIV 
PRODUCT 
KPRODUCT 

MUNIT

Creates the unity matrix of the given dimension.

Return type: A range of floating point values (like 1.3, 0.343, 253 )
Syntax

MUNIT(dimension)

Parameters
Comment: Dimension
Type: Whole number (like 1, 132, 2344)
Examples

MUNIT(3) creates a 3x3 unity matrix

Related Functions
MINVERSE 

ODD

The ODD() function returns the number rounded up (or down, for negative values) to the nearest odd integer. By definition, ODD(0) is 1.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ODD(value)

Parameters
Comment: Floating point value
Type: 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 

POW

The POW(x;y) function returns the value of x raised to the power of y. It's the same as POWER.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

POW(value;value)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Floating point value
Type: 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 

POWER

The POWER(x;y) function returns the value of x raised to the power of y.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

POWER(value;value)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Floating point value
Type: 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 

PRODUCT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PRODUCT(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

PRODUCT(3;5;7) equals 105

Examples

PRODUCT(12.5;2) equals 25

Related Functions
MULTIPLY 
KPRODUCT 

QUOTIENT

Function QUOTIENT returns the integer portion of numerator/denumerator.

Return type: Whole number (like 1, 132, 2344)
Syntax

QUOTIENT(numerator;denumerator)

Parameters
Comment: Numerator
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Denumerator
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

QUOTIENT(21;4) returns 5

Related Functions
INT 

RAND

The RAND() function returns a pseudo-random number between 0 and 1.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RAND()

Parameters

Examples

RAND() equals for example 0.78309922...

Related Functions
RANDBETWEEN 
RANDEXP 

RANDBERNOULLI

The RANDBERNOULLI() function returns a Bernoulli-distributed pseudo-random number.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

RANDBERNOULLI(0.45)

Related Functions
RAND 

RANDBETWEEN

The RANDBETWEEN() function returns a pseudo-random number between bottom and top value. If bottom > top this function returns Err.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RANDBETWEEN(bottom;top)

Parameters
Comment: Bottom value
Type: Whole number (like 1, 132, 2344)
Comment: Top value
Type: Whole number (like 1, 132, 2344)
Examples

RANDBETWEEN(12;78) equals for example 61.0811...

Related Functions
RAND 

RANDBINOM

The RANDBINOM() function returns a binomially-distributed pseudo-random number.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

RANDEXP

The RANDEXP() function returns an exponentially-distributed pseudo-random number.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

RANDNEGBINOM

The RANDNEGBINOM() function returns a negative binomially-distributed pseudo-random number.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RANDNEGBINOM(x)

Parameters
Comment: A floating point value (between 0 and 1)
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Failures (greater 0)
Type: Whole number (like 1, 132, 2344)
Examples

RANDNEGBINOM(4)

Related Functions
RAND 
RANDBINOM 

RANDNORM

The RANDNORM() function returns a Normal(Gaussian)-distributed pseudo-random number.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RANDNORM(mu; sigma)

Parameters
Comment: Mean value of the normal distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Dispersion of the normal distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

RANDNORM(0; 1)

Related Functions
RAND 

RANDPOISSON

The RANDPOISSON() function returns a poisson-distributed pseudo-random number.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

RANDPOISSON(4)

Related Functions
RAND 

ROOTN

The ROOTN() function returns the non-negative nth root of x.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ROOTN(x;n)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Value
Type: Whole number (like 1, 132, 2344)
Examples

ROOTN(9;2) equals 3

Related Functions
SQRT 

ROUND

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ROUND(value;[digits])

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Digits
Type: 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 

ROUNDDOWN

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ROUNDDOWN(value;[digits])

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Digits
Type: 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 

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ROUNDUP(value;[digits])

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Digits
Type: 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 

SERIESSUM

The SERIESSUM() function returns the sum of a power series.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SERIESSUM( X; N; M; Coefficients)

Parameters
Comment: X the independent variable of the power series
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: N the initial power to which X is to be raised
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: M the increment by which to increase N for each term in the series
Type: 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 multiplied
Type: FLOAT
Examples

SERIESSUM(2;0;2;{1;2}) return 9

SIGN

This function returns -1 if the number is negative, 0 if the number is null and 1 if the number is positive.

Return type: Whole number (like 1, 132, 2344)
Syntax

SIGN(value)

Parameters
Comment: Floating point value
Type: 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

SQRT

The SQRT() function returns the non-negative square root of the argument. It is an error if the argument is negative.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SQRT(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SQRT(9) equals 3

Examples

SQRT(-9) is an error

Related Functions
IMSQRT 

SQRTPI

The SQRTPI() function returns the non-negative square root of x * PI. It is an error if the argument is negative.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SQRTPI(x)

Parameters
Comment: A floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SQRTPI(2) equals 2.506628

SUBTOTAL

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUBTOTAL(function; value)

Parameters
Comment: Function
Type: Whole number (like 1, 132, 2344)
Comment: Values
Type: 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 

SUM

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUM(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

SUM(12;5;7) equals 24

Examples

SUM(12.5;2) equals 14.5

Related Functions
SUMA 
SUMSQ 
SUMIF 

SUMA

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUM(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

SUMA(12;5; 7) equals 24

Examples

SUMA(12.5; 2; TRUE) equals 15.5

Related Functions
SUM 
SUMSQ 

SUMIF

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUMIF(checkrange;criteria;sumrange)

Parameters
Comment: Check range
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Criteria
Type: Text
Comment: Sum range
Type: 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 

SUMSQ

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUMSQ(value;value;...)

Parameters
Comment: Values
Type: FLOAT
Examples

SUMSQ(12;5;7) equals 218

Examples

SUMSQ(12.5;2) equals 173

Related Functions
SUM 

TRANSPOSE

Returns the transpose of a matrix, i.e. rows and columns of the matrix are exchanged.

Return type: A range of floating point values (like 1.3, 0.343, 253 )
Syntax

TRANSPOSE(matrix)

Parameters
Comment: Matrix
Type: A range of floating point values (like 1.3, 0.343, 253 )
Examples

TRANSPOSE(A1:C3)

TRUNC

The TRUNC() function truncates a numeric value to a certain precision. If the precision is omitted 0 is assumed.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TRUNC(value; precision)

Parameters
Comment: Floating point value
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Precision
Type: Whole number (like 1, 132, 2344)
Examples

TRUNC(1.2) returns 1

Examples

TRUNC(213.232; 2) returns 213.23

Related Functions
ROUND 
ROUNDDOWN 
ROUNDUP 

Statistical

AVEDEV

The AVEDEV() function calculates the average of the absolute deviations of a data set from their mean.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

AVEDEV(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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) ...

AVERAGE

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

AVERAGE(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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

AVERAGEA

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

AVERAGEA(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: String values
Type: Text
Examples

AVERAGEA(11.4;17.3;"sometext";25.9;40.1) equals 18.94

BETADIST

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BETADIST(number;alpha;beta;start;end;[cumulative=TRUE])

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Alpha parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Beta parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Start
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: End
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Cumulative
Type: 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

BETAINV

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BETAINV(number;alpha;beta [; start=0 [; end=1]])

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Alpha parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Beta parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Start
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: End
Type: 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

BINO

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

BINO(trials;success;prob_of_success)

Parameters
Comment: Number of trials
Type: Whole number (like 1, 132, 2344)
Comment: Number of successful trials
Type: Whole number (like 1, 132, 2344)
Comment: Probability of success
Type: Double
Examples

BINO(12;9;0.8) returns 0.236223201

CHIDIST

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CHIDIST(number;degrees_freedom)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Degrees of freedom
Type: Whole number (like 1, 132, 2344)
Examples

CHIDIST(13.27;5) returns 0.021

COMBIN

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

COMBIN(total;chosen)

Parameters
Comment: Total number of elements
Type: Whole number (like 1, 132, 2344)
Comment: Number of elements to choose
Type: Whole number (like 1, 132, 2344)
Examples

COMBIN(12;5) returns 792

Examples

COMBIN(5;5) returns 1

COMBINA

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

COMBIN(total;chosen)

Parameters
Comment: Total number of elements
Type: Whole number (like 1, 132, 2344)
Comment: Number of elements to choose
Type: Whole number (like 1, 132, 2344)
Examples

COMBIN(12;5) returns 792

Examples

COMBIN(5;5) returns 1

CONFIDENCE

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CONFIDENCE(alpha;stddev;size)

Parameters
Comment: Level of the confidence interval
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Standard deviation for the total population
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Size of the total population
Type: Whole number (like 1, 132, 2344)
Examples

CONFIDENCE(0.05;1.5;100) equals 0.294059

CORREL

The CORREL() function calculates the correlation coefficient of two cell ranges.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CORREL(range1; range2)

Parameters
Comment: Cell range of values
Type: Double
Comment: Second cell range of values
Type: Double
Examples

CORREL(A1:A3; B1:B3)

Related Functions
PEARSON 

COVAR

The COVAR() function calculates the covariance of two cell ranges.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COVAR(range1; range2)

Parameters
Comment: Cell range of values
Type: Double
Comment: Second cell range of values
Type: Double
Examples

COVAR(A1:A3; B1:B3)

DEVSQ

The DEVSQ() function calculates the sum of squares of deviations.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

DEVSQ(value; value;...)

Parameters
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Examples

DEVSQ(A1:A5)

Examples

DEVSQ(21; 33; 54; 23) returns 684.75

EXPONDIST

The EXPONDIST() function returns the exponential distribution.

The lambda parameter must be positive.

Cumulative = 0 calculates the density function; cumulative = 1 calculates the distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

EXPONDIST(number;lambda;cumulative)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Lambda parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: 0 = density, 1 = distribution
Type: Whole number (like 1, 132, 2344)
Examples

EXPONDIST(3;0.5;0) equals 0.111565

Examples

EXPONDIST(3;0.5;1) equals 0.776870

FDIST

The FDIST() function returns the f-distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FDIST(number;degrees_freedom_1;degrees_freedom_2)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Degrees of freedom 1
Type: Whole number (like 1, 132, 2344)
Comment: Degrees of freedom 2
Type: Whole number (like 1, 132, 2344)
Examples

FDIST(0.8;8;12) yields 0.61

FINV

The FINV() function returns the unique non-negative number x such that FDIST(x;r1;r2) = p.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FINV(number; r1; r2)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Number r1
Type: Whole number (like 1, 132, 2344)
Comment: Number r2
Type: Whole number (like 1, 132, 2344)
Examples

FDIST(FINV(0.1;3;4);3;4) equals 0.1

FISHER

The FISHER() function returns the Fisher transformation for x and creates a function close to a normal distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FISHER(number)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

FISHER(0.2859) equals 0.294096

Examples

FISHER(0.8105) equals 1.128485

FISHERINV

The FISHERINV() function returns the inverse of the Fisher transformation for x and creates a function close to a normal distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

FISHERINV(number)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

FISHERINV(0.2859) equals 0.278357

Examples

FISHERINV(0.8105) equals 0.669866

FREQUENCY

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 )

GAMMADIST

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GAMMADIST(number;alpha;beta;cumulated)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Alpha parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Beta parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Cumulated flag
Type: 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

GAMMAINV

The GAMMAINV() function returns the unique number x >= 0 such that GAMMAINV(x;alpha;beta;TRUE()) = p.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GAMMAINV(number;alpha;beta)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Alpha parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Beta parameter
Type: 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

GAMMALN

The GAMMALN() function returns the natural logarithm of the gamma function: G(x). The number parameter must be positive.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GAMMALN(Number)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

GAMMALN(2) returns 0

GAUSS

The GAUSS() function returns the integral values for the standard normal cumulative distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GAUSS(value)

Parameters
Comment: The number for which the integral value of standard normal distribution is to be calculated
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

GAUSS(0.25) equals 0.098706

GEOMEAN

The GEOMEAN() function returns the geometric mean of the given arguments. This is equal to the Nth root of the product of the terms.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

GEOMEAN(value; value;...)

Parameters
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Examples

GEOMEAN(A1:A5)

Examples

GEOMEAN(21; 33; 54; 23) returns 30.45886

Related Functions
HARMEAN 

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

HARMEAN(value; value;...)

Parameters
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Examples

HARMEAN(A1:A5)

Examples

HARMEAN(21; 33; 54; 23) returns 28.588

Related Functions
GEOMEAN 

HYPGEOMDIST

The HYPGEOMDIST() function returns the hypergeometric distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

HYPGEOMDIST(x; n; M; N)

Parameters
Comment: Number of success in the sample
Type: Whole number (like 1, 132, 2344)
Comment: Number of trials
Type: Whole number (like 1, 132, 2344)
Comment: Number of success overall
Type: Whole number (like 1, 132, 2344)
Comment: Population size
Type: Whole number (like 1, 132, 2344)
Examples

HYPGEOMDIST(2; 5; 6; 20) returns 0.3522

INTERCEPT

The INTERCEPT() function calculates the interception of the linear regression line with the y axis.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 )

INVBINO

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

INVBINO(trials;failure;prob_of_failure)

Parameters
Comment: Number of trials
Type: Whole number (like 1, 132, 2344)
Comment: Number of failures
Type: Whole number (like 1, 132, 2344)
Comment: Probability of failure
Type: Double
Examples

INVBINO(12;3;0.2) returns 0.236223201

KURT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

KURT(value; value;...)

Parameters
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Examples

KURT(A1:A5)

Examples

KURT(21; 33; 54; 23) returns 1.344239

Related Functions
KURTP 

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

KURTP(value; value;...)

Parameters
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Comment: Floating point values
Type: Double
Examples

KURTP(A1:A5)

Examples

KURTP(21; 33; 54; 23) returns -1.021

Related Functions
KURT 

LARGE

The LARGE() function returns the k-th largest value from the data set.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LARGE(range; k)

Parameters
Comment: Cell range of values
Type: Double
Comment: Position (from the largest)
Type: Whole number (like 1, 132, 2344)
Examples

A1: 3, A2: 1, A3: 5 => LARGE(A1:A3; 2) returns 3

LEGACYFDIST

The LEGACYFDIST() function returns the f-distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LEGACYFDIST(number;degrees_freedom_1;degrees_freedom_2)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Degrees of freedom 1
Type: Whole number (like 1, 132, 2344)
Comment: Degrees of freedom 2
Type: Whole number (like 1, 132, 2344)
Examples

LEGACYFDIST(0.8;8;12) yields 0.61

LOGINV

The LOGINV() function returns the inverse of the lognormal cumulative distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LOGINV(p; mean; stdev)

Parameters
Comment: Probability
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Mean value of the standard logarithmic distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Standard deviation of the standard logarithmic distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LOGINV(0.1;0;1) equals 0.2776

LOGNORMDIST

The LOGNORMDIST() function returns the cumulative lognormal distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

LOGNORMDIST(Number;MV;STD)

Parameters
Comment: Probability value for which the standard logarithmic distribution is to be calculated
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Mean value of the standard logarithmic distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Standard deviation of the standard logarithmic distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

LOGNORMDIST(0.1;0;1) equals 0.01

MEDIAN

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MEDIAN(value;value;...)

Parameters
Comment: Floating point value or range of values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values or range of values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values or range of values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values or range of values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values or range of values
Type: 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

MODE

The MODE() function returns the most frequently occurring value in the data set.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

MODE(number; number2; ...)

Parameters
Comment: Float
Type: Double
Comment: Float
Type: Double
Comment: Float
Type: Double
Comment: Float
Type: Double
Examples

MODE(12; 14; 12; 15) returns 12

NEGBINOMDIST

The NEGBINOMDIST() function returns the negative binomial distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NEGBINOMDIST(failures; success; prob_of_success)

Parameters
Comment: Number of failures
Type: Whole number (like 1, 132, 2344)
Comment: Number of successful trials
Type: Whole number (like 1, 132, 2344)
Comment: Probability of success
Type: Double
Examples

NEGBINOMDIST(2;5;0.55) returns 0.152872629

NORMDIST

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NORMDIST(Number;MV;STD;K)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Linear middle of the distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Standard deviation of the distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: 0 = density, 1 = distribution
Type: 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

NORMINV

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NORMINV(number;MV;STD)

Parameters
Comment: Probability value for which the standard logarithmic distribution is to be calculated
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Middle value in the normal distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Standard deviation of the normal distribution
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

NORMINV(0.9;63;5) equals 69.41

NORMSDIST

The NORMSDIST() function returns the standard normal distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NORMSDIST(Number)

Parameters
Comment: Value to which the standard normal distribution is calculated
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

NORMSDIST(1) equals 0.84

NORMSINV

The NORMSINV() function returns the inverse of the standard normal cumulative distribution. The number must be between 0 and 1 (non-inclusive).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

NORMSINV(Number)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

NORMSINV(0.908789) returns 1.3333

PEARSON

The PEARSON() function calculates the correlation coefficient of two cell ranges. It is the same as the CORREL function.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PEARSON(range1; range2)

Parameters
Comment: Cell range of values
Type: Double
Comment: Second cell range of values
Type: Double
Examples

PEARSON(A1:A3; B1:B3)

Related Functions
CORREL 

PERCENTILE

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PERCENTILE(data;alpha)

Parameters
Comment: Range of values
Type: 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 

PERMUT

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

PERMUT(total;permutated)

Parameters
Comment: Total number of elements
Type: Whole number (like 1, 132, 2344)
Comment: Number of elements to permutate
Type: Whole number (like 1, 132, 2344)
Examples

PERMUT(8;5) equals 6720

Examples

PERMUT(1;1) equals 1

PERMUTATIONA

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

PERMUTATIONA(total;chosen)

Parameters
Comment: Total number of elements
Type: Whole number (like 1, 132, 2344)
Comment: Number of elements to choose
Type: Whole number (like 1, 132, 2344)
Examples

PERMUTATIONA(2,3) returns 8

Examples

PERMUTATIONA(0,0) returns 1

PHI

The PHI() function returns value of the distribution function for a standard normal distribution.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PHI(value)

Parameters
Comment: The number for which the standard normal distribution is to be calculated
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

PHI(0.25) equals 0.386668

POISSON

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

POISSON(number;lambda;cumulative)

Parameters
Comment: Number
Type: 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 = distribution
Type: Whole number (like 1, 132, 2344)
Examples

POISSON(60;50;0) equals 0.020105

Examples

POISSON(60;50;1) equals 0.927840

RANK

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

RSQ(Value; Data; Order)

Parameters
Comment: Value
Type: 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: Order
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

RANK (2;{1;2;3}) equals 2

RSQ

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 )

SKEW

The SKEW() function returns an estimate for skewness of a distribution

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SKEW(number; number2; ...)

Parameters
Comment: Float
Type: Double
Comment: Float
Type: Double
Comment: Float
Type: Double
Comment: Float
Type: Double
Examples

SKEW(11.4; 17.3; 21.3; 25.9; 40.1) returns 0.9768

Related Functions
SKEWP 

SKEWP

The SKEWP() function returns the population skewness of a distribution

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SKEWP(number; number2; ...)

Parameters
Comment: Float
Type: Double
Comment: Float
Type: Double
Comment: Float
Type: Double
Comment: Float
Type: Double
Examples

SKEWP(11.4; 17.3; 21.3; 25.9; 40.1) returns 0.6552

Related Functions
SKEW 

SLOPE

The SLOPE() function calculates the slope of the linear regression line.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 )

SMALL

The SMALL() function returns the k-th smallest value from the data set.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SMALL(range; k)

Parameters
Comment: Cell range of values
Type: Double
Comment: Position (from the smallest)
Type: Whole number (like 1, 132, 2344)
Examples

A1: 3, A2: 1, A3: 5 => SMALL(A1:A3; 1) returns 1

STANDARDIZE

The STANDARDIZE() function calculates a normalized value.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

STANDARDIZE(x; mean, stdev)

Parameters
Comment: Number to be normalized
Type: Double
Comment: Mean of the distribution
Type: Double
Comment: Standard deviation
Type: Double
Examples

STANDARDIZE(4; 3; 7) returns 0.1429

STDEV

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

STDEV(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Examples

STDEV(6;7;8) equals 1

Related Functions
STDEVP 

STDEVA

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

STDEVA(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

STDEVP

The STDEVP() function returns the standard deviation based on an entire population

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

STDEVP(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Examples

STDEVP(6;7;8) equals 0.816497...

Related Functions
STDEV 

STDEVPA

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

STDEVPA(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

STEYX

The STEYX() function calculates the standard error of the predicted y value for each x in the regression.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 )

SUM2XMY

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SLOPE(y;x)

Parameters

SUMPRODUCT

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUMPRODUCT(array1;array2)

Parameters
Comment: Value (array)
Type: Double
Comment: Value (array)
Type: Double
Examples

SUMPRODUCT(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns 31

SUMX2MY2

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUMX2MY2(array1;array2)

Parameters
Comment: Value (array)
Type: Double
Comment: Value (array)
Type: Double
Examples

SUMX2MY2(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns -5

SUMX2PY2

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUMX2PY2(array1;array2)

Parameters
Comment: Value (array)
Type: Double
Comment: Value (array)
Type: Double
Examples

SUMX2PY2(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns 63

SUMXMY2

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SUMXMY2(array1;array2)

Parameters
Comment: Value (array)
Type: Double
Comment: Value (array)
Type: Double
Examples

SUMXMY2(A1:A2;B1:B2) with A1=2, A2=5, B1=3 and B2=5, returns 1

TDIST

The TDIST() function returns the t-distribution.

Mode = 1 returns the one-tailed test, Mode = 2 returns the two-tailed test.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TDIST(number;degrees_freedom;mode)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Degrees of freedom for the t-distribution
Type: 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

TREND

The TREND() function calculates a sequence of values based on a linear regression of known value pairs.

Constraints: COUNT(knownY) = COUNT(knownX).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TREND(knownY[;knownX[;newX[;allowOffset = TRUE]]])

Parameters
Comment: KnownY
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: KnownX
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: NumberSequence newX
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: allowOffset
Type: A truth value (TRUE or FALSE)

TRIMMEAN

The TRIMMEAN() function calculates the mean of a data set's fraction.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

TRIMMEAN(dataSet; cutOffFraction)

Parameters
Comment: dataSet
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: cutOffFraction
Type: A floating point value (like 1.3, 0.343, 253 )

TTEST

The TTEST() function calculates the probability of a t-test.

Return type: A floating point value (like 1.3, 0.343, 253 )
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: type
Type: Whole number (like 1, 132, 2344)
Comment: mode
Type: Whole number (like 1, 132, 2344)

VAR

The VAR() function calculates the estimates variance based on a sample.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

VAR(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

VARA

The VARA() function calculates the variance based on a sample.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

VARA(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

VARIANCE

The VARIANCE() function calculates the estimates variance based on a sample. It's the same as the VAR function.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

VARIANCE(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

VARP

The VARP() function calculates the variance based on an entire population.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

VARP(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

VARPA(value;value;...)

Parameters
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: A range of floating point values (like 1.3, 0.343, 253 )
Comment: Floating point values
Type: 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 

WEIBULL

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

WEIBULL(number;alpha;beta;cumulative)

Parameters
Comment: Number
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Alpha parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: Beta parameter
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: 0 = density, 1 = distribution
Type: Whole number (like 1, 132, 2344)
Examples

WEIBULL(2;1;1;0) equals 0.135335

Examples

WEIBULL(2;1;1;1) equals 0.864665

ZTEST

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ZTEST(x; mean; standardDeviation)

Parameters
Comment: x (array)
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: mean
Type: A floating point value (like 1.3, 0.343, 253 )
Comment: standardDeviation
Type: A floating point value (like 1.3, 0.343, 253 )

Text

ASC

The ASC() function returns the half-width characters corresponding to the full-width argument.

Return type: Text
Syntax

ASC(text)

Parameters
Comment: Full width characters
Type: Text
Related Functions
JIS 

BAHTTEXT

The BAHTTEXT() function converts a number to a text in Thai characters (baht).

Return type: Text
Syntax

BAHTTEXT(number)

Parameters
Comment: Number
Type: Whole number (like 1, 132, 2344)
Examples

BAHTTEXT(23) returns "ยสบสามบาทถวน"

CHAR

The CHAR() function returns the character specified by a number.

Return type: Text
Syntax

CHAR(code)

Parameters
Comment: Character code
Type: Whole number (like 1, 132, 2344)
Examples

CHAR(65) returns "A"

Related Functions
CODE 

CLEAN

The CLEAN() function removes every non-printable character from the string

Return type: Text
Syntax

CLEAN(text)

Parameters
Comment: Source string
Type: Text
Examples

CLEAN(AsciiToChar(7) + "HELLO") returns "HELLO"

CODE

The CODE() function returns a numeric code for the first character in a text string.

Return type: Whole number (like 1, 132, 2344)
Syntax

CODE(text)

Parameters
Comment: Text
Type: Text
Examples

CODE("KDE") returns 75

Related Functions
CHAR 

COMPARE

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

COMPARE(string1; string2; true|false)

Parameters
Comment: First string
Type: Text
Comment: String to compare with
Type: Text
Comment: 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 

CONCATENATE

The CONCATENATE() function returns a string which is the concatenation of the strings passed as parameters.

Return type: Text
Syntax

CONCATENATE(value;value;...)

Parameters
Comment: String values
Type: A range of strings
Comment: String values
Type: A range of strings
Comment: String values
Type: A range of strings
Comment: String values
Type: A range of strings
Comment: String values
Type: A range of strings
Examples

CONCATENATE("Sheets";"Calligra";"KDE") returns "SheetsCalligraKDE"

DOLLAR

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.

Return type: Text
Syntax

DOLLAR(number;decimals)

Parameters
Comment: Number
Type: Double
Comment: Decimals
Type: Whole number (like 1, 132, 2344)
Examples

DOLLAR(1403.77) returns "$ 1,403.77"

Examples

DOLLAR(-0.123;4) returns "$-0.1230"

EXACT

The EXACT() function returns True if these two strings are equal. Otherwise, it returns False.

Return type: A truth value (TRUE or FALSE)
Syntax

EXACT(string1;string2)

Parameters
Comment: String
Type: Text
Comment: String
Type: Text
Examples

EXACT("Calligra";"Calligra") returns True

Examples

EXACT("KSpread";"Calligra") returns False

Related Functions
COMPARE 

FIND

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

FIND(find_text;within_text;start_num)

Parameters
Comment: The text you want to find
Type: Text
Comment: The text which may contain find_text
Type: Text
Comment: Specifies index to start the search
Type: 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 

FINDB

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

FINDB(find_text;within_text;BytePosition Start)

Parameters
Comment: The text you want to find
Type: Text
Comment: The text which may contain find_text
Type: Text
Comment: Specifies byte position to start the search
Type: Whole number (like 1, 132, 2344)
Related Functions
FIND 
SEARCH 
REPLACE 
SEARCHB 
REPLACEB 

FIXED

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.

Return type: Text
Syntax

FIXED(number;decimals;no_commas)

Parameters
Comment: Number
Type: Double
Comment: Decimals
Type: Whole number (like 1, 132, 2344)
Comment: No_commas
Type: 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"

JIS

The JIS() function returns the full-width characters corresponding to the half-width argument.

Return type: Text
Syntax

JIS(text)

Parameters
Comment: Half-width characters
Type: Text
Related Functions
ASC 

LEFT

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.

Return type: Text
Syntax

LEFT(text;length)

Parameters
Comment: Source string
Type: Text
Comment: Number of characters
Type: Whole number (like 1, 132, 2344)
Examples

LEFT("hello";2) returns "he"

Examples

LEFT("KSpread";10) returns "KSpread"

Examples

LEFT("KSpread") returns "K"

Related Functions
RIGHT 
MID 
RIGHTB 
MIDB 

LEFTB

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.

Return type: Text
Syntax

LEFTB(text;ByteLength)

Parameters
Comment: Source string
Type: Text
Comment: Byte Length
Type: Whole number (like 1, 132, 2344)
Related Functions
RIGHT 
MID 
RIGHTB 
MIDB 

LEN

The LEN() function returns the length of the string.

Return type: Whole number (like 1, 132, 2344)
Syntax

LEN(text)

Parameters
Comment: String
Type: Text
Examples

LEN("hello") returns 5

Examples

LEN("KSpread") returns 7

Related Functions
LENB 

LENB

The LENB() function returns the length of the string using byte positions.

Return type: Whole number (like 1, 132, 2344)
Syntax

LENB(text)

Parameters
Comment: String
Type: Text

LOWER

The LOWER() function converts a string to lower case.

Return type: Text
Syntax

LOWER(text)

Parameters
Comment: Source string
Type: Text
Examples

LOWER("hello") returns "hello"

Examples

LOWER("HELLO") returns "hello"

Related Functions
UPPER 
TOGGLE 

MID

The MID() function returns a substring that contains 'length' characters of the string, starting at 'position' index.

Return type: Text
Syntax

MID(text;position;length)

Parameters
Comment: Source string
Type: Text
Comment: Position
Type: Whole number (like 1, 132, 2344)
Comment: Length
Type: 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 

MIDB

The MIDB() function returns a substring that contains 'length' characters of the string, starting at 'position' index using byte positions.

Return type: Text
Syntax

MIDB(text;BytePosition Start;ByteLength)

Parameters
Comment: Source string
Type: Text
Comment: Byte Position
Type: Whole number (like 1, 132, 2344)
Comment: Byte Length
Type: Whole number (like 1, 132, 2344)
Related Functions
LEFT 
RIGHT 
LEFTB 
RIGHTB 
MID 

PROPER

The PROPER() function converts the first letter of each word to uppercase and the rest of the letters to lowercase.

Return type: Text
Syntax

PROPER(string)

Parameters
Comment: String
Type: Text
Examples

PROPER("this is a title") returns "This Is A Title"

REGEXP

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).

Return type: Text
Syntax

REGEXP(text; regexp; default; backref)

Parameters
Comment: Searched text
Type: Text
Comment: Regular expression
Type: Text
Comment: Default value (optional)
Type: Text
Comment: 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"

REGEXPRE

Replaces all matches of a regular expression with the replacement text

Return type: Text
Syntax

REGEXPRE(text; regexp; replacement)

Parameters
Comment: Searched text
Type: Text
Comment: Regular expression
Type: Text
Comment: Replacement
Type: Text
Examples

REGEXPRE("14 and 15 and 16";"[0-9]+";"num") returns "num and num and num"

REPLACE

The REPLACE() function replaces part of a text string with a different text string.

Return type: Text
Syntax

REPLACE(text;position;length;new_text)

Parameters
Comment: Text which you want to replace some characters
Type: Text
Comment: Position of the characters to replace
Type: Whole number (like 1, 132, 2344)
Comment: Number of characters to replace
Type: Whole number (like 1, 132, 2344)
Comment: The text that will replace characters in old text
Type: Text
Examples

REPLACE("abcdefghijk";6;5;"-") returns "abcde-k"

Examples

REPLACE("2002";3;2;"03") returns "2003"

Related Functions
FIND 
MID 
FINDB 
MIDB 

REPLACEB

The REPLACEB() function replaces part of a text string with a different text string using byte positions.

Return type: Text
Syntax

REPLACEB(text;BytePosition;ByteLength Len;new_text)

Parameters
Comment: Text which you want to replace some characters using byte position
Type: Text
Comment: Byte position of the characters to replace
Type: Whole number (like 1, 132, 2344)
Comment: The byte length of characters to replace
Type: Whole number (like 1, 132, 2344)
Comment: The text that will replace characters in old text
Type: Text
Related Functions
FINDB 
MIDB 
FIND 
MID 

REPT

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).

Return type: Text
Syntax

REPT(text;count)

Parameters
Comment: Source string
Type: Text
Comment: Count of repetitions
Type: Whole number (like 1, 132, 2344)
Examples

REPT("KSpread";3) returns "KSpreadKSpreadKSpread"

Examples

REPT("KSpread";0) returns ""

RIGHT

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.

Return type: Text
Syntax

RIGHT(text;length)

Parameters
Comment: Source string
Type: Text
Comment: Number of characters
Type: Whole number (like 1, 132, 2344)
Examples

RIGHT("hello";2) returns "lo"

Examples

RIGHT("KSpread";10) returns "KSpread"

Examples

RIGHT("KSpread") returns "d"

Related Functions
LEFT 
MID 
LEFTB 
MIDB 

RIGHTB

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.

Return type: Text
Syntax

RIGHTB(text;ByteLength)

Parameters
Comment: Source string
Type: Text
Comment: Byte Length
Type: Whole number (like 1, 132, 2344)
Related Functions
LEFT 
MID 
LEFTB 
MIDB 

ROT13

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.

Return type: Text
Syntax

ROT13(Text)

Parameters
Comment: Text
Type: Text
Examples

ROT13("KSpread") returns "XFcernq"

Examples

ROT13("XFcernq") returns "KSpread"

SEARCH

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

SEARCH(find_text;within_text;start_num)

Parameters
Comment: The text you want to find
Type: Text
Comment: The text which may contain find_text
Type: Text
Comment: Specified index to start the search
Type: 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 

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.

Return type: Whole number (like 1, 132, 2344)
Syntax

SEARCHB(find_text;within_text;BytePosition Start)

Parameters
Comment: The text you want to find
Type: Text
Comment: The text which may contain find_text
Type: Text
Comment: Specified byte position to start the search
Type: Whole number (like 1, 132, 2344)
Related Functions
FINDB 
FIND 
SEARCH 

SLEEK

The SLEEK() function removes all spaces from the string.

Return type: Text
Syntax

SLEEK(text)

Parameters
Comment: Source string
Type: Text
Examples

SLEEK("This is some text ") returns "Thisissometext"

Related Functions
TRIM 

SUBSTITUTE

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.

Return type: Text
Syntax

SUBSTITUTE(text; old_text; new_text; instance_num)

Parameters
Comment: Text for which you want to substitute
Type: Text
Comment: Part of text you want to replace
Type: Text
Comment: New text which will be replacement
Type: Text
Comment: Which occurrence to replace
Type: 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 

T

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.

Return type: Text
Syntax

T(value)

Parameters
Comment: Value
Type: Any kind of value
Examples

T("Calligra") returns "Calligra"

Examples

T(1.2) returns "" (empty text)

TEXT

The TEXT() function converts a value to text.

Return type: Text
Syntax

TEXT(value)

Parameters
Comment: Value
Type: Any kind of value
Examples

TEXT(1234.56) returns "1234.56"

Examples

TEXT("KSpread") returns "KSpread"

TOGGLE

The TOGGLE() function changes lowercase characters to uppercase and uppercase characters to lowercase.

Return type: Text
Syntax

TOGGLE(text)

Parameters
Comment: Source string
Type: Text
Examples

TOGGLE("hello") returns "HELLO"

Examples

TOGGLE("HELLO") returns "hello"

Examples

TOGGLE("HeLlO") returns "hElLo"

Related Functions
UPPER 
LOWER 

TRIM

The TRIM() function returns text with only single spaces between words.

Return type: Text
Syntax

TRIM(text)

Parameters
Comment: String
Type: Text
Examples

TRIM(" hello KSpread ") returns "hello KSpread"

UNICHAR

The UNICHAR() function returns the character specified by a unicode code point.

Return type: Text
Syntax

UNICHAR(code)

Parameters
Comment: Character code
Type: Whole number (like 1, 132, 2344)
Examples

UNICHAR(65) returns "A"

Related Functions
UNICODE 
CHAR 

UNICODE

The UNICODE() function returns a unicode code point for the first character in a text string.

Return type: Whole number (like 1, 132, 2344)
Syntax

UNICODE(text)

Parameters
Comment: Text
Type: Text
Examples

UNICODE("KDE") returns 75

Related Functions
UNICHAR 
CODE 

UPPER

The UPPER() function converts a string to upper case.

Return type: Text
Syntax

UPPER(text)

Parameters
Comment: Source string
Type: Text
Examples

UPPER("hello") returns "HELLO"

Examples

UPPER("HELLO") returns "HELLO"

Related Functions
LOWER 
TOGGLE 

VALUE

Converts text string that represents a value to the real value.

Return type: Double
Syntax

VALUE(text)

Parameters
Comment: Text
Type: Text
Examples

VALUE("14.03") returns 14.03

Trigonometric

ACOS

The ACOS() function returns the arc cosine in radians and the value is mathematically defined to be 0 to PI (inclusive).

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

ACOSH

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

ACOT

The ACOT() function returns the inverse cotangent of a number.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ACOT(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

ACOT(0) equals 1.57079633

ASIN

The ASIN() function returns the arc sine in radians and the value is mathematically defined to be -PI/2 to PI/2 (inclusive).

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

ASINH

The ASINH() function calculates the inverse hyperbolic sine of x; that is the value whose hyperbolic sine is x.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

ATAN

The ATAN() function returns the arc tangent in radians and the value is mathematically defined to be -PI/2 to PI/2 (inclusive).

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

ATAN(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

ATAN(0.8) equals 0.67474094

Examples

ATAN(0) equals 0

Related Functions
TAN 
ATAN2 

ATAN2

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.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

ATANH

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).

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

COS

The COS() function returns the cosine of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

COS(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

COS(0) equals 1.0

Examples

COS(PI()/2) equals 0

Related Functions
SIN 
ACOS 

COSH

The COSH() function returns the hyperbolic cosine of x, which is defined mathematically as (exp(x) + exp(-x)) / 2.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

CSC

The CSC() function returns the cosecant of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

CSC(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

CSC(PI()/2) equals 1

CSCH

The CSCH() function returns the hyperbolic cosecant of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
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...

DEGREES

This function transforms a radian angle to a degree angle.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

PI

The PI() function returns the value of PI.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

PI()

Parameters

Examples

PI() equals 3.141592654...

RADIANS

This function transforms a degree angle to a radian angle.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

SEC

The SEC() function returns the secant of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SEC(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SEC(0) equals 1

SECH

The SECH() function returns the hyperbolic secant of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SECH(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SECH(0) equals 1

SIN

The SIN() function returns the sine of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
Syntax

SIN(Float)

Parameters
Comment: Angle (radians)
Type: A floating point value (like 1.3, 0.343, 253 )
Examples

SIN(0) equals 0

Examples

SIN(PI()/2) equals 1

Related Functions
COS 
ASIN 

SINH

The SINH() function returns the hyperbolic sine of x, which is defined mathematically as (exp(x) - exp(-x)) / 2.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

TAN

The TAN() function returns the tangent of x, where x is given in radians.

Return type: A floating point value (like 1.3, 0.343, 253 )
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 

TANH

The TANH() function returns the hyperbolic tangent of x, which is defined mathematically as sinh(x)/cosh(x).

Return type: A floating point value (like 1.3, 0.343, 253 )
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