# Functions

&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

  CommentType First numberWhole number (like 1, 132, 2344) Second numberWhole 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

  CommentType First numberWhole number (like 1, 132, 2344) Amount to left shift byWhole 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

  CommentType First numberWhole number (like 1, 132, 2344) Second numberWhole 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

  CommentType First numberWhole number (like 1, 132, 2344) Amount to right shift byWhole 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

  CommentType First numberWhole number (like 1, 132, 2344) Second numberWhole 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

  CommentType NumeralText

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

  CommentType The ASCII values to convertWhole 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

  CommentType Bool value to convertA 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

  CommentType Bool value to convertA 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

  CommentType RadiusDouble Angle (radians)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

  CommentType RadiusDouble Angle (radians)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

  CommentType A one character string to convertText

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

  CommentType ValueDouble

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

  CommentType Integer value to convertWhole 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 &sheets; can auto-convert numbers to strings if needed, so this function should rarely be needed.

Return type Text

Syntax

  NUM2STRING(value)

Parameters

  CommentType Number to convert into stringA 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

  CommentType Value in XDouble Value in YDouble

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

  CommentType Value in XDouble Value in YDouble

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

  CommentType NumberWhole number (like 1, 132, 2344) FormatWhole 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

  CommentType HoursWhole number (like 1, 132, 2344) MinutesWhole number (like 1, 132, 2344) SecondsWhole 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

  CommentType Number to convert into stringA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range marking the databaseA range of strings String marking the column in the databaseText Range marking the conditionsA 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

  CommentType Range containing the pivot tableA range of strings Name of the field of which you want the summary dataText

### 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

  CommentType YearWhole number (like 1, 132, 2344) MonthWhole number (like 1, 132, 2344) DayWhole 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

  CommentType DateText

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

  CommentType First dateText Second dateText intervalText

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

  CommentType DateText

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

  CommentType DateText

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

  CommentType Number of day in week (1..7)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

  CommentType YearWhole number (like 1, 132, 2344) MonthWhole number (like 1, 132, 2344) DayWhole 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

  CommentType First (earlier) date valueText Second date valueText

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

  CommentType Date1Text Date2Text MethodA 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

  CommentType YearWhole number (like 1, 132, 2344) MonthWhole 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

  CommentType YearWhole 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

  CommentType YearWhole 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

  CommentType DateText MonthsWhole 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

  CommentType DateText MonthsWhole 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

  CommentType TimeText

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

  CommentType TimeText

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

  CommentType YearWhole 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

  CommentType DateText

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

  CommentType TimeText

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

  CommentType TimeText

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

  CommentType DateText

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

  CommentType Number of month (1..12)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

  CommentType First (earlier) date valueText Second date valueText Calculation modeWhole 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

  CommentType Start dateText End dateText HolidaysText

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

  CommentType TimeText

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

  CommentType TimeText

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

  CommentType HoursWhole number (like 1, 132, 2344) MinutesWhole number (like 1, 132, 2344) SecondsWhole 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

  CommentType TimeText

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

  CommentType UnixtimeWhole 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

  CommentType DateText Method (optional)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

  CommentType DateText Method (optional)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

  CommentType First (earlier) date valueText Second date valueText Calculation modeWhole 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

  CommentType YearWhole 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

  CommentType Start dateText Working daysWhole number (like 1, 132, 2344) HolidaysText

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

  CommentType DateText

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

  CommentType First dateText Second dateText intervalText

#### 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

  CommentType First (earlier) date valueText Second date valueText Calculation modeWhole 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) BaseWhole number (like 1, 132, 2344) MinLengthWhole 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

  CommentType Where the function is evaluatedA floating point value (like 1.3, 0.343, 253 ) Order of the functionWhole 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

  CommentType Where the function is evaluatedA floating point value (like 1.3, 0.343, 253 ) Order of the functionWhole 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

  CommentType Where the function is evaluatedA floating point value (like 1.3, 0.343, 253 ) Order of the functionWhole 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

  CommentType Where the function is evaluatedA floating point value (like 1.3, 0.343, 253 ) Order of the functionWhole 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

  CommentType The value to convertWhole 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

  CommentType The value to convertText The minimum length of the outputWhole 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

  CommentType The value to convertText The minimum length of the outputWhole 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

  CommentType Real coefficientA floating point value (like 1.3, 0.343, 253 ) Imaginary coefficientA 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) From unitText To unitText

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

  CommentType The value to convertWhole number (like 1, 132, 2344) The minimum length of the outputWhole 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

  CommentType The value to convertWhole number (like 1, 132, 2344) The minimum length of the outputWhole 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

  CommentType The value to convertWhole number (like 1, 132, 2344) The minimum length of the outputWhole 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) Floating point valueA 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

  CommentType Lower limitA floating point value (like 1.3, 0.343, 253 ) Upper limitA 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

  CommentType Lower limitA floating point value (like 1.3, 0.343, 253 ) Upper limitA 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) Floating point valueA 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

  CommentType The value to convertText

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

  CommentType The value to convertText

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

  CommentType The value to convertText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA 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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText PowerWhole 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

  CommentType Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA 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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA 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

  CommentType Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA range of strings Complex numberA 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

  CommentType Complex numberText

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

  CommentType Complex numberText

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

  CommentType The value to convertText The minimum length of the outputWhole 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

  CommentType The value to convertText

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

  CommentType The value to convertText The minimum length of the outputWhole 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

  CommentType Issue dateDate First interestDate SettlementDate Annual rate of securityA floating point value (like 1.3, 0.343, 253 ) Par valueA floating point value (like 1.3, 0.343, 253 ) Number of payments per yearA floating point value (like 1.3, 0.343, 253 ) Day counting basisWhole 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

  CommentType Issue dateDate SettlementDate Annual rate of securityA floating point value (like 1.3, 0.343, 253 ) Par valueA floating point value (like 1.3, 0.343, 253 ) Day counting basisWhole 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

  CommentType CostA floating point value (like 1.3, 0.343, 253 ) PvA floating point value (like 1.3, 0.343, 253 ) FvA 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

  CommentType PWhole number (like 1, 132, 2344) PvA floating point value (like 1.3, 0.343, 253 ) FvA 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

  CommentType PrincipalA floating point value (like 1.3, 0.343, 253 ) Interest rateA floating point value (like 1.3, 0.343, 253 ) Periods per yearA floating point value (like 1.3, 0.343, 253 ) YearsA 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

  CommentType PrincipalA floating point value (like 1.3, 0.343, 253 ) Interest rateA floating point value (like 1.3, 0.343, 253 ) YearsA 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

  CommentType SettlementDate MaturityDate FrequencyA floating point value (like 1.3, 0.343, 253 ) Day counting basisWhole 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

  CommentType rateA floating point value (like 1.3, 0.343, 253 ) periodsA floating point value (like 1.3, 0.343, 253 ) valueA floating point value (like 1.3, 0.343, 253 ) startWhole number (like 1, 132, 2344) endWhole number (like 1, 132, 2344) typeWhole 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

  CommentType rateA floating point value (like 1.3, 0.343, 253 ) periodsA floating point value (like 1.3, 0.343, 253 ) valueA floating point value (like 1.3, 0.343, 253 ) startWhole number (like 1, 132, 2344) endWhole number (like 1, 132, 2344) typeWhole 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

  CommentType CostA floating point value (like 1.3, 0.343, 253 ) SalvageA floating point value (like 1.3, 0.343, 253 ) LifeA floating point value (like 1.3, 0.343, 253 ) PeriodA floating point value (like 1.3, 0.343, 253 ) MonthA 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

  CommentType CostA floating point value (like 1.3, 0.343, 253 ) SalvageA floating point value (like 1.3, 0.343, 253 ) LifeA floating point value (like 1.3, 0.343, 253 ) PeriodA floating point value (like 1.3, 0.343, 253 ) FactorA 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

  CommentType SettlementDate MaturityDate Price per $100 face valueA floating point value (like 1.3, 0.343, 253 ) RedemptionA floating point value (like 1.3, 0.343, 253 ) Day counting basisWhole 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

  CommentType Fractional DollarA floating point value (like 1.3, 0.343, 253 ) FractionWhole 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

  CommentType Decimal DollarA floating point value (like 1.3, 0.343, 253 ) FractionWhole 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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) Present value (PV)A floating point value (like 1.3, 0.343, 253 ) Future value (FV)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

  CommentType SettlementDate MaturityDate CouponA floating point value (like 1.3, 0.343, 253 ) YieldA floating point value (like 1.3, 0.343, 253 ) FrequencyA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType Nominal interest rateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType Nominal interest rateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType CurrencyText

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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Source currencyText Target currencyText

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

  CommentType Present valueA floating point value (like 1.3, 0.343, 253 ) RateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType Payment per periodA floating point value (like 1.3, 0.343, 253 ) Interest rateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType SettlementDate MaturityDate InvestmentA floating point value (like 1.3, 0.343, 253 ) RedemptionA floating point value (like 1.3, 0.343, 253 ) Day counting basisWhole 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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) PeriodA floating point value (like 1.3, 0.343, 253 ) Number of periodsA floating point value (like 1.3, 0.343, 253 ) Present valuesA floating point value (like 1.3, 0.343, 253 ) Future value (optional)A floating point value (like 1.3, 0.343, 253 ) Type (optional)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

  CommentType ValuesA floating point value (like 1.3, 0.343, 253 ) GuessA 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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) PeriodWhole number (like 1, 132, 2344) Number of periodsWhole number (like 1, 132, 2344) Present values (PV)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

  CommentType Face valueA floating point value (like 1.3, 0.343, 253 ) Coupon rateA floating point value (like 1.3, 0.343, 253 ) Coupons per yearA floating point value (like 1.3, 0.343, 253 ) YearsA floating point value (like 1.3, 0.343, 253 ) Market interest rateA 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

  CommentType SettlementA floating point value (like 1.3, 0.343, 253 ) MaturityA floating point value (like 1.3, 0.343, 253 ) CouponA floating point value (like 1.3, 0.343, 253 ) YieldA floating point value (like 1.3, 0.343, 253 ) FrequencyA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType ValuesA floating point value (like 1.3, 0.343, 253 ) InvestmentA floating point value (like 1.3, 0.343, 253 ) ReinvestmentA 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

  CommentType Effective interest rateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) PaymentA floating point value (like 1.3, 0.343, 253 ) Present value (PV)A floating point value (like 1.3, 0.343, 253 ) Future value (FV - optional)A floating point value (like 1.3, 0.343, 253 ) Type (optional)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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) Values (array)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

  CommentType SettlementDate MaturityDate LastDate RateA floating point value (like 1.3, 0.343, 253 ) AnnualYieldA floating point value (like 1.3, 0.343, 253 ) RedemptionA floating point value (like 1.3, 0.343, 253 ) FrequencyA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType SettlementDate MaturityDate LastDate RateA floating point value (like 1.3, 0.343, 253 ) PriceA floating point value (like 1.3, 0.343, 253 ) RedemptionA floating point value (like 1.3, 0.343, 253 ) FrequencyA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) Number of periods (NPer)A floating point value (like 1.3, 0.343, 253 ) Present value (PV)A floating point value (like 1.3, 0.343, 253 ) Future value (FV - optional)A floating point value (like 1.3, 0.343, 253 ) Type (optional)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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) PeriodA floating point value (like 1.3, 0.343, 253 ) Number of periodsA floating point value (like 1.3, 0.343, 253 ) Present valueA floating point value (like 1.3, 0.343, 253 ) Future value (optional)A floating point value (like 1.3, 0.343, 253 ) Type (optional)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

  CommentType SettlementDate MaturityDate IssueDate Discount rateA floating point value (like 1.3, 0.343, 253 ) YieldA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType Future valueA floating point value (like 1.3, 0.343, 253 ) Interest rateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType Payment per periodA floating point value (like 1.3, 0.343, 253 ) Interest rateA floating point value (like 1.3, 0.343, 253 ) PeriodsA 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

  CommentType Payment periodA floating point value (like 1.3, 0.343, 253 ) Regular paymentsA floating point value (like 1.3, 0.343, 253 ) Present valueA floating point value (like 1.3, 0.343, 253 ) Future valueA floating point value (like 1.3, 0.343, 253 ) TypeA floating point value (like 1.3, 0.343, 253 ) GuessA 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

  CommentType SettlementDate MaturityDate InvestmentA floating point value (like 1.3, 0.343, 253 ) Discount rateA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType PWhole number (like 1, 132, 2344) PvA floating point value (like 1.3, 0.343, 253 ) FvA 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

  CommentType CostA floating point value (like 1.3, 0.343, 253 ) SalvageA floating point value (like 1.3, 0.343, 253 ) LifeA 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

  CommentType CostA floating point value (like 1.3, 0.343, 253 ) SalvageA floating point value (like 1.3, 0.343, 253 ) LifeA floating point value (like 1.3, 0.343, 253 ) PeriodA 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

  CommentType SettlementDate MaturityDate Discount rateA 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

  CommentType SettlementDate MaturityDate Discount rateA 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

  CommentType SettlementDate MaturityDate Price per $100 face valueA 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

  CommentType SettlementDate MaturityDate PriceA floating point value (like 1.3, 0.343, 253 ) RedemptionA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType ValuesA floating point value (like 1.3, 0.343, 253 ) DatesDate GuessA 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

  CommentType RateA floating point value (like 1.3, 0.343, 253 ) ValuesA floating point value (like 1.3, 0.343, 253 ) DatesDate

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

  CommentType SettlementDate MaturityDate PriceA floating point value (like 1.3, 0.343, 253 ) RedemptionA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType SettlementDate MaturityDate IssueDate Discount rateA floating point value (like 1.3, 0.343, 253 ) PriceA floating point value (like 1.3, 0.343, 253 ) BasisWhole 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

  CommentType Face valueA floating point value (like 1.3, 0.343, 253 ) Interest rateA floating point value (like 1.3, 0.343, 253 ) YearsA 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

  CommentType ErrorAny 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

  CommentType ReferenceReference

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 &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

  CommentType Type of informationText

#### 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType ReferenceReference

#### 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType Any valueAny 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

  CommentType ValueAny 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

  CommentType Any valueAny 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

  CommentType Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA 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

  CommentType ConditionA truth value (TRUE or FALSE) If trueAny kind of value If falseAny 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

  CommentType Any XAny kind of value Any AlternativeAny 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

  CommentType Any XAny kind of value Any AlternativeAny 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

  CommentType Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA 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

  CommentType Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA 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

  CommentType Boolean valueA 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

  CommentType Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA 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

  CommentType Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA range of truth values (TRUE or FALSE) Boolean valuesA 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

  CommentType Row numberWhole number (like 1, 132, 2344) Column numberWhole number (like 1, 132, 2344) Absolute number (optional)Whole number (like 1, 132, 2344) A1 style (optional)A truth value (TRUE or FALSE) Sheet nameText

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

  CommentType ReferenceA 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

  CommentType TypeText ReferenceReference

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

  CommentType IndexWhole number (like 1, 132, 2344) Arguments

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

  CommentType ReferenceText

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

  CommentType ReferenceText

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

  CommentType Lookup valueString/Numeric Data sourceArray RowWhole number (like 1, 132, 2344) Sorted (optional)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

  CommentType ReferenceText RowWhole number (like 1, 132, 2344) ColumnWhole 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

  CommentType ReferenceText A1 style (optional)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

  CommentType Lookup valueString/Numeric Lookup vectorString/Numeric Result vectorString/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

  CommentType Search valueString/Numeric Search regionReference/Array Match type (optional)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

  CommentType Formula cellReference Row cellReference Row replacementReference Column cell (optional)Reference Column replacement (optional)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

  CommentType Reference or rangeReference Number of rows to offsetWhole number (like 1, 132, 2344) Number of columns to offsetWhole number (like 1, 132, 2344) Height of the offset range (optional)Whole number (like 1, 132, 2344) Width of the offset range (optional)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

  CommentType ReferenceText

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

  CommentType ReferenceText

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

  CommentType ReferenceReference

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

  CommentType ReferenceReference

#### 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

  CommentType Lookup valueString/Numeric Data sourceArray ColumnWhole number (like 1, 132, 2344) Sorted (optional)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

  CommentType A floating point valueA 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, returning that value as a double.

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

Syntax

  CEIL(x)

Parameters

  CommentType A floating point valueA 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

#### CEILING

The CEILING() function rounds x up (away from zero) to the nearest multiple of Significance. 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 A floating point value (like 1.3, 0.343, 253 )

Syntax

  CEILING(x)

Parameters

  CommentType A floating point valueA floating point value (like 1.3, 0.343, 253 ) Significance (optional)A floating point value (like 1.3, 0.343, 253 ) Mode (optional)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

#### 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

  CommentType ValuesFLOAT

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

  CommentType ValuesFLOAT

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

  CommentType Cell rangeRange

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

  CommentType RangeA range of floating point values (like 1.3, 0.343, 253 ) CriteriaText

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

  CommentType A floating point valueA 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

  CommentType ValuesFLOAT

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 &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

  CommentType Floating point valueA 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

  CommentType A floating point valueA 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

  CommentType A floating point valueA 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

  CommentType A floating point valueA 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

  CommentType Nth termA 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. 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 A floating point value (like 1.3, 0.343, 253 )

Syntax

  FLOOR(x)

Parameters

  CommentType A floating point valueA floating point value (like 1.3, 0.343, 253 ) Significance (optional)A floating point value (like 1.3, 0.343, 253 ) Mode (optional)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

#### 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

  CommentType Floating point valueA 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

  CommentType First numberA range of whole numbers (like 1, 132, 2344) Second numberA range of whole numbers (like 1, 132, 2344) Third numberA 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

  CommentType ValuesFLOAT

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

  CommentType A floating point valueA 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

  CommentType Floating point valueA 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

  CommentType ValuesFLOAT

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

  CommentType First numberFLOAT Second numberFLOAT

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

  CommentType A floating point valueA 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

  CommentType A floating point value, greater than zeroA 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

  CommentType A positive floating point valueA 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

  CommentType A floating point valueA 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

  CommentType A floating point valueA floating point value (like 1.3, 0.343, 253 ) BaseA 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

  CommentType ValuesFLOAT

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

  CommentType ValuesFLOAT

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

  CommentType RangeA 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

  CommentType ValuesFLOAT

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

  CommentType ValuesFLOAT

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

  CommentType MatrixA 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

  CommentType First matrixA range of floating point values (like 1.3, 0.343, 253 ) Second matrixA 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

  CommentType Floating point valueWhole number (like 1, 132, 2344) Floating point valueWhole 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) MultipleA 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

  CommentType ValuesFLOAT

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

  CommentType ValuesFLOAT

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

  CommentType DimensionWhole 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

  CommentType Floating point valueA 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) Floating point valueA 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) Floating point valueA 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

  CommentType ValuesFLOAT

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

  CommentType NumeratorA floating point value (like 1.3, 0.343, 253 ) DenumeratorA 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

  CommentType A floating point value (between 0 and 1)A floating point value (like 1.3, 0.343, 253 ) A floating point valueA 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

  CommentType Bottom valueWhole number (like 1, 132, 2344) Top valueWhole 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

  CommentType A floating point value (between 0 and 1)A floating point value (like 1.3, 0.343, 253 ) Trials (greater 0)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

  CommentType A floating point value (greater 0)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

  CommentType A floating point value (between 0 and 1)A floating point value (like 1.3, 0.343, 253 ) Failures (greater 0)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

  CommentType Mean value of the normal distributionA floating point value (like 1.3, 0.343, 253 ) Dispersion of the normal distributionA 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

  CommentType A floating point value (greater 0)A floating point value (like 1.3, 0.343, 253 ) A floating point valueA 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

  CommentType A floating point valueA floating point value (like 1.3, 0.343, 253 ) ValueWhole 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) DigitsWhole 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) DigitsWhole 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) DigitsWhole 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

  CommentType X the independent variable of the power seriesA floating point value (like 1.3, 0.343, 253 ) N the initial power to which X is to be raisedA floating point value (like 1.3, 0.343, 253 ) M the increment by which to increase N for each term in the seriesA floating point value (like 1.3, 0.343, 253 ) Coefficients a set of coefficients by which each successive power of the variable X is multipliedFLOAT

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

  CommentType Floating point valueA 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

  CommentType A floating point valueA 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

  CommentType A floating point valueA 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

  CommentType FunctionWhole number (like 1, 132, 2344) ValuesFLOAT

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

  CommentType ValuesFLOAT

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

  CommentType ValuesFLOAT

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

  CommentType Check rangeA range of floating point values (like 1.3, 0.343, 253 ) CriteriaText Sum rangeA 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

  CommentType ValuesFLOAT

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

  CommentType MatrixA 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

  CommentType Floating point valueA floating point value (like 1.3, 0.343, 253 ) PrecisionWhole 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) String valuesText

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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Alpha parameterA floating point value (like 1.3, 0.343, 253 ) Beta parameterA floating point value (like 1.3, 0.343, 253 ) StartA floating point value (like 1.3, 0.343, 253 ) EndA floating point value (like 1.3, 0.343, 253 ) CumulativeA 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Alpha parameterA floating point value (like 1.3, 0.343, 253 ) Beta parameterA floating point value (like 1.3, 0.343, 253 ) StartA floating point value (like 1.3, 0.343, 253 ) EndA 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

  CommentType Number of trialsWhole number (like 1, 132, 2344) Number of successful trialsWhole number (like 1, 132, 2344) Probability of successDouble

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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Degrees of freedomWhole 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

  CommentType Total number of elementsWhole number (like 1, 132, 2344) Number of elements to chooseWhole 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

  CommentType Total number of elementsWhole number (like 1, 132, 2344) Number of elements to chooseWhole 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

  CommentType Level of the confidence intervalA floating point value (like 1.3, 0.343, 253 ) Standard deviation for the total populationA floating point value (like 1.3, 0.343, 253 ) Size of the total populationWhole 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

  CommentType Cell range of valuesDouble Second cell range of valuesDouble

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

  CommentType Cell range of valuesDouble Second cell range of valuesDouble

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

  CommentType Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble

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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Lambda parameterA floating point value (like 1.3, 0.343, 253 ) 0 = density, 1 = distributionWhole 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Degrees of freedom 1Whole number (like 1, 132, 2344) Degrees of freedom 2Whole 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Number r1Whole number (like 1, 132, 2344) Number r2Whole 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

  CommentType NumberA 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

  CommentType NumberA 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

  CommentType Floating point values, that should be counted.A range of floating point values (like 1.3, 0.343, 253 ) Floating point values, representing the upper boundaries of the intervals.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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Alpha parameterA floating point value (like 1.3, 0.343, 253 ) Beta parameterA floating point value (like 1.3, 0.343, 253 ) Cumulated flagWhole 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Alpha parameterA floating point value (like 1.3, 0.343, 253 ) Beta parameterA 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

  CommentType NumberA 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

  CommentType The number for which the integral value of standard normal distribution is to be calculatedA 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

  CommentType Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble

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

  CommentType Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble

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

  CommentType Number of success in the sampleWhole number (like 1, 132, 2344) Number of trialsWhole number (like 1, 132, 2344) Number of success overallWhole number (like 1, 132, 2344) Population sizeWhole 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

  CommentType y values (array)A floating point value (like 1.3, 0.343, 253 ) x values (array)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

  CommentType Number of trialsWhole number (like 1, 132, 2344) Number of failuresWhole number (like 1, 132, 2344) Probability of failureDouble

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

  CommentType Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble

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

  CommentType Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble Floating point valuesDouble

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

  CommentType Cell range of valuesDouble Position (from the largest)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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Degrees of freedom 1Whole number (like 1, 132, 2344) Degrees of freedom 2Whole 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

  CommentType ProbabilityA floating point value (like 1.3, 0.343, 253 ) Mean value of the standard logarithmic distributionA floating point value (like 1.3, 0.343, 253 ) Standard deviation of the standard logarithmic distributionA 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

  CommentType Probability value for which the standard logarithmic distribution is to be calculatedA floating point value (like 1.3, 0.343, 253 ) Mean value of the standard logarithmic distributionA floating point value (like 1.3, 0.343, 253 ) Standard deviation of the standard logarithmic distributionA 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

  CommentType Floating point value or range of valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point values or range of valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point values or range of valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point values or range of valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point values or range of valuesA 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

  CommentType FloatDouble FloatDouble FloatDouble FloatDouble

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

  CommentType Number of failuresWhole number (like 1, 132, 2344) Number of successful trialsWhole number (like 1, 132, 2344) Probability of successDouble

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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Linear middle of the distributionA floating point value (like 1.3, 0.343, 253 ) Standard deviation of the distributionA floating point value (like 1.3, 0.343, 253 ) 0 = density, 1 = distributionWhole 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

  CommentType Probability value for which the standard logarithmic distribution is to be calculatedA floating point value (like 1.3, 0.343, 253 ) Middle value in the normal distributionA floating point value (like 1.3, 0.343, 253 ) Standard deviation of the normal distributionA 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

  CommentType Value to which the standard normal distribution is calculatedA 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

  CommentType NumberA 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

  CommentType Cell range of valuesDouble Second cell range of valuesDouble

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

  CommentType Range of valuesA range of floating point values (like 1.3, 0.343, 253 ) The percentile value between 0 and 1, inclusive.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

  CommentType Total number of elementsWhole number (like 1, 132, 2344) Number of elements to permutateWhole 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

  CommentType Total number of elementsWhole number (like 1, 132, 2344) Number of elements to chooseWhole 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

  CommentType The number for which the standard normal distribution is to be calculatedA 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Lambda parameter (the middle value)A floating point value (like 1.3, 0.343, 253 ) 0 = density, 1 = distributionWhole 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

  CommentType ValueA floating point value (like 1.3, 0.343, 253 ) Data (array)A floating point value (like 1.3, 0.343, 253 ) OrderA 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

  CommentType known Y (array)A floating point value (like 1.3, 0.343, 253 ) known X (array)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

  CommentType FloatDouble FloatDouble FloatDouble FloatDouble

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

  CommentType FloatDouble FloatDouble FloatDouble FloatDouble

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

  CommentType y values (array)A floating point value (like 1.3, 0.343, 253 ) x values (array)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

  CommentType Cell range of valuesDouble Position (from the smallest)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

  CommentType Number to be normalizedDouble Mean of the distributionDouble Standard deviationDouble

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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType y values (array)A floating point value (like 1.3, 0.343, 253 ) x values (array)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

  CommentType Value (array)Double Value (array)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

  CommentType Value (array)Double Value (array)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

  CommentType Value (array)Double Value (array)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

  CommentType Value (array)Double Value (array)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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Degrees of freedom for the t-distributionWhole number (like 1, 132, 2344) Mode (1 or 2)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

  CommentType KnownYA floating point value (like 1.3, 0.343, 253 ) KnownXA floating point value (like 1.3, 0.343, 253 ) NumberSequence newXA floating point value (like 1.3, 0.343, 253 ) allowOffsetA 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

  CommentType dataSetA floating point value (like 1.3, 0.343, 253 ) cutOffFractionA 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

  CommentType x (array)A floating point value (like 1.3, 0.343, 253 ) y (array)A floating point value (like 1.3, 0.343, 253 ) typeWhole number (like 1, 132, 2344) modeWhole 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA range of floating point values (like 1.3, 0.343, 253 ) Floating point valuesA 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

  CommentType NumberA floating point value (like 1.3, 0.343, 253 ) Alpha parameterA floating point value (like 1.3, 0.343, 253 ) Beta parameterA floating point value (like 1.3, 0.343, 253 ) 0 = density, 1 = distributionWhole 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

  CommentType x (array)A floating point value (like 1.3, 0.343, 253 ) meanA floating point value (like 1.3, 0.343, 253 ) standardDeviationA 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

  CommentType Full width charactersText

Related Functions
  - JIS

#### BAHTTEXT

The BAHTTEXT() function converts a number to a text in Thai characters (baht).

Return type Text

Syntax

  BAHTTEXT(number)

Parameters

  CommentType NumberWhole 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

  CommentType Character codeWhole 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

  CommentType Source stringText

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

  CommentType TextText

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

  CommentType First stringText String to compare withText Compare case-sensitive (true/false)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

  CommentType String valuesA range of strings String valuesA range of strings String valuesA range of strings String valuesA range of strings String valuesA 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

  CommentType NumberDouble DecimalsWhole 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

  CommentType StringText StringText

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

  CommentType The text you want to findText The text which may contain find_textText Specifies index to start the searchWhole 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

  CommentType The text you want to findText The text which may contain find_textText Specifies byte position to start the searchWhole 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

  CommentType NumberDouble DecimalsWhole number (like 1, 132, 2344) No_commasA 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

  CommentType Half-width charactersText

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

  CommentType Source stringText Number of charactersWhole 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

  CommentType Source stringText Byte LengthWhole 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

  CommentType StringText

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

  CommentType StringText

#### LOWER

The LOWER() function converts a string to lower case.

Return type Text

Syntax

  LOWER(text)

Parameters

  CommentType Source stringText

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

  CommentType Source stringText PositionWhole number (like 1, 132, 2344) LengthWhole 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

  CommentType Source stringText Byte PositionWhole number (like 1, 132, 2344) Byte LengthWhole 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

  CommentType StringText

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

  CommentType Searched textText Regular expressionText Default value (optional)Text Back-reference (optional)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

  CommentType Searched textText Regular expressionText ReplacementText

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

  CommentType Text which you want to replace some charactersText Position of the characters to replaceWhole number (like 1, 132, 2344) Number of characters to replaceWhole number (like 1, 132, 2344) The text that will replace characters in old textText

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

  CommentType Text which you want to replace some characters using byte positionText Byte position of the characters to replaceWhole number (like 1, 132, 2344) The byte length of characters to replaceWhole number (like 1, 132, 2344) The text that will replace characters in old textText

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

  CommentType Source stringText Count of repetitionsWhole 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

  CommentType Source stringText Number of charactersWhole 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

  CommentType Source stringText Byte LengthWhole 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

  CommentType TextText

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

  CommentType The text you want to findText The text which may contain find_textText Specified index to start the searchWhole 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

  CommentType The text you want to findText The text which may contain find_textText Specified byte position to start the searchWhole 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

  CommentType Source stringText

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

  CommentType Text for which you want to substituteText Part of text you want to replaceText New text which will be replacementText Which occurrence to replaceWhole 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

  CommentType ValueAny 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

  CommentType ValueAny 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

  CommentType Source stringText

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

  CommentType StringText

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

  CommentType Character codeWhole 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

  CommentType TextText

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

  CommentType Source stringText

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

  CommentType TextText

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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)A floating point value (like 1.3, 0.343, 253 ) Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (degrees)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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

  CommentType Angle (radians)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