Issue: the issue date of the security.
First interest: the first interest date of the security.
Settlement: the date at which the interest accrued up until then is to be calculated.
Rate: the annual nominal rate of interest (coupon interest rate)
Par: the par value of the security.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis - Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of day in year.
2- Exact number of days in month, year has 360 days
3- Exact number of days in month, year has 365 days
4- European method, 12 months of 30 days each
Calculates the accrued interest of a security in the case of one-off payment at the settlement date.
Syntax
ACCRINTM(Issue;Settlement;Rate;Par;Basis)
Syntax Description
Issue: the issue date of the security.
Settlement: the maturity date.
Rate: the annual nominal rate of interest (coupon interest rate).
Par: the par value of the security.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis - Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Returns a cell address (reference) as text, according to the specified row and column numbers. You can determine whether the address is interpreted as an absolute address (for example, $A$1) or as a relative address (as A1) or in a mixed form (A$1 or $A1). You can also specify the name of the sheet.
Syntax
ADDRESS(row; column; abs;sheet)
Syntax Description
row: represents the row number for the cell reference
column: represents the column number for the cell reference (the number, not the letter)
abs: determines the type of reference:
1 - absolute ($A$1)
2 - row reference type is absolute; column reference is relative (A$1)
3 - row (relative); column (absolute) ($A1)
4 - relative (A1)
sheet: represents the name of the sheet. It must be placed in double quotes.
Calculates the amount of depreciation for a settlement period as degressive amortization. Unlike AMORLINC, a depreciation coefficient that is independent of the depreciable life is used here.
Cost: the acquisition costs.
Date purchased: the date of acquisition.
First period: the end date of the first settlement period.
Salvage: The salvage value of the capital asset at the end of the depreciable life.
Period: the settlement period to be considered.
Rate: the rate of depreciation.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis - Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns the FALSE value.The arguments are either logical expressions themselves (TRUE, 1=5, 2+3=7, B8=10) that return logical values, or arrays (A1:C3) containing logical values.When a function expects a single value, but you entered a cell range, then the value from the cell range is taken that is in the same column or row as the formula.If the entered range is outside of the current column or row of the formula, the function returns the error value #VALUE!
Syntax
AND(Logical value 1; Logical value 2 ...Logical value 30)
Syntax Description
Logical value 1; Logical value 2 ...Logical value 30 are conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row. The result is TRUE if the logical value in all cells within the cell range is TRUE.
Returns the probability of a sample with binomial distribution.
Syntax
B(trials;SP;T_1;T_2)
Syntax Description
Trials: is the number of independent trials.
SP: is the probability of success on each trial.
T_1: defines the lower limit for the number of trials.
T_2: (optional) defines the upper limit for the number of trials.
Converts a positive integer to a specified base into a text from the numbering system. The digits 0-9 and the letters A-Z are used.
Syntax
BASE(Number; Radix; [Minimum length])
Syntax Description
number: is the positive integer to be converted.
radix: indicates the base of the number system. It may be any positive integer between 2 and 36.
Minimum: length (optional) determines the minimum length of the character sequence that has been created. If the text is shorter than the indicated minimum length, zeros are added to the left of the string.
Returns the cumulative beta probability density function.
Syntax
BETADIST(Number;Alpha;Beta;Start;End)
Syntax Description
Number: is the value between Start and End at which to evaluate the function.
Alpha: is a parameter to the distribution.Beta is a parameter to the distribution.
Start: (optional) is the lower bound for number.
End: (optional) is the upper bound for number.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Returns the inverse of the cumulative beta probability density function.
Syntax
BETAINV(Number;Alpha;Beta;Start;End)
Syntax Description
Number: is the value between Start and End at which to evaluate the function.
Alpha: is a parameter to the distribution.Beta is a parameter to the distribution.
Start: (optional) is the lower bound for number.
End: (optional) is the upper bound for number.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
The result is the hexadecimal number for the binary number entered.
Syntax
BIN2HEX(Number;Places)
Syntax Description
Number: the binary number. The number can have a maximum of 10 places (bits). The most significant bit is the sign bit. Negative numbers are entered as two's complement.
Places: the number of places to be output.
The result is the octal number for the binary number entered.
Syntax
BIN2OCT(Number;Places)
Syntax Description
Number: the binary number. The number can have a maximum of 10 places (bits). The most significant bit is the sign bit. Negative numbers are entered as two's complement.
Places: the number of places to be output.
Returns the individual term binomial distribution probability.
Syntax
BINOMDIST(X;trials;SP;C)
Syntax Description
X: is the number of successes in a set of trials.
Trials: is the number of independent trials.
SP: is the probability of success on each trial.
C = 0 calculates the probability of a single event and C = 1 calculates the cumulative probability.
Rounds a number to the nearest integer or multiple of significance.
Syntax
CEILING(number; increment; mode)
Syntax Description
Number: is the number that is to be rounded up.
Increment: is the number to whose multiple the value is to be rounded up.
Mode: is an optional value. If it is indicated and is not equal to zero and if the number and increment are negative, rounding up is carried out based on that value.
Returns information on address, formatting or contents of a cell.
Syntax
CELL(Info_type; Reference)
Syntax Description
Info_type is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional.
Info_type: Meaning,
COL - Returns the number of the referenced column.Cell("COL";D2) returns 4.
ROW - Returns the number of the referenced row.Cell("ROW";D2) returns 2.
SHEET - Returns the number of the referenced sheet.Cell("Sheet";Sheet3.D2) returns 3.
ADDRESS - Returns the absolute address of the referenced cell.CELL("ADDRESS";D2) returns $D$2.CELL("ADDRESS";Sheet3.D2) returns $Sheet3.$D$2.
COORD - Returns the complete cell address in Lotus(TM) notation.CELL("COORD"; D2) returns $A:$D$2.CELL("COORD"; Sheet3.D2) returns $C:$D$2.CONTENTSReturns the contents of the referenced cell, without any formatting.
TYPE - Returns the type of cell contents.b = blank. empty celll = label. Text, result of a formula as textv = value. Value, result of a formula as a number.
WIDTH - Returns the width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size.
PREFIX - Returns the alignment of the referenced cell.' = align left or left-justified" = align right^ = centered\ = repeating (currently inactive)
PROTECT - Returns the status of the cell protection for the cell.1 = cell is protected0 = cell is not protected.
FORMAT - Returns a character string that indicates the number format., = number with thousands separatorF = number without thousands separatorC = currency formatS = exponential representation, for example, 1.234+E56P = percentageIn the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format #,##0.0 returns ,1 and the number format 00.000% returns P3D1 = MMM-D-YY, MM-D-YY and similar formatsD2 = DD-MMD3 = MM-YYD4 = DD-MM-YYYY HH:MM:SSD5 = MM-DDD6 = HH:MM:SS AM/PMD7 = HH:MM AM/PMD8 = HH:MM:SSD9 = HH:MMG = All other formats- (Minus) at the end = negative numbers are formatted in color() (brackets) at the end = there is an opening bracket in the format code
COLOR - Returns 1, if negative values have been formatted in color, otherwise 0.
PARENTHESES - Returns 1 if the format code contains an opening bracket (, otherwise 0.Reference (list of options) is the position of the cell to be examined.
Reference: If Reference is a range, the cell moves to the top left of the range. If Reference is missing, it uses the position of the cell in which this formula is located. Microsoft Excel uses the reference of the cell in which the cursor is positioned.
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.The probability determined by CHIDIST can also be determined by CHITEST.
Syntax
CHIDIST (Number; degrees_freedom)
Syntax Description
Number: the chi-square value of the random sample used to determine the error probability.
Degrees_freedom: the degrees of freedom of the experiment.
Returns the probability of a deviance from a random distribution of two test series based on the chi-squared test for independence. CHITEST returns the chi-squared distribution of the data.The probability determined by CHITEST can also be determined with CHIDIST, in which case the Chi square of the random sample must then be passed as a parameter instead of the data row.
Syntax
CHITEST(Data_B; Data_E)
Syntax Description
Data_B: the array of the observations.
Data_E: the range of the expected values.
Uses an index to return a value from a list of up to 30 values.
Syntax
CHOOSE(Index; value1;...value30)
Syntax Description
Index is a reference or number between 1 and 30 indicating which value is to be taken from the list.
Value1...Value30 is the list of values entered as a reference to a cell or as individual values.
Returns the column number of a cell reference. If the reference is a cell the column number of the cell is returned; if the parameter is a cell area, the corresponding column numbers are returned in a single-row array if the formula is entered as an array formula. If the COLUMN function with an area reference parameter is not used for an array formula, only the column number of the first cell within the area is determined.
Syntax
COLUMN(reference)
Syntax Description
Reference: the reference to a cell or cell area whose first column number is to be found.If no reference is entered, the column number of the cell in which the formula is entered is found. It automatically sets the reference to the current cell.
Converts a value from one unit of measure to the corresponding value in another unit of measure. Enter the units of measures directly as text in quotation marks or as a reference. If you enter the units of measure in cells, they must correspond exactly with the following list which is case sensitive: For example, in order to enter a lower case l (for liter) in a cell, enter the apostrophe ' immediately followed by l.
PropertyUnits
Weight - g, sg, lbm, u, ozm, stone, ton, grain, pweight, hweight, shweight
Length - m, mi, Nmi, in, ft, yd, ang, Pica, ell, parsec
Time - yr, day, hr, mn, sec
Pressure - Pa, atm, mmHg, Torr, psi
Force - N, dyn, pond
Energy - J, e, c, cal, eV, HPh, Wh, BTU
Power - W, HP, PS
Field strength - T, ga
Temperature - C, F, K, Reau, Rank
Volume - l, tsp, tbs, oz, cup, pt, qt, gal, m3, mi3, Nmi3, in3, ft3, yd3, ang3, Pica3, barrel, bushel, regton, Schooner, Middy, Glass
Area - m2, mi2, Nmi2, in2, ft2, yd2, ang2, Pica2, Morgen, ar, acre, ha
Speed - m/s, m/h, mph, kn, admkn
Each unit of measure must be preceded by a prefix character from the following list:
Permitted characters - prefix
10^(>0) - d, c, m, u, n, p, f, a, z, y
10^(>0) - e, h, k, M, G, T, P, E, Z, Y
The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Syntax
CONVERT_ADD(Number;From unit;To unit)
Syntax Description
Number: the number to be converted.
From unit: the unit from which conversion is taking place.
To unit: the unit to which conversion is taking place.
Counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0. If an argument is an array or reference, empty cells within the array or reference are ignored.
Syntax
COUNTA(value1; value2; ... value30)
Syntax Description
value1; value2, ... are 1 to 30 arguments representing the values to be counted.
Returns the number of elements that meet with certain criteria within a cell range.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.
Syntax
COUNTIF(range; criteria)
Syntax Description
Range: the range to which the criteria are to be applied.
Criteria: indicates the criteria in the form of a number, an expression or a character string. These criteria determine which cells are counted. You may also enter a search text in the form of a regular expression, e.g. "b.*" for all words that begin with b. You may also indicate a cell range that contains the search criterion. If you search for literal text, enclose the text in double quotes.
Returns the number of days from the first day of interest payment on a security until the settlement date.
Syntax
COUPDAYBS (Settlement;Maturity;Frequency;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis - Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Returns the number of days in the current interest period in which the settlement date falls.
Syntax
COUPDAYS(Settlement;Maturity;Frequency;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis - Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Returns the number of days from the settlement date until the next interest date.
Syntax
COUPDAYSNC (Settlement;Maturity;Frequency;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis - Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Returns the date of the first interest date after the settlement date. Format the result as a date.
Syntax
COUPNCD (Settlement;Maturity;Frequency;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Returns the number of coupons (interest payments) between the settlement date and the maturity date.
Syntax
COUPNUM (Settlement;Maturity;Frequency;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Returns the date of the interest date prior to the settlement date. Format the result as a date.
Syntax
COUPPCD(Settlement;Maturity;Frequency;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Calculates the cumulative interest payments, that is, the total interest, for an investment based on a constant interest rate.
Syntax
CUMIPMT(Rate;NPER;pv;S;E;Type)
Syntax Description
Rate: the periodic interest rate.
NPER: the payment period with the total number of periods.
NPER can also be a non-integer value.
pv: the current value in the sequence of payments.
S: the first period.E: the last period.
Type: the due date of the payment at the beginning or end of each period.
Calculates the accumulated interest for a period.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Rate: the interest rate for each period.
NPER: the total number of payment periods. The rate and NPER must refer to the same unit, and thus both be calculated annually or monthly.
Pv: the current value.
Start period: the first payment period for the calculation.
End period: the last payment period for the calculation.
Type: the maturity of a payment at the end of each period (Type = 0) or at the start of the period (Type = 1).
Returns the cumulative interest paid for an investment period with a constant interest rate.
Syntax
CUMPRINC(Rate;NPER;PV;S;E;Type)
Syntax Description
Rate: the periodic interest rate.
NPER: the payment period with the total number of periods.
NPER can also be a non-integer value.
PV: the current value in the sequence of payments.
S: the first period.
E: the last period.
Type: the due date of the payment at the beginning or end of each period.
Calculates the cumulative redemption of a loan in a period.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Rate: the interest rate for each period.
NPER: the total number of payment periods. The rate and NPER must refer to the same unit, and thus both be calculated annually or monthly.
PV: the current value.
Start period: the first payment period for the calculation.
End period: the last payment period for the calculation.
Type: the maturity of a payment at the end of each period (Type = 0) or at the start of the period (Type = 1).
This function converts a date written as year, month, day to an internal serial number and displays it in the cell's formatting. The default format of a cell containing the DATE function is the date format, but you can format the cells with the 0 number format, which displays the internal serial number of the date as a number.
Syntax
DATE(year; month; day)
Syntax Description
Year: an integer between 1583 and 9956 or 0 and 99.
Month: an integer between 1 and 12 indicating the month.
Day: a number between 1 and 31 indicating the day of the month.
If the values for month and day are higher, they are carried over to the next digit. If you enter =DATE(00;12;31) the result will be "12/31/00." If, on the other hand, you enter =DATE(00;13;31) the result will be "1/31/01."
Returns the internal date number for text in quotes.The internal date number is returned as a number. The number is determined by the date system that is internally configured to calculate dates.
Syntax
DATEVALUE("Text")
Syntax Description
Text: a valid date expression and must be entered with quotation marks.
Returns the difference between two dates based on the 360 day year used in interest calculations.
Syntax
DAYS360(Date_1;Date_2;Type)
Syntax Description
If Date_2 is earlier than Date_1, the function will return a negative number.The optional argument Type determines the type of difference calculation.<>
If Type = 0 or if the argument is missing, the US method (NASD, National Association of Securities Dealers) is used. If Type 0, the European method is used.
Returns the depreciation of an asset for a specified period using the arithmetic-declining method.Use this form of depreciation if you require a higher initial depreciation value as opposed to linear depreciation. The depreciation value gets less with each period and is usually used for assets whose value loss is higher shortly after purchase (for example, vehicles, computers). Please note that the book value will never reach zero under this calculation type.
Syntax
DDB(Cost;Salvage;Life;Period;Factor)
Syntax Description
Cost: fixes the initial cost of an asset.
Salvage: fixes the value of an asset at the end of its life.
Life: the number of periods defining how long the asset is to be used.
Period: defines the length of the period. The length must be entered in the same time unit as life.
Factor: (optional) is the factor by which depreciation decreases. If a value is not entered, the default is factor 2.
The result is the binary number for the decimal number entered between -512 and 511.
Syntax
DEC2BIN(Number;Places)
Syntax Description
Number: the decimal number. If Number is negative, the function returns a binary number with 10 characters. The most significant bit is the sign bit, the other 9 bits return the value.
Places: the number of places to be output.
The result is the hexadecimal number for the decimal number entered.
Syntax
DEC2HEX(Number;Places)
Syntax Description
Number: the decimal number. If Number is negative, the function returns a hexadecimal number with 10 characters (40 bits). The most significant bit is the sign bit, the other 39 bits return the value.
Places: the number of places to be output.
The result is the octal number for the decimal number entered.
Syntax
DEC2OCT(Number; Places)
Syntax Description
Number: the decimal number. If Number is negative, the function returns an octal number with 10 characters (30 bits). The most significant bit is the sign bit, the other 29 bits return the value.
Places: the number of places to be output.
Converts text with characters from a number system to a positive integer in the base radix given. The radix must be in the range 2 to 36. Spaces and tabs are ignored. The text field is not case-sensitive.If the radix is 16, a leading x or X or 0x or 0X, and an appended h or H, is disregarded. If the radix is 2, an appended b or B is disregarded. Other characters that do not belong to the number system generate an error.
Syntax
DECIMAL(Text; Radix)
Syntax Description
text: the text to be converted. To differentiate between a hexadecimal number, such as A1 and the reference to cell A1, you must place the number in quotation marks, for example, "A1" or "FACE".
radix: indicates the base of the number system. It may be any positive integer between 2 and 36.
Calculates the allowance (discount) of a security as a percentage.
Syntax
DISC(Settlement;Maturity;Price;Redemption;Basis)
Syntax Description
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Price: The price of the security per 100 currency units of par value.
Redemption: the redemption value of the security per 100 currency units of par value.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Converts a number to an amount in the currency format, rounded to a specified decimal place. In the value field enter the number to be converted to currency. Optionally, you may enter the number of decimal places in the decimals field. If no value is specified, all numbers in currency format will be displayed with two decimal places.You set the currency format in your system settings.
Syntax
DOLLAR(value; decimals)
Syntax Description
value: a number, a reference to a cell containing a number, or a formula which returns a number.
decimals: the optional number of decimal places.
Calculates the number of periods required by an investment to attain the desired value.
Syntax
DURATION(Rate;PV;FV)
Syntax Description
Rate: a constant. The interest rate is to be calculated for the entire duration (duration period). The interest rate per period is calculated by dividing the interest rate by the calculated duration. The internal rate for an annuity is to be entered as Rate/12.<0.FV: the expected value. The future value determines the desired (future) value of the deposit.
PV: the present (current) value. The cash value is the deposit of cash or the current cash value of an allowance in kind. As a deposit value a positive value must be entered; the deposit must not be 0 or <0.
FV: the expected value. The future value determines the desired (future) value of the deposit.
Calculates the duration of a fixed interest security in years.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Coupon: the annual coupon interest rate (nominal rate of interest)
Yield: the annual yield of the security.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Returns the net annual interest rate for a nominal interest rate.Nominal interest refers to the amount of interest due at the end of a calculation period. Effective interest increases with the number of payments made. In other words, interest is often paid in installments (for example, monthly or quarterly) before the end of the calculation period.
Syntax
EFFECTIVE(NOM;P)
Syntax Description
NOM: the nominal interest.
P: the number of interest payment periods per year.
Calculates the effective annual rate of interest on the basis of the nominal interest rate and the number of interest payments per annum.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Syntax
EFFECT_ADD(Nominal rate;Npery)
Syntax Description
Nominal rate: the annual nominal rate of interest.
Npery: the number of interest payments per year.
Lower limit: lower limit of integral.
Upper limit: optionally, the upper limit of the integral.
If this value is missing, the calculation takes places between 0 and the lower limit.
Returns the number corresponding to an error value occurring in a different cell. With the aid of this number, you can generate an error message text.If an error occurs, the function returns a logical or numerical value.
Syntax
ERRORTYPE(Reference)
Syntax Description
Reference: the address of the cell in which the error occurs.
Number: the value of the function.
Lambda: the parameter value.
C: a logical value that determines the form of the function. C = 0 calculates the density function, and C = 1 calculates the distribution.
The result is the factorial of the number with increments of 2.
Syntax
FACTDOUBLE(Number)
Syntax Description
Number: if the number is even, the following factorial is calculated: n*(N-2)*(n-4)*...*4*2.If the number is uneven, the following factorial is calculated: n*(N-2)*(n-4)*...*3*1.
Number: the value for which the F distribution is to be calculated.
degrees_freedom_1: is the degrees of freedom in the numerator in the F distribution.
degrees_freedom_2: is the degrees of freedom in the denominator in the F distribution.
Looks for a string of text within another string. You can also define where to begin the search. The search term can be a number or any string of characters. The search is case-sensitive.
Syntax
FIND(find_text; text; position)
Syntax Description
find_text: refers to the text to be found.
text: the text where the search takes place.
position: (optional) the position in the text from which the search starts.
Returns the inverse of the F probability distribution. The F distribution is used for F tests in order to set the relation between two differing data sets.
Number: probability value for which the inverse F distribution is to be calculated.
degrees_freedom_1: the number of degrees of freedom in the numerator of the F distribution.
degrees_freedom_2: the number of degrees of freedom in the denominator of the F distribution.
Specifies that a number be displayed with a fixed number of decimal places and with or without a thousands separator. This function can be used to apply a uniform format to a column of numbers.
Syntax
FIXED(Number; decimals; no thousands separators)
Syntax Description
Number: the number to be formatted.
Decimals: the number of decimal places to be displayed.
No thousands separators: (optional) determines whether the thousands separator is used. If the parameter is a number not equal to 0, the thousands separator is suppressed. If the parameter is equal to 0 or if it is missing altogether, the thousands separators of your current locale setting are displayed.
Rounds a number down to the nearest multiple of significance.
Syntax
FLOOR(Number; Significance; Mode)
Syntax Description
Number: the number that is to be rounded down.
Significance: the value to whose multiple the number is to be rounded down.
Mode: (optional) If it is indicated and is not equal to zero, if the number and increment are negative, rounding up is carried out based on that value.
Extrapolates future values based on existing x and y values.
Syntax
FORECAST(Value; data_Y; data_X)
Syntax Description
Value: the x value, for which the y value on the linear regression is to be returned.
Data_Y: the array or range of known y's.
Data_X: is the array or range of known x's.
Displays the formula of a formula cell at any position. The formula will be returned as a string in the Reference position. If no formula cell can be found,or if the presented argument is not a reference, the error value #N/A is set.
Returns the future value of an investment based on periodic, constant payments and a constant interest rate (Future Value).
Syntax
FV(Rate; NPER; PMT; PV; Type)
Syntax Description
Rate: the periodic interest rate.
NPER: the total number of periods (payment period).
PMT: the annuity paid regularly per period.
PV (optional): the (present) cash value of an investment.
Type (optional): defines whether the payment is due at the beginning or the end of a period.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Number: the value for which the Gamma distribution is to be calculated.
Alpha: the parameter Alpha of the Gamma distribution.
Beta: the parameter Beta of the Gamma distribution
C = 0 calculates the density function C = 1 the distribution.
Returns the inverse of the Gamma cumulative distribution. This function allows you to search for variables with different distribution.
Syntax
GAMMAINV(Number; Alpha; Beta)
Syntax Description
Number: the probability value for which the inverse Gamma distribution is to be calculated.
Alpha: the parameter Alpha of the Gamma distribution.
Beta: the parameter Beta of the Gamma distribution.
The result is the greatest common divisor of a list of numbers.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
The result is the binary number for the hexadecimal number entered.
Syntax
HEX2BIN(Number;Places)
Syntax Description
Number: the hexadecimal number. The number can have a maximum of 10 places. The most significant bit is the sign bit, the following bits return the value. Negative numbers are entered as two's complement.
Places: the number of places to be output.
The result is the decimal number for the hexadecimal number entered.
Syntax
HEX2DEC(Number)
Syntax Description
Number: the hexadecimal number. The number can have a maximum of 10 places. The most significant bit is the sign bit, the following bits return the value. Negative numbers are entered as two's complement.
The result is the octal number for the hexadecimal number entered.
Syntax
HEX2OCT(Number;Places)
Syntax Description
Number: the hexadecimal number. The number can have a maximum of 10 places. The most significant bit is the sign bit, the following bits return the value. Negative numbers are entered as two's complement.
Places: the number of places to be output.
Searches for a value and reference to the cells below the selected area. This function verifies if the first row of an array contains a certain value. The function returns then the value in a row of the array, named in the Index, in the same column.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.
X: the number of results achieved in the random sample.
N_sample: the size of the random sample.
Successes: the number of possible results in the total population.
N_population: the size of the total population.
Test: any value or expression that can be TRUE or FALSE.
Then_value: (optional) the value that is returned if the logical test is TRUE.
Otherwise_value: (optional) the value that is returned if the logical test is FALSE.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
INDEX returns the content of a cell, specified by row and column number or an optional range name.
Syntax
INDEX(reference;row;column;range)
Syntax Description
reference: a cell reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses.
row: (optional) represents the row number of the reference range, for which to return a value.
column: (optional) represents the column number of the reference range, for which to return a value.
range: (optional) represents the index of the subrange if referring to a multiple range.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Returns the reference specified by a text string. This function can also be used to return the area of a corresponding string.
Syntax
INDIRECT(ref)
Syntax Description
ref: represents a reference to a cell or an area (in text form) for which to return the contents.If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("filename!sheetname"&B1) is not converted into the Calc address in INDIRECT("filename.sheetname"&B1).
Calculates the point at which a line will intersect the y-values by using known x-values and y-values.
Syntax
INTERCEPT(data_Y; data_X)
Syntax Description
Data_Y: the dependent set of observations or data.
Data_X: the independent set of observations or data.
Names, arrays or references containing numbers must be used here. Numbers can also be entered directly.
Calculates the annual interest rate that results when a security (or other item) is purchased at an investment value and sold at a redemption value. No interest is paid.
Settlement: the date of purchase of the security.
Maturity: the date on which the security is sold.
Investment: the purchase price.
Redemption: the selling price.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each.
1 - Exact number of days in months, exact number of days in year.
2 - Exact number of days in month, year has 360 days.
3 - Exact number of days in month, year has 365 days.
4 - European method, 12 months of 30 days each.
Calculates the periodic amortizement for an investment with regular payments and a constant interest rate.
Syntax
IPMT(Rate;Period;NPER;PV;FV;Type)
Syntax Description
Rate is the periodic interest rate.
Period is the period, for which the compound interest is calculated. Period=NPER if compound interest for the last period is calculated.
NPER is the total number of periods, during which annuity is paid.
PV is the present cash value in sequence of payments.
FV (optional) is the desired value (future value) at the end of the periods.
Type is the due date for the periodic payments.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Calculates the internal rate of return for an investment. The values represent cash flow values at regular intervals, at least one value must be negative (payments), and at least one value must be positive (income).
Syntax
IRR(Values;Guess)
Syntax Description
Values: represents an array containing the values.
Guess: (optional) the estimated value. An iterative method is used to calculate the internal rate of return. If you can provide only few values, you should provide an initial guess to enable the iteration.
Returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty. A cell with a formula inside is not empty.If an error occurs, the function returns a logical or numerical value.
Returns TRUE if the value refers to any error value except #N/A. You can use this function to control error values in certain cells.If an error occurs, the function returns a logical or numerical value.
Syntax
ISERR(value)
Syntax Description
Value: any value or expression in which a test is performed to determine whether an error value not equal to #N/A is present.
The ISERROR tests if the cells contain general error values. ISERROR recognizes the #N/A error value.If an error occurs, the function returns a logical or numerical value.
Syntax
ISERROR(value)
Syntax Description
Value: any value where a test is performed to determine whether it is an error value.
Tests for even numbers. Returns TRUE (1) if the number returns a whole number when divided by 2.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Returns TRUE if the cell contains a logical number format. The function is used in order to check for both TRUE and FALSE values in certain cells.If an error occurs, the function returns a logical or numerical value.
Syntax
ISLOGICAL(value)
Syntax Description
Value: the value to be tested for logical number format.
Tests if the cell contents are text or numbers, and returns FALSE if the contents are text.If an error occurs, the function returns a logical or numerical value.
Syntax
ISNONTEXT(value)
Syntax Description
Value: any value or expression where a test is performed to determine whether it is a text or numbers or a Boolean value.
Returns TRUE (1) if the number does not return a whole number when divided by 2.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Calculates the level of interest for unchanged amortization installments.
Syntax
ISPMT(Rate; Period; Total_periods; Invest)
Syntax Description
Rate: sets the periodic interest rate.
Period: the number of installments for calculation of interest.
Total_periods: the total number of installment periods.
Invest: the amount of the investment.
Tests if the content of one or several cells is a reference. Verifies the type of references in a cell or a range of cells.If an error occurs, the function returns a logical or numerical value.
Syntax
ISREF(value)
Syntax Description
Value: the value to be tested, to determine whether it is a reference.
The result is the lowest common multiple of a list of numbers.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Returns the first character or characters in a text string.
Syntax
LEFT(text; number)
Syntax Description
text: the text where the initial partial words are to be determined.
Number: (optional) specifies the number of characters for the start text.
If this parameter is not defined, one character is returned.
Returns the inverse of the lognormal distribution.
Syntax
LOGINV(Number; Mean; STDEV)
Syntax Description
Number: the probability value for which the inverse standard logarithmic distribution is to be calculated.
Mean: the arithmetic mean of the standard logarithmic distribution.
STDEV: the standard deviation of the standard logarithmic distribution.
Number: the probability value for which the standard logarithmic distribution is to be calculated.
Mean: the mean value of the standard logarithmic distribution.
STDEV: the standard deviation of the standard logarithmic distribution.
Returns the contents of a cell either from a one-row or one-column range or from an array. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted, otherwise the search will not return any usable results.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.
Search criterion: the value to be searched for; entered either directly or as a reference.
Search vector: the single-row or single-column area to be searched.
result_vector: another single-row or single-column range from which the result of the function is taken. The result is the cell of the result vector with the same index as the instance found in the search vector.
Returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.
Syntax
MATCH(search criterion;lookup_array;type)
Syntax Description
Search Criterion: the value which is to be searched for in the single-row or single-column array.
lookup_array: the reference searched. A lookup array can be a single row or column, or part of a single row or column.
Type: takes the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the first one found. Only if Type = 0 can you search for regular expressions.If Type = 1 or the third parameter is missing, the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Coupon: the annual nominal rate of interest (coupon interest rate)
Yield: the annual yield of the security.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Returns the median of a set of numbers. In a set containing an uneven number of values, the median will be the number in the middle of the set and in a set containing an even number of values, it will be the mean of the two values in the middle of the set.
Syntax
MEDIAN(Number 1; Number 2; ...Number 30)
Syntax Description
Number 1; Number 2;...Number 30 are values or ranges, which represent a sample. Each number can also be replaced by a reference.
Returns a text segment of a character string. The parameters specify the starting position and the number of characters.
Syntax
MID(text; start; number)
Syntax Description
text: the text containing the characters to extract.
start: the position of the first character in the text to extract.
number: specifies the number of characters in the part of the text.
Calculates the modified internal rate of return of a series of investments.
Syntax
MIRR(Values; investment; reinvest_rate)
Syntax Description
Values: corresponds to the array or the cell reference for cells whose content corresponds to the payments.
investment: the rate of interest of the investments (the negative values of the array)
reinvest_rate: the rate of interest of the reinvestment (the positive values of the array)
Returns the most common value in a data set. If there are several values with the same frequency, it returns the smallest value. An error occurs when a value doesn't appear twice.
Syntax
MODE(Number 1; Number 2; ...Number 30)
Syntax Description
Number 1; Number 2;...Number 30 are numerical values or ranges.
Returns the number 1, if the parameter is TRUE. Returns the parameter, if the parameter is a number. Returns the number 0 for other parameters.If an error occurs, the function returns a logical or numerical value.
Syntax
N(value)
Syntax Description
Value: the parameter to be converted into a number.
X: represents the value returned for unsuccessful tests.
R: represents the value returned for successful tests.
SP: is the probability of the success of an attempt.
X: represents the value returned for unsuccessful tests.
R: represents the value returned for successful tests.
SP: is the probability of the success of an attempt.
Returns the number of workdays between Start date and End date. Holidays can be deducted.
Syntax
NETWORKDAYS (Start date;End date;Holidays)
Syntax Description
Start date: the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation.
End date: the date up until when the calculation is carried out. If the end date is a workday, the day is included in the calculation.
Holidays: optional list of holidays. These are non-working days. Enter a cell range in which the holidays are listed individually.
Calculates the annual nominal rate of interest on the basis of the effective rate and the number of interest payments per annum.The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel.
Syntax
NOMINAL_ADD(Effective rate;Npery)
Syntax Description
Effective rate: the effective annual rate of interest.
Npery: the number of interest payments per year.
Returns the standard normal cumulative distribution.
Syntax
NORMDIST(Number; Mean; STDEV; C)
Syntax Description
Number: the value of the distribution based on which the normal distribution is to be calculated.
Mean: the mean value of the distribution.
STDEV: the standard deviation of the distribution.
C = 0 calculates the density function; C = 1 calculates the distribution.
Returns the inverse of the normal cumulative distribution.
Syntax
NORMINV(Number; Mean; STDEV)
Syntax Description
Number: represents the probability value used to determine the inverse normal distribution.
Mean: represents the mean value in the normal distribution.
STDEV: represents the standard deviation of the normal distribution.
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Syntax
NPER(Rate;PMT;PV;FV;Type)
Syntax Description
Rate: the periodic interest rate.
PMT: the constant annuity paid in each period.
PV: the present value (cash value) in a sequence of payments.
FV (optional): the future value, which is reached at the end of the last period.
Type (optional): the due date of the payment at the beginning or at the end of the period.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
The result is the binary number for the octal number entered.
Syntax
OCT2BIN(Number;Places)
Syntax Description
Number: the octal number. The number can have a maximum of 10 places. The most significant bit is the sign bit, the following bits return the value. Negative numbers are entered as two's complement.
Places: the number of places to be output.
The result is the decimal number for the octal number entered.
Syntax
OCT2DEC(Number)
Syntax Description
Number: the octal number. The number can have a maximum of 10 places. The most significant bit is the sign bit, the following bits return the value. Negative numbers are entered as two's complement.
The result is the hexadecimal number for the octal number entered.
Syntax
OCT2HEX(Number;Places)
Syntax Description
Number: the octal number. The number can have a maximum of 10 places. The most significant bit is the sign bit, the following bits return the value. Negative numbers are entered as two's complement.
Places: the number of places to be output.x: the value on which the function will be calculated.n: the order of the Bessel function
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Issue: the date of issue of the security.
First coupon: the first interest date of the security.
Rate: the annual rate of interest.
Yield: the annual yield of the security.
Redemption: the redemption value per 100 currency units of par value.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Issue: the date of issue of the security.
First coupon: the first interest date of the security.
Rate: the annual rate of interest.
Yield: the annual yield of the security.
Redemption: the redemption value per 100 currency units of par value.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Last interest: the last interest date of the security.
Rate: the annual rate of interest.
Yield: the annual yield of the security.
Redemption: the redemption value per 100 currency units of par value.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Last interest: the last interest date of the security.
Rate: the annual rate of interest.
Yield: the annual yield of the security.
Redemption: the redemption value per 100 currency units of par value.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Returns the value of a cell offset by a certain number of rows and columns from a given reference point.
Syntax
OFFSET(reference;rows;columns;height;width)
Syntax Description
Reference: the cell from which the function searches for the new reference.
Rows: the number of cells by which the reference was corrected up (negative value) or down.
Columns: the number of columns by which the reference was corrected to the left (negative value) or to the right.
Height: the optional vertical height for an area that starts at the new reference position.
Width: the optional horizontal width for an area that starts at the new reference position.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Returns TRUE if at least one argument is TRUE. This function returns the value FALSE, if all the arguments have the logical value FALSE.The arguments are either logical expressions themselves (TRUE, 1<5, 2+3=7, B8<10) that return logical values, or arrays (A1:C3) containing logical values.When a function expects a single value, but you entered a cell range, then the value from the cell range is taken that is in the same column or row as the formula.If the entered range is outside of the current column or row of the formula, the function returns the error value #VALUE!
Syntax
OR(Logical value 1; Logical value 2 ...Logical value 30)
Syntax Description
Logical value 1; Logical value 2 ...Logical value 30 are conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row.
Returns the alpha-percentile of data values in an array. 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.
Syntax
PERCENTILE(Data;Alpha)
Syntax Description
Data: represents the array of data.
Alpha :represents the percentage of the scale between 0 and 1.
Returns the periodic payment for an annuity with constant interest rates.
Syntax
PMT(Rate; NPER; PV; FV; Type)
Syntax Description
Rate: the periodic interest rate.
NPER: the number of periods in which annuity is paid.
PV: the present value (cash value) in a sequence of payments.
FV (optional): the desired value (future value) to be reached at the end of the periodic payments.
Type (optional): the due date for the periodic payments. Type=1 is payment at the beginning and Type=0 is payment at the end of each period.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Number: represents the value based on which the Poisson distribution is calculated.
Mean: represents the middle value of the Poisson distribution.
C = 0 calculates the density function; C = 1 calculates the distribution.
Returns for a given period the payment on the principal for an investment that is based on periodic and constant payments and a constant interest rate.
Syntax
PPMT(Rate;Period;NPER;PV;FV;Type)
Syntax Description
Rate: the periodic interest rate.
Period: the amortizement period. P=1 for the first and P=NPER for the last period.
NPER: the total number of periods during which annuity is paid.
PV. the present value in the sequence of payments.
FV (optional): the desired (future) value.
Type (optional): defines the due date. F=1 for payment at the beginning of a period and F=0 for payment at the end of a period.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Rate: the annual nominal rate of interest (coupon interest rate)
Yield: the annual yield of the security.
Redemption: the redemption value per 100 currency units of par value.
Frequency: number of interest payments per year (1, 2 or 4).
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Discount: the discount of a security as a percentage.
Redemption: the redemption value per 100 currency units of par value.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Issue: the date of issue of the security.
Rate: the interest rate of the security on the issue date.
Yield: the annual yield of the security.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Returns the probability that values in a range are between two limits. If there is no End value, this function calculates the probability based on the principle that the Data values are equal to the value of Start.
Syntax
PROB(Data; Probability: Start; End)
Syntax Description
Data: the array or range of data in the sample.
Probability: the array or range of the corresponding probabilities.
Start: the start value of the interval whose probabilities are to be summed.
End (optional): the end value of the interval whose probabilities are to be summed.
If this parameter is missing, the probability for the Start value is calculated.
Returns the present value of an investment resulting from a series of regular payments.Use this function to calculate the amount of money needed to be invested at a fixed rate today, to receive a specific amount, an annuity, over a specified number of periods. You can also determine how much money is to remain after the elapse of the period. Specify as well if the amount is to be paid out at the beginning or at the end of each period.Enter these values either as numbers, expressions or references. If, for example, interest is paid annually at 8%, but you want to use month as your period, enter 8%/12 under Rate and ti will automatically calculate the correct factor.
Syntax
PV(Rate; NPER; PMT; FV; Type)
Syntax Description
Rate: defines the interest rate per period.
NPER: the total number of periods (payment period).
PMT: the regular payment made per period.
FV (optional): defines the future value remaining after the final installment has been made.
Type (optional): denotes due date for payments. Type = 1 means due at the beginning of a period and Type = 0 (default) means due at the end of the period.
In these functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Value: the value, whose rank is to be determined
Data: the array or range of data in the sample.
Type (optional): the sequence order. = 0 means ascending, = 1 means descending.
Returns the constant interest rate per period of an annuity.
Syntax
RATE(NPER;PMT;PV;FV;Type;GUESS)
Syntax Description
NPER: the total number of periods, during which payments are made (payment period).
PMT: the constant payment (annuity) paid during each period.
PV: the cash value in the sequence of payments.
FV (optional): the future value, which is reached at the end of the periodic payments.
Type (optional): the due date of the periodic payment, either at the beginning or at the end of a period.
GUESS (optional): determines the estimated value of the interest with iterative calculation.
In thesefunctions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Settlement: the date of purchase of the security.
Maturity: the date on which the security matures (expires).
Investment: the purchase sum.
Discount: the percentage discount on acquisition of the security.
Basis: is chosen from a list of options and indicates how the year is to be calculated.
Basis Calculation
0 or missing - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
Replaces part of a text string with a different text string. This function can be used to replace both characters and numbers (which are automatically converted to text). The result of the function is always displayed as text. If you intend to perform further calculations with a number which has been replaced by text, you will need to convert it back to a number using the VALUE function.Any text containing numbers must be enclosed in quotation marks if you do not want it to be interpreted as a number and automatically converted to text.
Syntax
REPLACE(text; position; length; new text)
Syntax Description
Text: refers to text of which a part will be replaced.
position: refers to the position within the text where the replacement will begin.
length: the number of characters in text to be replaced.
new text: refers to the text which replaces text.
Converts a number into a Roman numeral. The value range must be between 0 and 3999, the modes can be integers from 0 to 4.
Syntax
ROMAN(Number; Mode)
Syntax Description
Number: the number that is to be converted into a Roman numeral.
Mode (optional): indicates the degree of simplification. The higher the value, the greater is the simplification of the Roman number.
Returns a number rounded to a certain number of decimal places according to valid mathematical criteria.
Syntax
ROUND(number; count)
Syntax Description
number: the number to be rounded. count (optional): the number of the places to which the value is to be rounded. If the count parameter is negative, numbers before the decimal are rounded.
number: the number to be rounded down. count (optional):the number of digits to be rounded down to. If the count parameter is negative, rounding is to the digits preceding the decimal point.
Returns the row number of a cell reference. If the reference is a cell, it returns the row number of the cell. If the reference is a cell range, it returns the corresponding row numbers in a one-column Array if the formula is entered as an array formula. If the ROW function with a range reference is not used in an array formula, only the row number of the first range cell will be returned.
Syntax
ROW(reference)
Syntax Description
Reference: it is a cell, an area, or the name of an area.If you do not indicate a reference, the row number of the cell in which the formula is entered will be found.
Calculates the interest rate resulting from the profit (return) of an investment.
Syntax
RRI(P;PV;FV)
Syntax Description
P: the number of periods needed for calculating the interest rate. PV: the present (current) value. The cash value is the deposit of cash or the current cash value of an allowance in kind. As a deposit value a positive value must be entered; the deposit must not be 0 or <0. FV: determines what is desired as the cash value of the deposit.
Returns the square of the Pearson correlation coefficient based on the given values. RSQ (also called determination coefficient) is a measure for the accuracy of an adjustment and can be used to produce a regression analysis.
Syntax
RSQ(Data_Y; Data_X)
Syntax Description
Data_Y: an array or range of data points. Data_X: an array or range of data points.
Returns the position of a text segment within a character string. You can set the start of the search as an option. The search text can be a number or any sequence of characters. The search is not case-sensitive.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.
Syntax
SEARCH(find_text; text; position)
Syntax Description
find_text: the text to be searched for. text: the text where the search will take place. position (optional): the position in the text where the search is to start.
Returns a sum of powers of the number x in accordance with the following formula:SERIESSUM(x;n;m;coefficients) = coefficient_1*x^n + coefficient_2*x^(n+m) + coefficient_3*x^(n+2m) +...+ coefficient_i*x^(n+(i-1)m)
Syntax
SERIESSUM(x; n; m; coefficients)
Syntax Description
x: the number as an independent variable n: the starting power m: the increment coefficients: a series of coefficients. For each coefficient the series sum is extended by one section.
Returns the sheet number of a reference or a string representing a sheet name. If you do not enter any parameters, the result is the sheet number of the spreadsheet containing the formula.
Syntax
SHEET(Reference)
Syntax Description
Reference: (optional) and is the reference to a cell, an area, or a sheet name string.
Returns the straight-line depreciation of an asset for one period.The amount of the depreciation is constant during the depreciation period.
Syntax
SLN(COST; SALVAGE; LIFE)
Syntax Description
COST: the initial cost of an asset. SALVAGE: the value of an asset at the end of the depreciation. LIFE: the depreciation period determining the number of periods in the depreciation of the asset.
Returns the stock details for the symbol provided.
Syntax
STOCK(Symbol; Function)
Syntax Description
symbol: represent the stock symbol of the company. function: represents the type of data you want, this is a optional parameter[Default value is price].
Example: =STOCK("GOOG","PRICE"), =STOCK("MSFT"), =STOCK("YHOO","VOLUME")
supported functions price - price of stock.
tradetime - last traded time of stock in PST.
dayopen - price when the current day open.
dayhigh - highest traded price for the current day.
daylow - lowest traded price for the current day.
volume - volume of shares traded for the day.
close - price at previous day close.
change - %change from previous day close.
52weekhigh - highest price in last 52 weeks.
52weeklow - lowest price in last 52 weeks.
Note:Stock details are provided from free stock sources available in the Web. The data may be delayed up to 30 minutes. Also the data provided is for informational purposes only.
SUBSTITUTE(text; search_text; new text; occurrence)
Syntax Description
text: the text in which text segments are to be exchanged. search_text: the text segment that is to be replaced (a number of times). new text: the text that is to replace the text segment. occurrence (optional): indicates how many occurrences of the search text are to be replaced. If this parameter is missing the search text is replaced throughout.
Calculates subtotals. If a range already contains subtotals, these are not used for further calculations. Use this function with the AutoFilters to take only the filtered records into account.
Syntax
SUBTOTAL(function; range)
Syntax Description
Function: a number that stands for one of the following functions:Function indexFunction1AVERAGE2COUNT3COUNTA4MAX5MIN6PRODUCT7STDEV8STDEVP9SUM10VAR11VARP Range: the range whose cells are included.
Adds the cells specified by a given criteria. This function is used to browse a range when you search for a certain value.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.
Syntax
SUMIF(range; criteria; sum_range)
Syntax Description
Range: the range to which the criteria are to be applied. Criteria: the cell in which the search criterion is shown, or the search criterion itself. Sum_range: the range from which values are summed. If this parameter has not been indicated, the values found in the Range are summed.
Returns the arithmetic-declining depreciation rate.Use this function to calculate the depreciation amount for one period of the total depreciation span of an object. Arithmetic declining depreciation reduces the depreciation amount from period to period by a fixed sum.
Syntax
SYD(Cost;Salvage;Life;Period)
Syntax Description
Cost: the initial cost of an asset. Salvage: the value of an asset after depreciation. Life: the period fixing the time span over which an asset is depreciated. Period: defines the period for which the depreciation is to be calculated.
This function converts a number to a blank text string.
Syntax
T(value)
Syntax Description
value: the value to be converted. Also, a reference can be used as a parameter. If the referenced cell includes a number or a formula containing a numerical result, the result will be an empty string.
Calculates the annual return on a treasury bill (). A treasury bill is purchased on the settlement date and sold at the full par value on the maturity date, that must fall within the same year. A discount is deducted from the purchase price.
Syntax
TBILLEQ(Settlement;Maturity;Discount)
Syntax Description
Settlement: the date of purchase of the security. Maturity: the date on which the security matures (expires). Discount: the percentage discount on acquisition of the security.
Calculates the price of a treasury bill per 100 currency units.
Syntax
TBILLPRICE(Settlement;Maturity;Discount)
Syntax Description
Settlement: the date of purchase of the security. Maturity: the date on which the security matures (expires). Discount: the percentage discount upon acquisition of the security.
Settlement: the date of purchase of the security. Maturity: the date on which the security matures (expires). Price: the price (purchase price) of the treasury bill per 100 currency units of par value.
Number: the value for which the t-distribution is calculated. Degrees_freedom: the number of degrees of freedom for the t-distribution. Mode = 1 returns the one-tailed test, Mode = 2 returns the two-tailed test.
Converts a number into text according to a given format.
Syntax
TEXT(Number; Format)
Syntax Description
Number: the numerical value to be converted. Format: the text which defines the format. Use decimal and thousands separators according to the language set in the cell format.
TIME returns the current time value from values for hours, minutes and seconds. This function can be used to convert a time based on these three elements to a decimal time value.
Syntax
TIME(hour; minute; second)
Syntax Description
Use an integer to set the hour.Use an integer to set the minute.Use an integer to set the second.
TIMEVALUE returns the internal time number from a text enclosed by quotes and which may show a possible time entry format.The internal number indicated as a decimal is the result of the date system configured to alculate date entries.
Syntax
TIMEVALUE("Text")
Syntax Description
Text: a valid time expression and must be entered in quotation marks.
Returns the probability associated with a Student's t-Test.
Syntax
TTEST(Data_1; Data_2; Mode; Type)
Syntax Description
Data_1: the dependent array or range of data for the first record. Data_2: the dependent array or range of data for the second record. Mode = 1 calculates the one-tailed test, Mode = 2 the two- tailed test. Type: the kind of t-test to perform. Type 1 means paired. Type 2 means two samples, equal variance (homoscedastic). Type 3 means two samples, unequal variance (heteroscedastic).
Returns the type of value.If an error occurs, the function returns a logical or numerical value.
Syntax
TYPE(value)
Syntax Description
Value: a specific value for which the data type is determined. Value 1 = number, value 2 = text, value 4 = Boolean value, value 8 = formula, value 16 = error value.
Returns the depreciation of an asset for a specified or partial period using a variable declining balance method.
Syntax
VDB(Cost;Salvage;Life;S;End;Factor;Type)
Syntax Description
Cost: the initial value of an asset. Salvage: the value of an asset at the end of the depreciation. Life: the depreciation duration of the asset. S: the start of the depreciation. A must be entered in the same date unit as the duration. End: the end of the depreciation. Factor (optional): the depreciation factor. Factor=2 is double rate depreciation. Type (optional): Type=1 means a switch to linear depreciation. In Type=0 no switch is made.
Parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.
Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value to the same line of a specific array column named by index.The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off.
Syntax
=VLOOKUP(Search criterion;array;index;Sort order)
Syntax Description
Search criterion: the value searched for in the first column of the array. array: the reference, which is to comprise at least two columns. index: the number of the column in the array that contains the value to be returned. The first column has the number 1. Sort order: an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.
Returns the day of the week for the given date value. The day is returned as an integer between 1 (Sunday) and 7 (Saturday) if no type or type = 1 is specified. If type=2, numbering begins at Monday=1; and if type=3 numbering begins at Monday=0.
Syntax
WEEKDAY(Number; Type)
Syntax Description
Number: as a date value, is a decimal for which the weekday is to be returned. Type: determines the type of calculation. For Type =1, the weekdays are counted starting from Sunday (this is the default even when the Type parameter is missing). For Type =2, the weekdays are counted starting from Monday =1. For Type = 3, the weekdays are counted starting from Monday = 0.
WEEKNUM calculates the week number of the year for the internal date value.The International Standard ISO 8601 has decreed that Monday shall be the first day of the week. A week that lies partly in one year and partly in another is assigned a number in the year in which most of its days lie. That means that week number 1 of any year is the week that contains the January 4th.
Syntax
WEEKNUM(number; mode)
Syntax Description
Number:the internal date number.Mode sets the start of the week and the calculation type.1 = Sunday2 = Monday
The result indicates the number of the calendar week for a Date.The WEEKNUM_ADD function is designed to calculate week numbers exactly as Microsoft Excel does. Use the WEEKNUM function, or format your date cells using the WW formatting code, when you need ISO 8601 week numbers.
Syntax
WEEKNUM_ADD(Date;Return type)
Syntax Description
Date: the date within the calendar week. Return type: 1 for week beginning on a Sunday, 2 for week beginning on a Monday.
Number: the value at which to calculate the Weibull distribution. Alpha: the Alpha parameter of the Weibull distribution. Beta: the Beta parameter of the Weibull distribution. C: indicates the type of function. If C equals 0 the form of the function is calculated, if C equals 1 the distribution is calculated.
The result is a date number that can be formatted as a date. You then see the date of a day that is a certain number of Workdays away from the Start date.
Syntax
WORKDAY (Start date;Days;Holidays)
Syntax Description
Start date: the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation. Days: the number of workdays. Positive value for a result after the start date, negative value for a result before the start date. Holidays: list of optional holidays. These are non-working days. Enter a cell range in which the holidays are listed individually.
Calculates the internal rate of return for a list of payments which take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years.If the payments take place at regular intervals, use the IRR function.
Syntax
XIRR(Values;Dates;Guess)
Syntax Description
Values and dates: a series of payments and the series of associated date values. The first pair of dates defines the start of the payment plan. All other date values must be later, but need not be in any order. The series of values must contain at least one negative and one positive value (receipts and deposits). Guess (optional): a guess can be input for the internal rate of return. The default is 10%.
Calculates the capital value (net present value)for a list of payments which take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years.If the payments take place at regular intervals, use the NPV function.
Syntax
XNPV(Rate;Values;Dates)
Syntax Description
Rate: the internal rate of return for the payments. Values and dates: a series of payments and the series of associated date values. The first pair of dates defines the start of the payment plan. All other date values must be later, but need not be in any order.
The result is a number between 0 and 1, representing the fraction of a year between Start date and End date.
Syntax
YEARFRAC (Start date;End date;Basis)
Syntax Description
Start date and end date: two date values. Basis: is chosen from a list of options and indicates how the year is to be calculated. Basis - Calculation 0 or missing - US method (NASD), 12 months of 30 days each 1 - Exact number of days in months, exact number of days in year 2 - Exact number of days in month, year has 360 days 3 - Exact number of days in month, year has 365 days 4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security. Maturity: the date on which the security matures (expires). Rate: the annual rate of interest. Price: the price (purchase price) of the security per 100 currency units of par value. Redemption: the redemption value per 100 currency units of par value. Frequency: number of interest payments per year (1, 2 or 4). Basis: is chosen from a list of options and indicates how the year is to be calculated. Basis - Calculation 0 or missing - US method (NASD), 12 months of 30 days each 1 - Exact number of days in months, exact number of days in year 2 - Exact number of days in month, year has 360 days 3 - Exact number of days in month, year has 365 days 4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security. Maturity: the date on which the security matures (expires). Price: the price (purchase price) of the security per 100 currency units of par value. Redemption: the redemption value per 100 currency units of par value. Basis: is chosen from a list of options and indicates how the year is to be calculated. Basis - Calculation 0 or missing - US method (NASD), 12 months of 30 days each 1 - Exact number of days in months, exact number of days in year 2 - Exact number of days in month, year has 360 days 3 - Exact number of days in month, year has 365 days 4 - European method, 12 months of 30 days each
Settlement: the date of purchase of the security. Maturity: the date on which the security matures (expires). Issue: the date of issue of the security. Rate: the interest rate of the security on the issue date. Price: the price (purchase price) of the security per 100 currency units of par value. Basis: is chosen from a list of options and indicates how the year is to be calculated. Basis - Calculation 0 or missing - US method (NASD), 12 months of 30 days each 1 - Exact number of days in months, exact number of days in year 2 - Exact number of days in month, year has 360 days 3 - Exact number of days in month, year has 365 days 4 - European method, 12 months of 30 days each
Returns the two-tailed P value of a z test with standard distribution.
Syntax
ZTEST(Data; Number; Sigma)
Syntax Description
Data: the array of the data. Number: the value to be tested. Sigma (optional): the standard deviation of the total population. If this argument is missing, the standard deviation of the sample in question will be processed.
