You are viewing the static version of this documentation. If you have JavaScript enabled, you may wish to view the dynamic version of this page which offers filtering and searching. More about this documentation.
| Type | Function | Syntax | Description |
|---|---|---|---|
| Array | Frequency distribution | FREQUENCY(data, classes) | Calculates the frequency distribution in a one-column-array. The default value supply and the number of intervals or classes are used to count how many values are omitted on the single intervals. Data is the array of, or reference to, the set of values to be counted. Classes is the array of the class set. View example (new window) |
| Array | Exponential trend | GROWTH(data_Y, data_X, new_data_X, function_type) | Calculates the points of an exponential trend in an array. Data_Y is the Y Data array. Data_X (optional) is the X Data array. New_Data_X (optional) is the X data array, in which the values are recalculated. Function_type is optional. If function_type = 0, functions in the form y = m^x are calculated. Otherwise, y = b*m^x functions are calculated. |
| Array | Linear trend parameters | LINEST(data_Y, data_X, linear_type, stats) | Returns the parameters of a linear trend. Data_Y is the Y Data array. Data_X (optional) is the X Data array. Linear_Type (optional). If the line goes through the zero point, then set Linear_Type = 0. Stats (optional): If Stats=0, only the regression coefficient is calculated. Otherwise, other statistics will be seen. |
| Array | Exponential regression curve | LOGEST(data_Y, data_X, function_type, stats) | Calculates the adjustment of the entered data as an exponential regression curve (y=b*m^x). Data_Y is the Y Data array. Data_X (optional) is the X Data array. Function_type (optional): If function_type = 0, functions in the form y = m^x are calculated. Otherwise, y = b*m^x functions are calculated. Stats (optional). If Stats=0, only the regression coefficient is calculated. |
| Array | Array determinant | MDETERM(array) | Returns the array determinant of an array. This function returns a value in the current cell, it is not necessary to define a range for the results. Array is a square array in which the determinants are defined. |
| Array | Inverse array | MINVERSE(array) | Returns the inverse array. Array is a square array that is to be inverted. |
| Array | Array product | MMULT(array, array) | Calculates the array product of two arrays. The number of columns for array 1 must match the number of rows for array 2. The square array has an equal number of rows and columns. Array at first place is the first array used in the array product. Array at second place is the second array with the same number of rows. |
| Array | Sum array product | SUMPRODUCT(array 1, array 2, ...array 30) | Multiplies corresponding elements in the given arrays, and returns the sum of those products. Array 1, array 2,...array 30 are arrays whose corresponding elements are to be multiplied. At least one array must be part of the argument list. If only one array is given, all array elements are summed. |
| Array | Sum array product | SUMX2MY2(array_X, array_Y) | Returns the sum of the difference of squares of corresponding values in two arrays. Array_X is the first array whose elements are to be squared and added. Array_Y is the second array whose elements are to be squared and subtracted. |
| Array | Sum array product | SUMX2PY2(array_X, array_Y) | Returns the sum of the sum of squares of corresponding values in two arrays. Array_X is the first array whose arguments are to be squared and added. Array_Y is the second array, whose elements are to be added and squared. |
| Array | Sum array product | SUMXMY2(array_X, array_Y) | Adds the squares of the variance between corresponding values in two arrays. Array_X is the first array whose elements are to be subtracted and squared. Array_Y is the second array, whose elements are to be subtracted and squared. |
| Array | Transpose array | TRANSPOSE(array) | Transposes the rows and columns of an array. Array is the array in the spreadsheet that is to be transposed. |
| Array | Linear trend | TREND(data_Y, data_X, new_data_X, linear_Type) | Returns values along a linear trend. Data_Y is the Y Data array. Data_X (optional) is the X Data array. New_data_X (optional) is the array of the X data, which are used for recalculating values. Linear_type is optional. If linear_type = 0, then lines will be calculated through the zero point. Otherwise, offset lines will also be calculated. The default is linear_type <> 0. |
| Date | Date to serial | DATE(year, month, day) | Converts a date written as year, month, day to an internal serial number and displays it in the cell's formatting. Year is an integer between 1583 and 9956 or 0 and 99. Month is an integer between 1 and 12. Day is an integer between 1 and 31. View example (new window) |
| Date | Internal date number | DATEVALUE("Text") | Returns the internal date number for text in quotes. Text is a valid date expression and must be entered with quotation marks. View example (new window) |
| Date | Day as integer | DAY(number) | Returns the day, as an integer, of the given date value. A negative date/time value can be entered. Number is a time value. View example (new window) |
| Date | Difference between two dates | DAYS360(date_1, date_2, type) | Returns the difference between two dates based on the 360 day year used in interest calculations. If Date_2 is earlier than Date_1, the function will return a negative number. Type (optional) determines the type of difference calculation: the US method (0) or the European method (≠0). View example (new window) |
| Date | Date estimate | EDATE(start_date, months) | The result is a date a number of Months away from the given Start_date. Only months are considered, days are not used for calculation. Months is the number of months. View example (new window) |
| Date | End of month | EOMONTH(start_date, months) | Returns the date of the last day of a month which falls Months away from the given Start_date. Months is the number of months before (negative) or after (positive) the start date. View example (new window) |
| Date | Hour | HOUR(number) | Returns the hour, as an integer, for the given time value. Number is a time value. View example (new window) |
| Date | Minute | MINUTE(number) | Returns the minute, as an integer, for the given time value. Number is a time value. View example (new window) |
| Date | Month | MONTH(number) | Returns the month, as an integer, for the given date value. Number is a time value. |
| Date | Number of workdays | NETWORKDAYS(start_date, end_date, holidays) | Returns the number of workdays between start_date and end_date. Holidays can be deducted. Start_date is the date from which the calculation is carried out. End_date is the date up to which the calculation is carried out. If the start or end date is a workday, the day is included in the calculation. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually. |
| Date | Computer system time/date | NOW() | Returns the computer system date and time. The value is updated when your document recalculates. NOW is a function without arguments. |
| Date | Second | SECOND(number) | Returns the second, as an integer, for the given time value. Number is a time value. |
| Date | Time | TIME(hour, minute, second) | 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. Hour, minute and second must all be integers. |
| Date | Today | TODAY() | Returns the current computer system date. The value is updated when your document recalculates. TODAY is a function without arguments. |
| Date | Day of Week | WEEKDAY(number, type) | Returns the day of the week for the given number (date value). The day is returned as an integer based on the type. Type determines the type of calculation: type = 1 (default), the weekdays are counted starting from Sunday (Monday = 0), type = 2, the weekdays are counted starting from Monday (Monday = 1), type = 3, the weekdays are counted starting from Monday (Monday = 0). |
| Date | Date number | WORKDAY(start_date, days, holidays) | Returns 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. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually. |
| Date | Year | YEAR(number) | Returns the year as a number according to the internal calculation rules. Number shows the internal date value for which the year is to be returned. |
| Date | Year fraction | YEARFRAC(start_date, end_date, basis) | Returns a number between 0 and 1, representing the fraction of a year between start_date and end_date. Start_date and end_date are two date values. Basis is chosen from a list of options and indicates how the year is to be calculated. |
| Engineering | Binary to decimal | BIN2DEC(number) | Converts a binary number to its decimal value. View example (new window) |
| Engineering | Binary to hexadecimal | BIN2HEX(number, places) | Converts a binary number to its hexadecimal value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Binary to octal | BIN2OCT(number, places) | Converts a binary number to its octal value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Decimal to binary | DEC2BIN(number, places) | Converts a decimal number to its binary value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Decimal to hexadecimal | DEC2HEX(number, places) | Converts a decimal number to its hexadecimal value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Decimal to octal | DEC2OCT(number, places) | Converts a decimal number to its octal value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Hexadecimal to binary | HEX2BIN(number, places) | Converts a hexadecimal number to its binary value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Hexadecimal to decimal | HEX2DEC(number) | Converts a hexadecimal number to its decimal value. |
| Engineering | Hexadecimal to octal | HEX2OCT(number, places) | Converts a hexadecimal number to its octal value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Octal to binary | OCT2BIN(number, places) | Converts an octal number to its binary value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Engineering | Octal to decimal | OCT2DEC(number) | Converts an octal number to its decimal value. |
| Engineering | Octal to hexadecimal | OCT2HEX(number, places) | Converts an octal number to its hexadecimal value. Places (optional) is the number of characters to use in the result. If it is larger than the number, it will pad the result with leading zeros. |
| Filter | Array Filter | FILTER(sourceArray, arrayCondition_1, arrayCondition_2, ..., arrayCondition_30) | Returns a filtered version of the given source array, where only certain rows or columns have been included. Each condition should be either a 1-dimensional range of boolean values, or else an array-formula expression which evaluates to a 1-dimensional array of booleans. If the conditions evaluate to a column array, then only the rows from the source array corresponding to the true values of the condition array will be returned. Likewise, if the conditions evaluate to a row array, then only the columns of the source array corresponding to the true values in the condition will be returned. If there are multiple conditions, then all must be true for the corresponding values in the source array to be returned. View example (new window) |
| Filter | Data Sort | SORT(data, keyColumn_1, ascOrDesc_1, keyColumn_2, ascOrDesc_2, ..., keyColumn_30, ascOrDesc_30) | Returns the rows in the given data range (or array), sorted according to the given key columns. There are two ways to specify the key column: either as a column within the data range, or as another column outside the range. To specify a column within the data range, give its index within the range: 1 is the first column of the range, 2 is the second column, etc. To specify another column outside the range (e.g. an intermediate calculated value that you don't want to include in your output), just specify it as a range. Each key column must also be marked to sort either ascending or descending, by adding either TRUE or FALSE after that specifying that key column. You can also specify only a single array or range. In that case, the function returns that range sorted ascending by the columns from left to right. View example (new window) |
| Filter | Unique rows | UNIQUE(sourceArray) | Returns only the unique rows in the source array, discarding duplicates, in the order in which those rows first appears. View example (new window) |
| Financial | Accrued interest | ACCRINT(issue, first_interest, settlement, rate, par, frequency, basis) | Calculates the accrued interest of a security in the case of periodic payments. Issue is the issue date of the security. First_interest is the first interest date of the security. Settlement is the maturity date. Rate is the annual nominal rate of interest (coupon interest rate). Par is the par value of the security. Frequency is the 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. View example (new window) |
| Financial | Accrued interest | ACCRINTM(issue, settlement, rate, par, basis) | Calculates the accrued interest of a security in the case of one-off payment at the settlement date. Issue is the issue date of the security. Settlement is the maturity date. Rate is the annual nominal rate of interest (coupon interest rate). Par is the par value of the security. Basis is chosen from a list of options and indicates how the year is to be calculated. |
| Financial | Interest | COUPDAYBS(settlement, maturity, frequency, basis) | Returns the number of days from the first day of interest payment on a security until the settlement date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the 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. |
| Financial | Interest | COUPDAYS(settlement, maturity, frequency, basis) | Returns the number of days in the current interest period in which the settlement date falls. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the 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. |
| Financial | Interest / settlement | COUPDAYSNC(settlement, maturity, frequency, basis) | Returns the number of days from the settlement date until the next interest date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the 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. |
| Financial | First interest date | COUPNCD(settlement, maturity, frequency, basis) | Returns the date of the first interest date after the settlement date, and formats the result as a date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the 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. |
| Financial | Number of coupons | COUPNUM(settlement, maturity, frequency, basis) | Returns the number of coupons (interest payments) between the settlement date and the maturity date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the 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. |
| Financial | Date of interest date | COUPPCD(settlement, maturity, frequency, basis) | Returns the date of the interest date prior to the settlement date, and formats the result as a date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the 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. |
| Financial | Cumulative interest payments | CUMIPMT(rate, NPER, PV, S, E, type) | Calculates the cumulative interest payments (the total interest) for an investment based on a constant interest rate. Rate is the periodic interest rate. NPER is the payment period with the total number of periods. NPER can also be a non-integer value. The rate and NPER must refer to the same unit, and thus both must be calculated annually or monthly. PV is the current value in the sequence of payments. S is the first period. E is the last period. Type is the due date of the payment at the beginning (1) or end (0) of each period. |
| Financial | Cumulative interest with constant interest rate | CUMPRINC(rate, NPER, PV, S, E, type) | Returns the cumulative interest paid for an investment period with a constant interest rate. Rate is the periodic interest rate. NPER is the payment period with the total number of periods. NPER can also be a non-integer value. The rate and NPER must refer to the same unit, and thus both must be calculated annually or monthly. PV is the current value in the sequence of payments. S is the first period. E is the last period. Type is the due date of the payment at the beginning (1) or end (0) of each period. |
| Financial | Depreciation of asset | DB(cost, salvage, life, period, month) | Returns the depreciation of an asset for a specified period using the double-declining balance method. Cost is the initial cost of an asset. Salvage is the value of an asset at the end of the depreciation. Life defines the period over which an asset is depreciated. Period is the length of each period. The life must be entered in the same date unit as the depreciation period. Month (optional) denotes the number of months for the first year of depreciation. |
| Financial | Depreciation | DDB(cost, salvage, life, period, factor) | Returns the depreciation of an asset for a specified period using the arithmetic-declining method. Note that the book value will never reach zero under this calculation type. Cost fixes the initial cost of an asset. Salvage fixes the value of an asset at the end of its life. Life is the number of periods defining how long the asset is to be used. Period defines the length of the period. The period must be entered in the same time unit as the life. Factor (optional) is the factor by which depreciation decreases. |
| Financial | Allowance | DISC(settlement, maturity, price, redemption, basis) | Calculates the allowance (discount) of a security as a percentage. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Price is the price of the security per 100 currency units of par value. Redemption is 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. |
| Financial | Fraction to number | DOLLARDE(fractional _dollar, fraction) | Converts a quotation that has been given as a decimal fraction into a decimal number. Fractional_dollar is a number given as a decimal fraction. (In this number, the decimal value is the numerator of the fraction.) Fraction is a whole number that is used as the denominator of the decimal fraction. |
| Financial | Quotation | DOLLARFR(decimal _dollar, fraction) | Converts a quotation that has been given as a decimal number into a mixed decimal fraction. The decimal of the result is the numerator of the fraction that would have Fraction as the denominator. Decimal_dollar is a decimal number. Fraction is a whole number that is used as the denominator of the decimal fraction. |
| Financial | Duration | DURATION(rate, PV, FV) | Calculates the number of periods required by an investment to attain the desired value. Rate (a constant) is the interest rate to be calculated for the entire duration. Entering the interest rate divided by the periods per year, can calculate the interest after each period. PV is the present value. FV is the desired future value of the investment. |
| Financial | Effective annual interest rate | EFFECT(NOM, P) | Returns the effective annual interest rate, given the nominal annual interest rate (NOM) and the number of compounding periods (P) per year. |
| Financial | Future Value | FV(rate, NPER, PMT, PV, type) | Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Rate is the periodic interest rate. NPER is the total number of periods. PMT is the annuity paid regularly per period. PV (optional) is the present cash value of an investment. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
| Financial | Future Value | FVSCHEDULE(principal, schedule) | Calculates the accumulated value of the starting capital for a series of periodically varying interest rates. Principal is the starting capital. Schedule is a series of interest rates. Schedule has to be entered with cell references. |
| Financial | Annual interest rate | INTRATE(settlement, maturity, investment, redemption, basis) | 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 with no interest being paid. Settlement is the date of purchase of the security. Maturity is the date on which the security is sold. Investment is the purchase price. Redemption is the selling price. Basis is chosen from a list of options and indicates how the year is to be calculated. |
| Financial | Periodic amoritization | IPMT(rate, period, NPER, PV, FV, type) | Calculates the periodic amortization for an investment with regular payments and a constant interest rate. Rate is the periodic interest rate. Period is the period for which the compound interest is calculated. NPER is the total number of periods during which annuity is paid. Period=NPER, if compound interest for the last period is calculated. 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 (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
| Financial | Internal Rate of Return | IRR(values, guess) | 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). Values is an array containing the values. Guess (optional) is the estimated value. If you can provide only a few values, you should provide an initial guess to enable the iteration. |
| Financial | Modified Macauley duration | MDURATION(settlement, maturity, coupon, yield, frequency, basis) | Calculates the modified Macauley duration of a fixed interest security in years. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Coupon is the annual nominal rate of interest (coupon interest rate) Yield is the annual yield of the security. Frequency is the 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. |
| Financial | Modified internal rate of return | MIRR(values, investment, reinvest_rate) | Calculates the modified internal rate of return of a series of investments. Values corresponds to the array or the cell reference for cells whose content corresponds to the payments. Investment is the rate of interest of the investments (the negative values of the array) Reinvest_rate is the rate of interest of the reinvestment (the positive values of the array). |
| Financial | Nominal interest rate | NOMINAL(effective_rate, Npery) | Calculates the yearly nominal interest rate, given the effective rate and the number of compounding periods per year. Effective_rate is the effective interest rate Npery is the number of periodic interest payments per year. |
| Financial | Number of periods for an investment | NPER(rate, PMT, PV, FV, type) | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Rate is the periodic interest rate. PMT is the constant annuity paid in each period. PV is the present value (cash value) in a sequence of payments. FV (optional) is the future value, which is reached at the end of the last period. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
| Financial | Net present value | NPV(Rate, value_1, value_2, ... value_30) | Returns the net present value of an investment based on a series of periodic cash flows and a discount rate. Rate is the discount rate for a period. Value_1, value_2,... value_30 are values representing deposits or withdrawals. |
| Financial | Periodic payment | PMT(rate, NPER, PV, FV, type) | Returns the periodic payment for an annuity with constant interest rates. Rate is the periodic interest rate. NPER is the number of periods in which annuity is paid. PV is the present value (cash value) in a sequence of payments. FV (optional) is the desired value (future value) to be reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
| Financial | Payment on principal | PPMT(rate, period, NPER, PV, FV, type) | 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. Rate is the periodic interest rate. Period is the amortization period. NPER is the total number of periods during which annuity is paid. PV is the present value in the sequence of payments. FV (optional) is the desired (future) value. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
| Financial | Market value | PRICE(settlement, maturity, rate, yield, redemption, frequency, basis) | Calculates the market value of a fixed interest security with a par value of 100 currency units as a function of the forecast yield. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Rate is the annual nominal rate of interest (coupon interest rate). Yield is the annual yield of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the 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. |
| Financial | Price per 100 currency units | PRICEDISC(settlement, maturity, discount, redemption, basis) | Calculates the price per 100 currency units of par value of a non-interest- bearing security. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Discount is the discount of a security as a percentage. Redemption is 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. |
| Financial | Price per 100 currency units | PRICEMAT(settlement, maturity, issue, rate, yield, basis) | Calculates the price per 100 currency units of par value of a security, that pays interest on the maturity date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security. Rate is the interest rate of the security on the issue date. Yield is the annual yield of the security. Basis is chosen from a list of options and indicates how the year is to be calculated. |
| Financial | Present value | PV(rate, NPER, PMT, FV, type) | Returns the present value of an investment resulting from a series of regular payments. Rate defines the interest rate per period. NPER is the total number of payment periods. PMT is the regular payment made per period. FV (optional) defines the future value remaining after the final installment has been made. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
| Financial | Constant interest rate | RATE(NPER, PMT, PV, FV, type, guess) | Returns the constant interest rate per period of an annuity. NPER is the total number of periods, during which payments are made (payment period). PMT is the constant payment (annuity) paid during each period. PV is the cash value in the sequence of payments. FV (optional) is the future value, which is reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. Guess (optional) determines the estimated value of the interest with iterative calculation. |
| Financial | Amount received | RECEIVED(settlement, maturity, investment, discount, basis) | Calculates the amount received that is paid for a fixed-interest security at a given point in time. Settlement is the date of purchase of the security. Maturity is the date on which the security matures. Investment is the purchase sum. Discount is 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. |
| Financial | Straight-line depreciation | SLN(cost, salvage, life) | Returns the straight-line depreciation of an asset for one period. The amount of the depreciation is constant during the depreciation period. Cost is the initial cost of an asset. Salvage is the value of an asset at the end of the depreciation. Life is the depreciation period determining the number of periods in the depreciation of the asset. |
| Financial | Arithmetic-declining depreciation rate | SYD(cost, salvage, life, period) | 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. Cost is the initial cost of an asset. Salvage is the value of an asset after depreciation. Life is the period fixing the time span over which an asset is depreciated. Period defines the period for which the depreciation is to be calculated. |
| Financial | Annual return | TBILLEQ(settlement, maturity, discount) | Calculates the annual return on a treasury bill. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). (The settlement and maturity date must be in the same year.) Discount is the percentage discount on acquisition of the security. |
| Financial | Treasury bill price | TBILLPRICE(settlement, maturity, discount) | Calculates the price of a treasury bill per 100 currency units. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Discount is the percentage discount upon acquisition of the security. |
| Financial | Yield Treasury bill | TBILLYIELD(settlement, maturity, price) | Calculates the yield of a treasury bill. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Price is the price (purchase price) of the treasury bill per 100 currency units of par value. |
| Financial | Internal rate | XIRR(values, dates, guess) | 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. Values and dates are a series of payments and the series of associated date values entered as cell references. Guess (optional) is a guess for the internal rate of return. The default is 10%. |
| Financial | Capital value | XNPV(rate, values, dates) | 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. Rate is the internal rate of return for the payments. Values and dates are a series of payments and the series of associated date values entered as cell references. |
| Financial | Yield of security | YIELD(settlement, maturity, rate, price, redemption, frequency, basis) | Calculates the yield of a security. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Rate is the annual rate of interest. Price is the price (purchase price) of the security per 100 currency units of par value. Redemption is the redemption value per 100 currency units of par value. Frequency is the 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. |
| Financial | Annual yield | YIELDDISC(settlement, maturity, price, redemption, basis) | Calculates the annual yield of a non-interest-bearing security. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Price is the price (purchase price) of the security per 100 currency units of par value. Redemption is 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. |
| Google Finance information | GoogleFinance(symbol, attribute) | Retrieves market information from Google Finance. Full documentation can be found at http://docs.google.com/support/spreadsheets/bin/answer.py?answer=54198 View example (new window) | |
| Google Lookup information | GoogleLookup(entity, attribute) | Retrieves facts from the web. Full documentation can be found at http://docs.google.com/support/spreadsheets/bin/answer.py?answer=54199 View example (new window) | |
| Import data from external source | ImportData(URL) | Retrieves information from a CSV or TSV file. Full documentation can be found at http://docs.google.com/support/spreadsheets/bin/answer.py?answer=75507 | |
| Import data from external source | ImportFeed(URL, query, headers, numItems) | Retrieves information from a data feed. Full documentation can be found at http://docs.google.com/support/spreadsheets/bin/answer.py?answer=75507 View example (new window) | |
| Import data from external source | ImportHtml(URL, query, index) | Retrieves information from an HTML file. Full documentation can be found at http://docs.google.com/support/spreadsheets/bin/answer.py?answer=75507 View example (new window) | |
| Cross-workbook reference | ImportRange(spreadsheet_key, [sheet!]range) | Pulls one or multiple cell values from one spreadsheet into another. Full documentation can be found at http://documents.google.com/support/spreadsheets/bin/answer.py?answer=98757 | |
| Import data from external source | ImportXML(URL, query) | Retrieves information from an XML file. Full documentation can be found at http://docs.google.com/support/spreadsheets/bin/answer.py?answer=75507 View example (new window) | |
| Info | Blank cell | ISBLANK(value) | 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. Value is the content to be tested. View example (new window) |
| Info | Error | ISERR(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. Value is any value or expression in which a test is performed to determine whether an error value not equal to #N/A is present. |
| Info | Error | ISERROR(value) | 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. Value is any value where a test is performed to determine whether it is an error value. |
| Info | Logical | ISLOGICAL(value) | 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. Value is the value to be tested for logical number format. |
| Info | Error | ISNA(value) | Returns TRUE if a cell contains the #N/A (value not available) error value. If an error occurs, the function returns a logical or numerical value. Value is the value or expression to be tested. |
| Info | Tests text | ISNONTEXT(value) | 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. Value is any value or expression where a test is performed to determine whether it is a text or numbers or a Boolean value. |
| Info | Tests numbers | ISNUMBER(value) | Returns TRUE if the value refers to a number. If an error occurs, the function returns a logical or numerical value. Value is any expression to be tested to determine whether it is a number or text. |
| Info | Tests reference | ISREF(value) | 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. Value is the value to be tested, to determine whether it is a reference. |
| Info | Tests text | ISTEXT(value) | Returns TRUE if the cell contents refer to text. If an error occurs, the function returns a logical or numerical value. Value is a value, number, Boolean value, or error value to be tested. |
| Info | Test | N(value) | 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. Value is the parameter to be converted into a number. |
| Info | Error | NA() | Returns the error value #N/A. |
| Logical | And | AND(logical_value_1, logical_value_2, ...logical_value_30) | Returns TRUE if all arguments are TRUE. If any element is FALSE, this function returns the FALSE value. 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 View example (new window) |
| Logical | FALSE | FALSE() | Set the logical value to FALSE. The FALSE() function does not require any arguments. |
| Logical | If | IF(test, then_value, otherwise_value) | Specifies a logical test to be performed. Test is any value or expression that can be TRUE or FALSE. Then_value (optional) is the value that is returned if the logical test is TRUE. Otherwise_value (optional) is the value that is returned if the logical test is FALSE. |
| Logical | If error | IFERROR(test, value) | If the first argument is an error and the second argument is null, then a blank will be returned. If the first argument is an error and the second argument is not null, then the second argument will be returned. If the first argument is not an error, then it will be returned. |
| Logical | Reverse of logical value | NOT(logical_value) | Reverses the logical value. Logical_value is any value to be reversed. |
| Logical | Or | OR(logical_value_1, logical_value_2, ...logical_value_30) | Returns TRUE if at least one argument is TRUE. Returns the value FALSE if all the arguments have the logical value FALSE.. 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. |
| Logical | TRUE | TRUE() | Sets the logical value to TRUE. The TRUE() function does not require any arguments. |
| Lookup | Address | ADDRESS(row, column, abs, ref, sheet) | Returns a cell address (reference) as text, according to the specified row and column numbers. Optionally, 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) can be determined. The name of the sheet can also be specified. Row is the row number for the cell reference. Column is the column number for the cell reference (the number, not the letter). Abs determines the type of reference: 1 for column/row absolute; 2 for column relative, row absolute; 3 for column absolute, row relative; 4 for column/row relative. Ref is a Boolean value: true for A1 notation; FALSE for R1C1 notation. Sheet is the name of the sheet. View example (new window) |
| Lookup | Choose | CHOOSE(index, value1, ... value30) | Uses an index to return a value from a list of up to 30 values. 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. |
| Lookup | Column | COLUMN(reference) | 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. Reference is 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. Calc automatically sets the reference to the current cell. |
| Lookup | Columns | COLUMNS(array) | Returns the number of columns in the given reference. Array is the reference to a cell range whose total number of columns is to be found. The argument can also be a single cell. |
| Lookup | Error Type | ERROR.TYPE(reference) | Returns the number corresponding to an error value occurring in a different cell. With the aid of this number, an error message text can be generated. If an error occurs, the function returns a logical or numerical value. Reference contains the address of the cell in which the error occurs. |
| Lookup | Horizontal Lookup | HLOOKUP(search_criteria, array, index, sorted) | 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 the value in a row of the array, named in the index, in the same column. |
| Lookup | Hyperlink | HYPERLINK(URL, cell_text) | When a cell that contains the HYPERLINK function is clicked, the hyperlink opens. URL specifies the link target. The optional cell_text argument is the text displayed in the cell. If the cell_text parameter is not specified, the URL is displayed. |
| Lookup | Index | INDEX(reference, row, column, range) | Returns the content of a cell, specified by row and column number or an optional range name. Reference is a cell reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, the reference or range name must be enclosed in parentheses. Row (optional) is the row number of the reference range, for which to return a value. Column (optional) is the column number of the reference range, for which to return a value. Range (optional) is the index of the subrange if referring to a multiple range. |
| Lookup | Indirect | INDIRECT(reference) | Returns the reference specified by a text string. This function can also be used to return the area of a corresponding string. Reference is a reference to a cell or an area (in text form) for which to return the contents. |
| Lookup | Match | MATCH(search_criterion, lookup_array, type) | 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. Search_criterion is the value which is to be searched for in the single-row or single-column array. Lookup_array is the reference searched. A lookup array can be a single row or column, or part of a single row or column. Type may take the values 1, 0, or -1. This corresponds to the same function in Microsoft Excel. |
| Lookup | Offset | OFFSET(reference, rows, columns, height, width) | Returns the value of a cell offset by a certain number of rows and columns from a given reference point. Reference is the cell from which the function searches for the new reference. Rows is the number of cells by which the reference was corrected up (negative value) or down. Columns is the number of columns by which the reference was corrected to the left (negative value) or to the right. Height is the optional vertical height for an area that starts at the new reference position. Width is the optional horizontal width for an area that starts at the new reference position. |
| Lookup | Row | ROW(reference) | 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. Reference is a cell, an area, or the name of an area. If a reference is not indicated, Calc automatically sets the reference to the current cell. |
| Lookup | Rows | ROWS(array) | Returns the number of rows in a reference or array. Array is the reference or named area whose total number of rows is to be determined. |
| Lookup | Vertical Lookup | VLOOKUP(search_criterion, array, index, sort_order) | Searches vertically with reference to adjacent cells to the right. If a specific value is contained in the first column of an array, returns the value to the same line of a specific array column named by index. Search_criterion is the exact value searched for in the first column of the array. Array is the reference, which must include at least two columns. Index is the number of the column in the array that contains the value to be returned. The first column has the number 1. Sort_order (optional) indicates whether the first column in the array is sorted in ascending order. We'd love to learn how you're using the VLOOKUP function. You can now show off your finished spreadsheet by editing our knol (a knol is an authoritative article about a specific topic). |
| Math | Absolute value | ABS(number) | Returns the absolute value of the given number. View example (new window) |
| Math | Inverse cosine | ACOS(number) | Returns the inverse cosine of the given number in radians. |
| Math | Hyperbolic cosine | ACOSH(number) | Returns the inverse hyperbolic cosine of the given number in radians. |
| Math | Inverse sine | ASIN(number) | Returns the inverse sine of the given number in radians. |
| Math | Inverse hyperbolic sine | ASINH(number) | Returns the inverse hyperbolic sine of the given number in radians. |
| Math | Inverse tangent | ATAN(number) | Returns the inverse tangent of the given number in radians. |
| Math | Inverse tangent of coordinates | ATAN2(number_x, number_y) | Returns the inverse tangent of the specified x and y coordinates. Number_x is the value for the x coordinate. Number_y is the value for the y coordinate. |
| Math | Inverse hyperbolic tangent | ATANH(number) | Returns the inverse hyperbolic tangent of the given number. (Angle is returned in radians.) |
| Math | Rounds | CEILING(number, significance, mode) | Rounds the given number to the nearest integer or multiple of significance. Significance is the value to whose multiple of ten the value is to be rounded up (.01, .1, 1, 10, etc.). Mode is an optional value. If it is indicated and non-zero and if the number and significance are negative, rounding up is carried out based on that value. |
| Math | Number of combinations | COMBIN(count_1, count_2) | Returns the number of combinations for a given number of objects. Count_1 is the total number of elements. Count_2 is the selected count from the elements. This is the same as the nCr function on a calculator. |
| Math | Cosine | COS(number) | Returns the cosine of the given number (angle in radians). |
| Math | Hyperbolic cosine | COSH(number) | Returns the hyperbolic cosine of the given number (angle in radians). |
| Math | Empty cells | COUNTBLANK(range) | Returns the number of empty cells. Range is the cell range in which the empty cells are counted. |
| Math | Elements based on criteria | COUNTIF(range, criteria) | Returns the number of elements that meet certain criteria within a cell range. Range is the range to which the criteria are to be applied. Criteria indicates the criteria in the form of a number or a character string by which the cells are counted. |
| Math | Radians to degrees | DEGREES(number) | Converts the given number in radians to degrees. |
| Math | Rounds | EVEN(number) | Rounds the given number up to the nearest even integer. |
| Math | e raised | EXP(number) | Returns e raised to the power of the given number. |
| Math | Factorial | FACT(number) | Returns the factorial of the given number. |
| Math | Factorial / 2 | FACTDOUBLE(number) | Returns the factorial of the number with increments of 2. 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. |
| Math | Rounds down | FLOOR(number, significance, mode) | Rounds the given number down to the nearest multiple of significance. Significance is the value to whose multiple of ten the number is to be rounded down (.01, .1, 1, 10, etc.). Mode is an optional value. If it is indicated and non-zero and if the number and significance are negative, rounding up is carried out based on that value. |
| Math | Greatest common divisor | GCD(numbers) | Returns the greatest common divisor of one or more integers. Numbers is a list of up to 30 numbers whose greatest common divisor is to be calculated, separated by semi-colons. |
| Math | Rounds down | INT(number) | Rounds the given number down to the nearest integer. |
| Math | True if even | ISEVEN(value) | Returns TRUE if the given value is an even integer, or FALSE if the value is odd. If the value is not an integer, the function evaluates only the integer part of the value. |
| Math | True if odd | ISODD(value) | Returns TRUE if the given value is an odd integer, or FALSE if the value is even. If the value is not an integer, the function evaluates only the integer part of the value. |
| Math | Least common multiple | LCM(integer_1, integer_2, ... integer_30) | Returns the least common multiple of one or more integers. Integer_1, integer_2,... integer_30 are integers whose lowest common multiple is to be calculated. |
| Math | Logarithm | LN(number) | Returns the natural logarithm based on the constant e of the given number. |
| Math | Logarithm | LOG(number, base) | Returns the logarithm of the given number to the specified base. Base is the base for the logarithm calculation. |
| Math | base 10 Logarithm | LOG10(number) | Returns the base-10 logarithm of the given number. |
| Math | Returns remainder | MOD(dividend, divisor) | Returns the remainder after a number is divided by a divisor. Dividend is the number which will be divided by the divisor. Divisor is the number by which to divide the dividend. |
| Math | Nearest integer | MROUND(number, multiple) | The result is the nearest integer multiple of the number. |
| Math | Factorial of sum | MULTINOMIAL (number(s)) | Returns the factorial of the sum of the arguments divided by the product of the factorials of the arguments. Number(s) is a list of up to 30 numbers separated by semi-colons. |
| Math | Rounds to odd | ODD(number) | Rounds the given number up to the nearest odd integer. |
| Math | Pi | PI() | Returns the value of PI to fourteen decimal places. |
| Math | Power | POWER(base, power) | Returns the result of a number raised to a power. Base is the number that is to be raised to the given power. Power is the exponent by which the base is to be raised. |
| Math | Product | PRODUCT(number 1 to 30) | Multiplies all the numbers given as arguments and returns the product. Number 1 to number 30 are up to 30 arguments whose product is to be calculated, separated by semi-colons. |
| Math | Integer result | QUOTIENT(numerator, denominator) | Returns the integer result of a division operation. Numerator is the number that will be divided. Denominator is the number the numerator will be divided by. |
| Math | Degrees to radians | RADIANS(number) | Converts the given number in degrees to radians. |
| Math | Random number | RAND() | Returns a random number between 0 and 1. |
| Math | Random number | RANDBETWEEN (bottom, top) | Returns an integer random number between bottom and top (inclusive). |
| Math | Rounds | ROUND(number, count) | Rounds the given number to a certain number of decimal places according to valid mathematical criteria. Count (optional) is the number of the places to which the value is to be rounded. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count. |
| Math | Rounds down | ROUNDDOWN(number, count) | Rounds the given number. Count (optional) is the number of digits to be rounded down to. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count. |
| Math | Rounds up | ROUNDUP(number, count) | Rounds the given number up. Count (optional) is the number of digits to which rounding up is to be done. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count. |
| Math | Sum of powers | SERIESSUM(x, n, m, coefficients) | 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). x is the number as an independent variable. n is the starting power. m is the increment. Coefficients is a series of coefficients. For each coefficient the series sum is extended by one section. You can only enter coefficients using cell references. |
| Math | Sign | SIGN(number) | Returns the sign of the given number. The function returns the result 1 for a positive sign, -1 for a negative sign, and 0 for zero. |
| Math | Sine | SIN(number) | Returns the sine of the given number (angle in radians). |
| Math | Hyperbolic sine | SINH(number) | Returns the hyperbolic sine of the given number (angle in radians). |
| Math | Positive square root | SQRT(number) | Returns the positive square root of the given number. The value of the number must be positive. |
| Math | Square root | SQRTPI(number) | Returns the square root of the product of the given number and PI. |
| Math | Sum | SUM(number_1, number_2, ... number_30) | Adds all the numbers in a range of cells. Number_1, number_2,... number_30 are up to 30 arguments whose sum is to be calculated. You can also enter a range using cell references. |
| Math | Sum based on criteria | SUMIF(range, criteria, sum_range) | Adds the cells specified by a given criteria. Range is the range to which the criteria are to be applied. Criteria is the cell in which the search criterion is shown, or the search criterion itself. Sum_range is the range from which values are summed, if it has not been indicated, the values found in the Range are summed. |
| Math | Sum of squares | SUMSQ(number_1, number_2, ... number_30) | Calculates the sum of the squares of numbers (totaling up of the squares of the arguments) Number_1, number_2,... number_30 are up to 30 arguments, the sum of whose squares is to be calculated. |
| Math | Tangent | TAN(number) | Returns the tangent of the given number (angle in radians). |
| Math | Hyperbolic tangent | TANH(number) | Returns the hyperbolic tangent of the given number (angle in radians). |
| Math | Truncates | TRUNC(number, count) | Truncates a number to an integer by removing the fractional part of the number according to the precision specified in Tools > Options > OpenOffice.org Calc > Calculate. Number is the number whose decimal places are to be cut off. Count is the number of decimal places which are not cut off. |
| Operator | Addition | ADD(number_1, number_2) | Adds number_1 and number_2. Equivalent to + operator View example (new window) |
| Operator | Concatenation | CONCAT(value_1, value_2) | Concatenates value_1 and value_2. Equivalent to & operator. |
| Operator | Division | DIVIDE(number_1, number_2) | Divides number_1 by number_2. Equivalent to / operator |
| Operator | Equal | EQ(value_1, value_2) | Returns TRUE if value_1 is equal to value_2, otherwise returns FALSE. Equivalent to == operator. |
| Operator | Greater than | GT(value_1, value_2) | Returns TRUE if value_1 is greater than value_2, otherwise returns FALSE. Equivalent to > operator. |
| Operator | Greater than or equal to | GTE(value_1, value_2) | Returns TRUE if value_1 is greater than or equal to value_2, otherwise returns FALSE. Equivalent to >= operator. |
| Operator | Less than | LT(value_1, value_2) | Returns TRUE if value_1 is less than value_2, otherwise returns FALSE. Equivalent to < operator. |
| Operator | Less than or equal to | LTE(value_1, value_2) | Returns TRUE if value_1 is less than or equal to value_2, otherwise returns FALSE. Equivalent to <= operator. |
| Operator | Subtraction | MINUS(number_1, number_2) | Subtracts number_2 from number_1. Equivalent to - operator. |
| Operator | Multiplication | MULTIPLY(number_1, number_2) | Multiplies number_1 by number_2. Equivalent to * operator. |
| Operator | Not Equal | NE(value_1, value_2) | Returns TRUE if value_1 is not equal to value_2, otherwise returns FALSE. Equivalent to != operator. |
| Operator | Power | POW(base, power) | Returns the result of a number raised to a power. Base is the number that is to be raised to the given power. Power is the exponent by which the base is to be raised. Equivalent to ^ operator. |
| Statistical | Average | AVEDEV(number1, number2, ... number_30) | Returns the average of the absolute deviations of data points from their mean. Displays the diffusion in a data set. Number_1, number_2, ... number_30 are values or ranges that represent a sample. Each number can also be replaced by a reference. View example (new window) |
| Statistical | Average | AVERAGE(number_1, number_2, ... number_30) | Returns the average of the arguments. Number_1, number_2, ... number_30 are numerical values or ranges. Text is ignored. |
| Statistical | Average | AVERAGEA(value_1, value_2, ... value_30) | Returns the average of the arguments. The value of a text is 0. Value_1, value_2, ... value_30 are values or ranges. |
| Statistical | Binomial distribution | BINOMDIST(X, trials, SP, C) | Returns the individual term binomial distribution probability. 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. |
| Statistical | Confidence interval | CONFIDENCE(alpha, STDEV, size) | Returns the (1-alpha) confidence interval for a normal distribution. Alpha is the level of the confidence interval. STDEV is the standard deviation for the total population. Size is the size of the total population. |
| Statistical | Correlation coefficient | CORREL(data_1, data_2) | Returns the correlation coefficient between two data sets. Data_1 is the first data set. Data_2 is the second data set. |
| Statistical | Count | COUNT(value_1, value_2, ... value_30) | Counts how many numbers are in the list of arguments. Text entries are ignored. Value_1, value_2, ... value_30 are values or ranges which are to be counted. |
| Statistical | Count | COUNTA(value_1, value_2, ... value_30) | 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. value_1, value_2, ... value_30 are up to 30 arguments representing the values to be counted. |
| Statistical | Covariance | COVAR(data_1, data_2) | Returns the covariance of the product of paired deviations. Data_1 is the first data set. Data_2 is the second data set. |
| Statistical | Smallest value | CRITBINOM(trials, SP, alpha) | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value. Trials is the total number of trials. SP is the probability of success for one trial. Alpha is the threshold probability to be reached or exceeded. |
| Statistical | Sum of squares | DEVSQ(number_1, number_2, ... number_30) | Returns the sum of squares of deviations based on a sample mean. Number_1, number_2, ... number_30 are numerical values or ranges representing a sample. |
| Statistical | Exponential distribution | EXPONDIST(number, lambda, C) | Returns the exponential distribution. Number is the value of the function. Lambda is the parameter value. C is a logical value that determines the form of the function. C = 0 calculates the density function, and C = 1 calculates the distribution. |
| Statistical | Fisher transformation | FISHER(number) | Returns the Fisher transformation for the given number and creates a function close to a normal distribution. |
| Statistical | Inverse of Fisher transformation | FISHERINV(number) | Returns the inverse of the Fisher transformation for the given number and creates a function close to a normal distribution. |
| Statistical | Future values | FORECAST(value, data_Y, data_X) | Extrapolates future values based on existing x and y values. Value is the x value, for which the y value of the linear regression is to be returned. Data_Y is the array or range of known y's. Data_X is the array or range of known x's. Does not work for exponential functions. |
| Statistical | Geometric mean | GEOMEAN(number_1, number_2, ... number_30) | Returns the geometric mean of a sample. Number_1, number_2, ... number_30 are numerical arguments or ranges that represent a random sample. |
| Statistical | Harmonic mean | HARMEAN(number_1, number_2, ... number_30) | Returns the harmonic mean of a data set. Number_1, number_2, ... number_30 are values or ranges that can be used to calculate the harmonic mean. |
| Statistical | Hypergeometric distribution | HYPGEOMDIST(X, n_sample, successes, n_population) | Returns the hypergeometric distribution. X is the number of results achieved in the random sample. N_sample is the size of the random sample. Successes is the number of possible results in the total population. N_population is the size of the total population. |
| Statistical | Intersect | INTERCEPT(data_Y, data_X) | Calculates the y-value at which a line will intersect the y-axis by using known x-values and y-values. Data_Y is the dependent set of observations or data. Data_X is the independent set of observations or data. Names, arrays or references containing numbers must be used here. Numbers can also be entered directly. |
| Statistical | Kurtorsis | KURT(number_1, number_2, ... number_30) | Returns the kurtosis of a data set (at least 4 values required). Number_1, number_2, ... number_30 are numerical arguments or ranges representing a random sample of distribution. |
| Statistical | Rank | LARGE(data, rank_c) | Returns the Rank_c-th largest value in a data set. Data is the cell range of data. Rank_c is the ranking of the value (2nd largest, 3rd largest, etc.) written as an integer. |
| Statistical | Inverse of lognormal distribution | LOGINV(number, mean, STDEV) | Returns the inverse of the lognormal distribution for the given Number, a probability value. Mean is the arithmetic mean of the standard logarithmic distribution. STDEV is the standard deviation of the standard logarithmic distribution. |
| Statistical | Cumulative lognormal | LOGNORMDIST(number, mean, STDEV) | Returns the cumulative lognormal distribution for the given Number, a probability value. Mean is the mean value of the standard logarithmic distribution. STDEV is the standard deviation of the standard logarithmic distribution. |
| Statistical | Maximum value | MAX(number_1, number_2, ... number_30) | Returns the maximum value in a list of arguments. Number_1, number_2, ... number_30 are numerical values or ranges. |
| Statistical | Maximum value | MAXA(value_1, value_2, ... value_30) | Returns the maximum value in a list of arguments. Unlike MAX, text can be entered. The value of the text is 0. Value_1, value_2, ... value_30 are values or ranges. |
| Statistical | Median | MEDIAN(number_1, number_2, ... number_30) | Returns the median of a set of numbers. Number_1, number_2, ... number_30 are values or ranges, which represent a sample. Each number can also be replaced by a reference. |
| Statistical | Minimum | MIN(number_1, number_2, ... number_30) | Returns the minimum value in a list of arguments. Number_1, number_2, ... number_30 are numerical values or ranges. |
| Statistical | Minimum | MINA(value_1, value_2, ... value_30) | Returns the minimum value in a list of arguments. Here text can also be entered. The value of the text is 0. Value_1, value_2, ... value_30 are values or ranges. |
| Statistical | Most common value | MODE(number_1, number_2, ... number_30) | Returns the most common value in a data set. Number_1, number_2, ... number_30 are numerical values or ranges. If several values have the same frequency, it returns the smallest value. An error occurs when a value does not appear twice. |
| Statistical | Negative binomial distribution | NEGBINOMDIST(X, R, SP) | Returns the negative binomial distribution. X is the value returned for unsuccessful tests. R is the value returned for successful tests. SP is the probability of the success of an attempt. |
| Statistical | Normal distribution | NORMDIST(number, mean, STDEV, C) | Returns the normal distribution for the given Number in the distribution. Mean is the mean value of the distribution. STDEV is the standard deviation of the distribution. C = 0 calculates the density function, and C = 1 calculates the distribution. |
| Statistical | Inverse of normal distribution | NORMINV(number, mean, STDEV) | Returns the inverse of the normal distribution for the given Number in the distribution. Mean is the mean value in the normal distribution. STDEV is the standard deviation of the normal distribution. |
| Statistical | Standard normal cumulative distribution | NORMSDIST(number) | Returns the standard normal cumulative distribution for the given Number. |
| Statistical | Inverse of standard normal distribution | NORMSINV(number) | Returns the inverse of the standard normal distribution for the given Number, a probability value. |
| Statistical | Pearson product | PEARSON(data_1, data_2) | Returns the Pearson product moment correlation coefficient r. Data_1 is the array of the first data set. Data_2 is the array of the second data set. |
| Statistical | Alpha percentile | PERCENTILE(data, alpha) | Returns the alpha-percentile of data values in an array. Data is the array of data. Alpha is the percentage of the scale between 0 and 1. |
| Statistical | Percentage rank | PERCENTRANK(data, value) | Returns the percentage rank (percentile) of the given value in a sample. Data is the array of data in the sample. |
| Statistical | Number of permutations | PERMUT(count_1, count_2) | Returns the number of permutations for a given number of objects. Count_1 is the total number of objects. Count_2 is the number of objects in each permutation. |
| Statistical | Poisson distribution | POISSON(number, mean, C) | Returns the Poisson distribution for the given Number. Mean is the middle value of the Poisson distribution. C = 0 calculates the density function, and C = 1 calculates the distribution. |
| Statistical | Probability | PROB(data, probability, start, end) | Returns the probability that values in a range are between two limits. Data is the array or range of data in the sample. Probability is the array or range of the corresponding probabilities. Start is the start value of the interval whose probabilities are to be summed. End (optional) is 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. |
| Statistical | Quartile of data set | QUARTILE(data, type) | Returns the quartile of a data set. Data is the array of data in the sample. Type is the type of quartile. (0 = Min, 1 = 25%, 2 = 50% (Median), 3 = 75% and 4 = Max.) |
| Statistical | Rank | RANK(value, data, type) | Returns the rank of the given Value in a sample. Data is the array or range of data in the sample. Type (optional) is the sequence order, either ascending (0) or descending (1). |
| Statistical | Square of Pearson correlation | RSQ(data_Y, data_X) | Returns the square of the Pearson correlation coefficient based on the given values. Data_Y is an array or range of data points. Data_X is an array or range of data points. |
| Statistical | Skewness of distribution | SKEW(number_1, number_2, ... number_30) | Returns the skewness of a distribution. Number_1, number_2, ... number_30 are numerical values or ranges. |
| Statistical | Slope | SLOPE(data_Y, data_X) | Returns the slope of the linear regression line. Data_Y is the array or matrix of Y data. Data_X is the array or matrix of X data. |
| Statistical | Rank | SMALL(data, rank_c) | Returns the Rank_c-th smallest value in a data set. Data is the cell range of data. Rank_c is the rank of the value (2nd smallest, 3rd smallest, etc.) written as an integer. |
| Statistical | Random variable | STANDARDIZE(number, mean, STDEV) | Converts a random variable to a normalized value. Number is the value to be standardized. Mean is the arithmetic mean of the distribution. STDEV is the standard deviation of the distribution. |
| Statistical | Standard deviation | STDEV(number_1, number_2, ... number_30) | Estimates the standard deviation based on a sample. Number_1, number_2, ... number_30 are numerical values or ranges representing a sample based on an entire population. |
| Statistical | Standard deviation | STDEVA(value_1, value_2, ... value_30) | Calculates the standard deviation of an estimation based on a sample. Value_1, value_2, ... value_30 are values or ranges representing a sample derived from an entire population. Text has the value 0. |
| Statistical | Standard deviation | STDEVP(number_1, number_2, ... number_30) | Calculates the standard deviation based on the entire population. Number_1, number_2, ... number_30 are numerical values or ranges representing a sample based on an entire population. |
| Statistical | Standard deviation | STDEVPA(value_1, value_2, ... value_30) | Calculates the standard deviation based on the entire population. Value_1, value_2, ... value_30 are values or ranges representing a sample derived from an entire population. Text has the value 0. |
| Statistical | Standard error | STEYX(data_Y, data_X) | Returns the standard error of the predicted y value for each x in the regression. Data_Y is the array or matrix of Y data. Data_X is the array or matrix of X data. |
| Statistical | Mean | TRIMMEAN(data, alpha) | Returns the mean of a data set without the Alpha proportion of data at the margins. Data is the array of data in the sample. Alpha is the proportion of the marginal data that will not be taken into consideration. |
| Statistical | Variance | VAR(number_1, number_2, ... number_30) | Estimates the variance based on a sample. Number_1, number_2, ... number_30 are numerical values or ranges representing a sample based on an entire population. |
| Statistical | Variance | VARA(value_1, value_2, ... value_30) | Estimates a variance based on a sample. The value of text is 0. Value_1, value_2, ... value_30 are values or ranges representing a sample derived from an entire population. Text has the value 0. |
| Statistical | Variance | VARP(Number_1, number_2, ... number_30) | Calculates a variance based on the entire population. Number_1, number_2, ... number_30 are numerical values or ranges representing an entire population. |
| Statistical | Variance | VARPA(value_1, value_2, .. .value_30) | Calculates the variance based on the entire population. The value of text is 0. Value_1, value_2, ... value_30 are values or ranges representing an entire population. |
| Statistical | Weibull distribution | WEIBULL(number, alpha, beta, C) | Returns the values of the Weibull distribution for the given Number. Alpha is the Alpha parameter of the Weibull distribution. Beta is the Beta parameter of the Weibull distribution. C indicates the type of function: C= 0 the form of the function is calculated, C=1 the distribution is calculated. |
| Statistical | Z-test | ZTEST(data, number, sigma) | Returns the two-tailed P value of a z test with standard distribution. Data is the array of the data. Number is the value to be tested. Sigma (optional) is the standard deviation of the total population. If this argument is missing, the standard deviation of the sample is processed. |
| Text | Number to character | CHAR(number) | Converts a number into a character according to the current code table. The number can be a two-digit or three-digit integer number. Number is a number between 1 and 255 representing the code value for the character. View example (new window) |
| Text | Numeric code | CODE(text) | Returns a numeric code for the first character in a text string. Text is the text for which the code of the first character is to be found. |
| Text | Combines text strings | CONCATENATE(text_1, text_2, ..., text_30) | Combines several text strings into one string. Text_1, text_2, ... text_30 are text passages that are to be combined into one string. |
| Text | Dollar | DOLLAR(value, decimals) | Converts a number to an amount in the currency format, rounded to a specified decimal place. Value is the number to be converted to currency, it can be a number, a reference to a cell containing a number, or a formula which returns a number. Decimals (optional) is the number of decimal places. If no decimals value is specified, all numbers in currency format will be displayed with two decimal places. The currency format is set in the system settings. |
| Text | Exact Comparison | EXACT(text_1, text_2) | Compares two text strings and returns TRUE if they are identical. This function is case-sensitive. Text_1 is the first text to compare. Text_2 is the second text to compare. |
| Text | Find | FIND(find_text, text, position) | Looks for a string of text within another string. Where to begin the search can also be defined. The search term can be a number or any string of characters. The search is case-sensitive. Find_text is the text to be found. Text is the text where the search takes place. Position (optional) is the position in the text from which the search starts. |
| Text | Fixed number | FIXED(number, decimals, no_thousands_separator) | 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. Number is the number to be formatted. Decimals is the number of decimal places to be displayed. No_thousands_separator (optional) determines whether the thousands separator is used or not. 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 the current locale setting are displayed. |
| Text | Left | LEFT(text, number) | Returns the first character or characters in a text string. Text is the text where the initial partial words are to be determined. Number (optional) is the number of characters for the start text. If this parameter is not defined, one character is returned. |
| Text | Length | LEN(text) | Returns the length of a string including spaces. Text is the text whose length is to be determined. |
| Text | Lowercase | LOWER(text) | Converts all uppercase letters in a text string to lowercase. Text is the text to be converted. |
| Text | Text segment | MID(text, start, number) | Returns a text segment of a character string. The parameters specify the starting position and the number of characters. Text is the text containing the characters to extract. Start is the position of the first character in the text to extract. Number is the number of characters in the part of the text. |
| Text | Capitalize | PROPER(text) | Capitalizes the first letter in all words of a text string. Text refers to the text to be converted. |
| Text | Replace | REPLACE(text, position, length, new_text) | 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. To perform further calculations with a number which has been replaced by text, convert it back to a number using the VALUE function. Any text containing numbers must be enclosed in quotation marks so it is not interpreted as a number and automatically converted to text. Text is text of which a part will be replaced. Position is the position within the text where the replacement will begin. Length is the number of characters in text to be replaced. New_text is the text which replaces text. |
| Text | Repeats | REPT(text, number) | Repeats a character string by the given number of copies. Text is the text to be repeated. Number is the number of repetitions. The result can be a maximum of 255 characters. |
| Text | Defines characters | RIGHT(text, number) | Defines the last character or characters in a text string. Text is the text of which the right part is to be determined. Number (optional) is the number of characters from the right part of the text. |
| Text | Search | SEARCH(find_text, text, position) | Returns the position of a text segment within a character string. The start of the search can be set as an option. The search text can be a number or any sequence of characters. The search is not case-sensitive. Find_text is the text to be searched for. Text is the text where the search will take place. Position (optional) is the position in the text where the search is to start. |
| Text | Split text | SPLIT(string, delimiter) | Splits text based on the given delimiter, putting each section into a separate column in the row. Full documentation can be found at http://documents.google.com/support/spreadsheets/bin/answer.py?answer=105612 |
| Text | Substitute | SUBSTITUTE(text, search_text, new text, occurrence) | Substitutes new text for old text in a string. Text is the text in which text segments are to be exchanged. Search_text is the text segment that is to be replaced (a number of times). New text is 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. |
| Text | Number to blank text string | T(value) | Converts a number to a blank text string. Value is 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. |
| Text | Number into text | TEXT(number, format) | Converts a number into text according to a given format. Number is the numerical value to be converted. Format is the text which defines the format. Use decimal and thousands separators according to the language set in the cell format. |
| Text | Removes spaces | TRIM(text) | Removes spaces that are in front of a string, or aligns cell contents to the left. Text refers to text in which leading spaces are removed, or to the cell in which the contents will be left-aligned. |
| Text | Converts to uppercase | UPPER(text) | Converts the string specified in the text parameter to uppercase. Text refers to the lower case letters you want to convert to upper case. |
| Text | Text string to number | VALUE(text) | Converts a text string into a number. Text is the text to be converted to a number. |
About this documentation:
Google Spreadsheets provides support for cell formulas consistent with those used in traditional and popular desktop spreadsheet packages. A powerful part of these formulas are the functions which may be included to manipulate data and calculate results using strings and numbers which could be the content of other cells. The following is a list of all the supported functions within each major category.
This document contains modified content from Appendix B of the OpenOffice.org "Calc Guide," available at http://documentation.openoffice.org/manuals/oooauthors2/0314CG-DescriptionOfFunctions.pdf. This content is licensed under the Creative Commons Attribution License, version 2.0, available at http://creativecommons.org/licenses/by/2.0/legalcode.
The copyright notice found in the OpenOffice.org "Calc Guide" is included in its complete form below.
Copyright
This document is Copyright © 2005 by its contributors as listed in the section titled Authors. You can distribute it and/or modify it under the terms of either the GNU General Public License, version 2 or later (http://www.gnu.org/licenses/gpl.html), or the Creative Commons Attribution License, version 2.0 or later (http://creativecommons.org/licenses/by/2.0/).
All trademarks within this guide belong to their legitimate owners.
Authors
Magnus Adielsson
Richard Barnes
Peter Kupfer
Iain Roberts
Jean Hollis Weber
Notes on financial functions
Accounting systems vary in the number of days in a month or a year used in calculations. The following table gives the integers used for the basis parameter used in some of the financial analysis functions.
Table 1: Basis calculation types
| 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. |
Notes on date functions
Google Spreadsheets handles and computes date/time values as numbers. When you assign a numeric format (such as "1,000 Rounded") to a date or time value, it is displayed as a number. To get the date value, format the cell as the desired date format (such as "3/24/2006 Date").
Notes on function examples
Many of our function examples are provided by our user base. If you would like to contribute examples for functions, please visit the Google Docs help forum.