 |
用户中心 | 官方博客 | 常见问题 | Bug提交 |
| 其他百会产品
|
Functions Reference
Function Name:
| ABS | |
| Returns the absolute value of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose absolute value is to be calculated. |
| ACCRINT | |
| Calculates the accrued interest of a security in the case of periodic payments. | |
| Syntax | |
| |
| Syntax Description | |
|
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 |
| ACCRINTM | |
| Calculates the accrued interest of a security in the case of one-off payment at the settlement date. | |
| Syntax | |
| |
| 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 |
| ACOS | |
| Returns the arc cosine of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value, whose arc cosine value is to be calculated. |
| ACOSH | |
| Returns the inverse hyperbolic cosine of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose inverse hyperbolic cosine is to be calculated. |
| ACOT | |
| Returns the inverse cotangent of the given number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose inverse cotangent is to be calculated. |
| ACOTH | |
| Returns the inverse hyperbolic cotangent of the given number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose inverse hyperbolic cotangent is to be calculated. |
| ADDRESS | |
| 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 | |
| |
| 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. |
| AMORDEGRC | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
|
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 |
| AND | |
| 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 | |
| |
| 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. |
| ARABIC | |
| Calculates the value of a Roman number. The value range must be between 0 and 3999. | |
| Syntax | |
| |
| Syntax Description | |
| Text: the text that represents a Roman number. |
| AREAS | |
| Returns the number of individual ranges that belong to a multiple range. A range can consist of contiguous cells or a single cell. | |
| Syntax | |
| |
| Syntax Description | |
| Reference: represents the reference to a cell or cell range. |
| ASIN | |
| Returns the arcsine of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose arcsine is to be calculated. |
| ASINH | |
| Returns the inverse hyperbolic sine of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose inverse hyperbolic sine is to be calculated. |
| ATAN | |
| Returns the arctangent of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose arctangent value is to be calculated. |
| ATAN2 | |
| Returns the arctangent of the specified x and y coordinates. | |
| Syntax | |
| |
| Syntax Description | |
|
Number x: the value for the x coordinate.
Number y: the value for the y coordinate. |
| ATANH | |
| Returns the inverse hyperbolic tangent of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose inverse hyperbolic tangent is to be calculated. |
| AVEDEV | |
| Returns the average of the absolute deviations of data points from their mean. Displays the diffusion in a data set. | |
| Syntax | |
| |
| Syntax Description | |
| Number 1, Number 2,...Number 30 are values or ranges that represent a sample. Each number can also be replaced by a reference. |
| AVERAGE | |
| Returns the average of the arguments. | |
| Syntax | |
| |
| Syntax Description | |
| Number 1; Number 2;...Number 30 are numerical values or ranges. |
| AVERAGEA | |
| Returns the average of the arguments. The value of a text is 0. | |
| Syntax | |
| |
| Syntax Description | |
|
Value 1; Value 2;...Value 30 are values or ranges.
Text has the value of 0. |
| B | |
| Returns the probability of a sample with binomial distribution. | |
| Syntax | |
| |
| 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. |
| BASE | |
| 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 | |
| |
| 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. |
| BETADIST | |
| Returns the cumulative beta probability density function. | |
| Syntax | |
| |
| 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. |
| BETAINV | |
| Returns the inverse of the cumulative beta probability density function. | |
| Syntax | |
| |
| 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. |
| BIN2HEX | |
| The result is the hexadecimal number for the binary number entered. | |
| Syntax | |
| |
| 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. |
| BIN2OCT | |
| The result is the octal number for the binary number entered. | |
| Syntax | |
| |
| 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. |
| BINOMDIST | |
| Returns the individual term binomial distribution probability. | |
| Syntax | |
| |
| 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. |
| CEILING | |
| Rounds a number to the nearest integer or multiple of significance. | |
| Syntax | |
| |
| 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. |
| CELL | |
| Returns information on address, formatting or contents of a cell. | |
| Syntax | |
| |
| 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. |
| CHAR | |
| Converts a number into a character according to the current code table. The number can be a two-digit or three-digit integer number. | |
| Syntax | |
| |
| Syntax Description | |
| number: a number between 1 and 255 representing the code value for the character. |
| CHIDIST | |
| 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 | |
| |
| 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. |
| CHIINV | |
| Returns the inverse of the one-tailed probability of the chi-squared distribution. | |
| Syntax | |
| |
| Syntax Description | |
|
Number: the value of the error probability.
Degrees_freedom: the degrees of freedom of the experiment. |
| CHITEST | |
| 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 | |
| |
| Syntax Description | |
|
Data_B: the array of the observations.
Data_E: the range of the expected values. |
| CHOOSE | |
| Uses an index to return a value from a list of up to 30 values. | |
| Syntax | |
| |
| 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. |
| CLEAN | |
| All non-printing characters are removed from the string. | |
| Syntax | |
| |
| Syntax Description | |
| text: refers to the text from which to remove all non-printable characters. |
| COLUMN | |
| 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 | |
| |
| 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. |
| COLUMNS | |
| Returns the number of columns in the given reference. | |
| Syntax | |
| |
| Syntax Description | |
| array: the reference to a cell range whose total number of columns is to be found. The argument can also be a single cell. |
| COMBIN | |
| Returns the number of combinations for a given number of objects. | |
| Syntax | |
| |
| Syntax Description | |
|
Count 1: the total number of elements.
Count 2: the select count from the elements. |
| COMBINA | |
| Returns the number of combinations for a given number of objects (repetition included). | |
| Syntax | |
| |
| Syntax Description | |
|
Count 1: the total number of elements.
Count 2: the select count from the elements. |
| COMPLEX | |
| The result is a complex number which is returned from a real coefficient and an imaginary coefficient. | |
| Syntax | |
| |
| Syntax Description | |
|
Real num: the real coefficient of the complex number.
I num: the imaginary coefficient of the complex number. Suffix: list of options, "i" or "j". |
| CONCATENATE | |
| Combines several text strings into one string. | |
| Syntax | |
| |
| Syntax Description | |
| Text 1; text 2; ... represent up to 30 text passages which are to be combined into one string. |
| CONFIDENCE | |
| Returns the (1-alpha) confidence interval for a normal distribution. | |
| Syntax | |
| |
| Syntax Description | |
|
Alpha: is the level of the confidence interval.
STDEV: the standard deviation for the total population. Size: the size of the total population. |
| CONVERT_ADD | |
|
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 | |
| |
| 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. |
| CORREL | |
| Returns the correlation coefficient between two data sets. | |
| Syntax | |
| |
| Syntax Description | |
|
Data_1: the first data set
Data_2: the second data set. |
| COS | |
| Returns the cosine of the given number (angle). | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose cosine is to be calculated. |
| COSH | |
| Returns the hyperbolic cosine of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose hyperbolic cosine is to be calculated. |
| COT | |
| Returns the cotangent of the given angle. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose cotangent is to be calculated. |
| COTH | |
| Returns the hyperbolic cotangent of a given number (angle). | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose hyperbolic cotangent is to be calculated. |
| COUNT | |
| Counts how many numbers are in the list of arguments. Text entries are ignored. | |
| Syntax | |
| |
| Syntax Description | |
| Value1; value2, ... are 1 to 30 values or ranges representing the values to be counted. |
| COUNTA | |
| 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 | |
| |
| Syntax Description | |
| value1; value2, ... are 1 to 30 arguments representing the values to be counted. |
| COUNTBLANK | |
| Returns the number of empty cells. Enter the cell references separated by a colon in the range text field. | |
| Syntax | |
| |
| Syntax Description | |
| range: the cell range in which the empty cells are counted. |
| COUNTIF | |
| 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 | |
| |
| 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. |
| COUPDAYBS | |
| Returns the number of days from the first day of interest payment on a security until the settlement date. | |
| Syntax | |
| |
| 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. |
| COUPDAYS | |
| Returns the number of days in the current interest period in which the settlement date falls. | |
| Syntax | |
| |
| 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. |
| COUPDAYSNC | |
| Returns the number of days from the settlement date until the next interest date. | |
| Syntax | |
| |
| 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. |
| COUPNCD | |
| Returns the date of the first interest date after the settlement date. Format the result as a date. | |
| Syntax | |
| |
| 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. |
| COUPNUM | |
| Returns the number of coupons (interest payments) between the settlement date and the maturity date. | |
| Syntax | |
| |
| 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. |
| COUPPCD | |
| Returns the date of the interest date prior to the settlement date. Format the result as a date. | |
| Syntax | |
| |
| 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. |
| COVAR | |
| Returns the covariance of the product of paired deviations. | |
| Syntax | |
| |
| Syntax Description | |
|
Data_1: the first data set.
Data_2: the second data set. |
| CRITBINOM | |
| Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value. | |
| Syntax | |
| |
| Syntax Description | |
|
Trials: the total number of trials.
SP: the probability of success for one trial Alpha: the threshold probability to be reached or exceeded. |
| CUMIPMT | |
| Calculates the cumulative interest payments, that is, the total interest, for an investment based on a constant interest rate. | |
| Syntax | |
| |
| 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. |
| CUMIPMT_ADD | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
|
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). |
| CUMPRINC | |
| Returns the cumulative interest paid for an investment period with a constant interest rate. | |
| Syntax | |
| |
| 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. |
| CUMPRINC_ADD | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
|
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). |
| DATE | |
| 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 | |
| |
| 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." |
| DATEVALUE | |
| 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 | |
| |
| Syntax Description | |
| Text: a valid date expression and must be entered with quotation marks. |
| DAY | |
| Returns the day of given date value. The day is returned as an integer between 1 and 31. You can also enter a negative date/time value. | |
| Syntax | |
| |
| Syntax Description | |
| Number: as a time value, is a decimal, for which the day is to be returned. |
| DAYS | |
| Calculates the difference between two date values. The result returns the number of days between the two days. | |
| Syntax | |
| |
| Syntax Description | |
|
Date_1: the start date.
Date_2: the end date. If Date_2 is an earlier date than Date_1 the result is a negative number. |
| DAYS360 | |
| Returns the difference between two dates based on the 360 day year used in interest calculations. | |
| Syntax | |
| |
| 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. |
| DDB | |
| 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 | |
| |
| 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. |
| DEC2BIN | |
| The result is the binary number for the decimal number entered between -512 and 511. | |
| Syntax | |
| |
| 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. |
| DEC2HEX | |
| The result is the hexadecimal number for the decimal number entered. | |
| Syntax | |
| |
| 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. |
| DEC2OCT | |
| The result is the octal number for the decimal number entered. | |
| Syntax | |
| |
| 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. |
| DECIMAL | |
| 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 | |
| |
| 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. |
| DEGREES | |
| Converts radians into degrees. | |
| Syntax | |
| |
| Syntax Description | |
| Number: is the value to be converted. |
| DELTA | |
| The result is TRUE (1) if both numbers, which are delivered as an argument, are equal, otherwise it is FALSE (0). | |
| Syntax | |
| |
| Syntax Description | |
| =DELTA(1;2) returns 0 |
| DEVSQ | |
| Returns the sum of squares of deviations based on a sample mean. | |
| Syntax | |
| |
| Syntax Description | |
| Number 1,number 2,...number 30 numerical values or ranges representing a sample. |
| DISC | |
| Calculates the allowance (discount) of a security as a percentage. | |
| Syntax | |
| |
| 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. |
| DOLLAR | |
| 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 | |
| |
| 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. |
| DOLLARDE | |
| Converts a quotation that has been given as a decimal fraction into a decimal number. | |
| Syntax | |
| |
| Syntax Description | |
|
Fractional dollar: a number given as a decimal fraction.
Fraction: a whole number that is used as the denominator of the decimal fraction. |
| DOLLARFR | |
| Converts a quotation that has been given as a decimal number into a mixed decimal fraction. | |
| Syntax | |
| |
| Syntax Description | |
|
Decimal dollar: a decimal number.
Fraction: a whole number that is used as the denominator of the decimal fraction. |
| DURATION | |
| Calculates the number of periods required by an investment to attain the desired value. | |
| Syntax | |
| |
| 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. |
| DURATION_ADD | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
|
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. |
| EDATE | |
| The result is a date which is a number of Months away from the Start date. Only months are considered; days are not used for calculation. | |
| Syntax | |
| |
| Syntax Description | |
|
Start date: a date.
Months: the number of months. |
| EFFECTIVE | |
| 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 | |
| |
| Syntax Description | |
|
NOM: the nominal interest.
P: the number of interest payment periods per year. |
| EFFECT_ADD | |
| 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 | |
| |
| Syntax Description | |
|
Nominal rate: the annual nominal rate of interest.
Npery: the number of interest payments per year. |
| EOMONTH | |
| Returns the date of the last day of a month which falls Months away from the Start date. | |
| Syntax | |
| |
| Syntax Description | |
|
Start date: calculated from this point onwards.
Months: the number of months before (negative) or after (positive) the Start Date. |
| ERF | |
| Returns values of the Gaussian error integral. | |
| Syntax | |
| |
| Syntax Description | |
|
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. |
| ERFC | |
| Returns complementary values of the Gaussian error integral between x and infinity. | |
| Syntax | |
| |
| Syntax Description | |
| Lower limit: lower limit of integral |
| ERRORTYPE | |
| 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 | |
| |
| Syntax Description | |
| Reference: the address of the cell in which the error occurs. |
| EXACT | |
| Compares two text strings and returns TRUE if they are identical. This function is case-sensitive. | |
| Syntax | |
| |
| Syntax Description | |
|
text_1: refers to the first text to compare.
text_2: the second text to compare. |
| EXP | |
| Returns e raised to the power of a number. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the power to which e is to be raised. |
| EXPONDIST | |
| Returns the exponential distribution. | |
| Syntax | |
| |
| Syntax Description | |
|
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. |
| FACT | |
| Returns the factorial of a number. FACT(0) returns 1. FACT(n) returns 1*2*3*4* ... *n. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value whose factorial is to be calculated. |
| FACTDOUBLE | |
| The result is the factorial of the number with increments of 2. | |
| Syntax | |
| |
| 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. |
| FALSE | |
| Returns the logical value FALSE. The FALSE() function does not require any arguments, and always returns the logical value FALSE. | |
| Syntax | |
| |
| Syntax Description | |
|
If A=TRUE and B=FALSE the following examples appear: =AND(A;B) returns FALSE =OR(A;B) returns TRUE |
| FDIST | |
| Calculates the values of an F distribution. | |
| Syntax | |
| |
| Syntax Description | |
|
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. |
| FIND | |
| 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 | |
| |
| 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. |
| FINV | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
|
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. |
| FISHER | |
| Returns the Fisher transformation for x and creates a function close to a normal distribution. | |
| Syntax | |
| |
| Syntax Description | |
| Number is the value to be transformed. |
| FISHERINV | |
| Returns the inverse of the Fisher transformation for x and creates a function close to a normal distribution. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value that is to undergo reverse-transformation. |
| FIXED | |
| 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 | |
| |
| 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. |
| FLOOR | |
| Rounds a number down to the nearest multiple of significance. | |
| Syntax | |
| |
| 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. |
| FORECAST | |
| Extrapolates future values based on existing x and y values. | |
| Syntax | |
| |
| 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. |
| FORMULA | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
| reference: the cell whose formula is the result. |
| FTEST | |
| Returns the result of an F test. | |
| Syntax | |
| |
| Syntax Description | |
|
Data_1: the first record array.
Data_2: the second record array. |
| FV | |
| Returns the future value of an investment based on periodic, constant payments and a constant interest rate (Future Value). | |
| Syntax | |
| |
| 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. |
| FVSCHEDULE | |
| Calculates the accumulated value of the starting capital for a series of periodically varying interest rates. | |
| Syntax | |
| |
| Syntax Description | |
|
Principal: the starting capital.
Schedule: a series of interest rates, for example, as a range H3:H5 or as a (List) (see example). |
| GAMMADIST | |
| Returns the values of a Gamma distribution. | |
| Syntax | |
| |
| Syntax Description | |
|
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. |
| GAMMAINV | |
| Returns the inverse of the Gamma cumulative distribution. This function allows you to search for variables with different distribution. | |
| Syntax | |
| |
| 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. |
| GAMMALN | |
| Returns the natural logarithm of the Gamma function: G(x). | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value for which the natural logarithm of the Gamma function is to be calculated. |
| GAUSS | |
| Returns the standard normal cumulative distribution. | |
| Syntax | |
| |
| Syntax Description | |
| Number: the value for which the integral value of the normalized standard distribution is to be calculated. |
| GCD | |
| Returns the greatest common divisor of two or more integers. | |
| Syntax | |
| |
| Syntax Description | |
| Integer 1 to 30 are up to 30 integers whose greatest common divisor is to be calculated. |
| GCD_ADD | |
| 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. | |
| Syntax | |
| |
| Syntax Description | |
| Number(s): a list of up to 30 numbers. |
| GEOMEAN | |
| Returns the geometric mean of a sample. | |
| Syntax | |
| |
| Syntax Description | |
| Number 1, Number 2,...Number 30 are numeric arguments or ranges that represent a random sample. |
| GESTEP | |
| The result is 1 if Number is greater than or equal to Step. | |
| Syntax | |
| |
| Syntax Description | |
| =GESTEP(5;1) returns 1 |
| HARMEAN | |
| Returns the harmonic mean of a data set. | |
| Syntax | |
| |
| Syntax Description | |
| Number 1,Number 2,...Number 30 are up to 30 values or ranges, that can be used to calculate the harmonic mean. |
| HEX2BIN | |
| The result is the binary number for the hexadecimal number entered. | |
| Syntax | |
| |
| 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. |