Built-in Operators and Functions
- 59 minutes to read
The ExpressSpreadSheet formula engine provides multiple built-in Microsoft Excel-compatible operators and functions that you can use in various formula expressions to:
Perform calculations on cell data in spreadsheet documents displayed by the Spreadsheet and Report Designer controls;
Define specific cell formatting criteria in the “Expression“ and “Cell Is“ conditional formatting rules applied to cell ranges in a Table View worksheet or dataset fields displayed by data source presentation controls.
All logical and arithmetic operators perform basic operations on one or two operands while most functions perform more sophisticated calculations on multiple values. You can override any built-in function with a custom implementation or register entirely new functions in the functions repository. All built-in operators and functions are split up into the following categories according to their common focus:
Arithmetic Operators
Logical Operators
Lookup and Reference Functions
Arithmetic Functions
Financial Functions
Date and Time Functions
Text Functions
Report Functions
Arithmetic Operators
Arithmetic operators allow you to perform basic arithmetic operations (i.e., addition, subtraction, multiplication, division, and exponential functions).
| Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
| + | Two parameters | = 5 + B1 | Adds the first parameter’s value to the value of the second parameter. |
| - | Two parameters | = A5 - B1 | Subtracts the second parameter’s value from the value of the first one. |
| * | Two parameters | = A2 * A3 | Multiplies the first parameter’s value by the value of the second parameter. |
| / | Two parameters | = A1 / PI() | Divides the first parameter’s value by the value of the second parameter. |
| ^ | Two parameters | = B1 ^ A2 | Raises the first parameter’s value to the power determined by the second parameter’s value. |
Logical operators
Logical operators perform comparison operations and return a logical value (TRUE or FALSE) as a result.
| Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
| = | Two parameters of logical types | = A1=A2 | The equality operation. Returns TRUE if the first parameter is equal to the second one. Otherwise, returns FALSE. |
| <> | Two parameters of logical types | = A1<>B2 | The inequality operation. Returns TRUE if values within the list are not equal. Otherwise, returns FALSE. |
| < | Two parameters of logical types | = A1<B1 | The “Less than” operation. Returns TRUE if the first parameter is less than the second one. Otherwise, returns FALSE. |
| > | Two parameters of logical types | = A1>100 | The “Greater than” operation. Returns TRUE if the first parameter is greater than the second one. Otherwise, returns FALSE. |
| <= | Two parameters of logical types | = A2<=C4 | The “Less than or equal to” operation. Returns TRUE if the first parameter is less than or equal to the second one. Otherwise, returns FALSE. |
| >= | Two parameters of logical types | = A2>=1.57 | The “Greater than or equal to” operation. Returns TRUE if the first parameter is greater than or equal to the second one. Otherwise, returns FALSE. |
Lookup and Reference Functions
A set of reference and lookup functions.
Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
|
ADDRESS | Three parameters: the numeric value that specifies the row number to use in the cell reference, the numeric value that specifies the column number to use in the cell reference. Optionally, you can add a numeric value that specifies the type of reference to return. | =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) | Returns a reference as text to a single cell in a worksheet. You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers. For instance, ADDRESS(2, 3) returns $C$2. As another example, ADDRESS(77, 300) returns $KN$77. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number parameters to the ADDRESS function. |
|
AREAS | The reference parameter is mandatory. It is a reference to a cell or cell range and can refer to multiple areas. If you want to specify several references as a single parameter, include extra sets of parentheses so the ExpressSpreadSheet formula engine will not interpret the comma as a field separator. | =AREAS(reference) | Returns the number of areas in a reference. An area is a range of continuous cells or a single cell. For instance, the AREAS(B2:D4) expression returns 1. As another example, AREAS((B2:D4,E5,F6:I9)) returns 3. |
|
CHOOSE | Two mandatory parameters. The index_num parameter specifies which value parameter is selected. The index_num value must be within the range of 1 to 254, or a formula expression or cell reference returning a value within the same range. The value1 parameter is also mandatory while subsequent parameters are optional. You can specify up to 254 value parameters from which the CHOOSE function selects a value or an action to perform based on the index_num parameter value. The actual parameters can be numbers, cell references, defined names, formulas, functions, or text. | =CHOOSE(index_num, value1, [value2], …) | Returns a value from the list of value parameters. You can use the CHOOSE function to select one of the specified values based on the index number. If index_num is 1, CHOOSE returns value1. If the index value is 2, the CHOOSE function returns value 2, and so on. If the index_num value is less than 1 or greater than the number of the last provided value, the CHOOSE function returns the #VALUE! error value. If the index_num parameter value is a fraction, it is truncated to the lowest integer before evaluation. The value parameters can be cell range references as well as single values. |
|
COLUMN | The reference parameter is optional. If it is omitted or refers to a range of cells, and if the COLUMN function is entered as a horizontal array formula, the COLUMN function returns the column numbers of reference as a horizontal array. | =COLUMN([reference]) | Returns the column number of a reference. For instance, the formula =COLUMN(D10) returns 4 since column D is the fourth column. If the reference parameter is a range of cells, and if the COLUMN function is not entered as a horizontal array formula, the COLUMN function returns the number of the leftmost column. If the reference parameter is omitted, it is assumed to be the reference of the cell in which the COLUMN function appears. The reference parameter cannot refer to multiple areas. |
|
COLUMNS | The array parameter is mandatory. It specifies an array or array formula, or a reference to a range of cells for which you want the number of columns. | =COLUMNS(array) | Returns the number of columns in a reference. |
|
FORMULATEXT | The reference parameter is mandatory; it specifies a reference to a cell or range of cells. | =FORMULATEXT(reference) | Returns the formula at the given reference as text. The reference parameter can be to another worksheet or workbook. If the reference parameter is to another workbook that is not open, FORMULATEXT returns the #N/A error value. If the reference parameter is to an entire row or column, or to a range or defined name containing more than one cell, FORMULATEXT returns the value in the upper leftmost cell or the row, column, or range. In the following cases, FORMULATEXT returns the #N/A error value:
Invalid data types used as inputs will produce a #VALUE error value. |
|
HLOOKUP | Three mandatory parameters: the value to be found in the first row of the table, the table of information in which data is looked up, and the row number in table_array which the matching value will be returned. You can optionally add a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Looks in the top row of an array and returns the value of the initiated cell. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. If HLOOKUP can’t find lookup_value, the range_lookup is TRUE, it uses the largest value that is less than lookup_value. If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value. If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. |
|
INDEX | Array form: Two mandatory parameters. The array parameter specifies a cell range or an array constant. If the specified array contains only one row or column, the corresponding row_num or column_num parameter is optional. If the array has more than one row and more than one column, and only the row_num or column_num parameter is provided, the INDEX function returns an array of the entire row or column in array. The mandatory row_num parameter specifies the row in the array from which the INDEX function should return a value. If the row_num parameter is omitted, the column_num parameter is required instead. The optional column_num parameter specifies the column in the array from which the INDEX function should return a value. If the column_num parameter is omitted, the row_num parameter is required instead. Reference form: Two mandatory parameters. The reference parameter specifies a reference to one or more cell ranges. If you are entering a nonadjacent cell range for the reference, enclose the reference in parentheses. If each area in the reference parameter contains only one row or column, the row_num or column_num parameter becomes optional, respectively. The mandatory row_num parameter specifies the number of the row in the reference from which the INDEX function returns a reference. The optional column_num parameter specifies the number of the column in the reference from which the INDEX function returns a reference. The optional area_num parameter specifies a range in the reference from which the INDEX function returns the intersection of row_num and column_num. The first selected area is numbered 1, the second is 2, and so on. If the area_num parameter is omitted, the INDEX function uses 1 as the area value. | Array form: =INDEX(array, row_num, [column_num]) Reference form: =INDEX(reference, row_num, [column_num], [area_num]) | Returns a value or the reference to a value from within a table or cell range. There are two forms of the INDEX function: the array form and the reference form. Array form: Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array from if the first parameter of the INDEX function is an array constant. If both the row_num and column_num parameters are used, the INDEX function returns the value in the cell at the intersection of row_num and column_num. If you set row_num or column_num to 0 (zero), the INDEX function returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. In order to enter the INDEX function as an array formula, press the Ctrl+Shift+Enter key combination. The row_num and column_num parameter values must point to a cell within the array. Otherwise, the INDEX function returns the #REF! error value. Reference form: Returns the reference of the cell at the intersection of a particular row and column. If the reference consists of nonadjacent cell ranges, you can pick a particular range. After the reference and area_num values have been used to select a particular range, the row_num and column_num values are used to select a particular cell. The row_num value set to 1 corresponds to the first row in the range, the column_num value set to 1 corresponds to the first column, and so on. The reference returned by the INDEX function is the intersection of the row and column whose numbers are specified as the row_num and column_num parameters. If you set the row_num or column_num parameter to 0 (zero), the INDEX function returns the reference for the entire column or row, respectively. The row_num, column_num, and area_num parameter values must point to a cell within the reference. Otherwise, the INDEX function returns the #REF! error value. If the row_num and column_num parameters are omitted, the INDEX function returns the area in the reference specified by the area_num parameter. The result of the INDEX function is a reference and can be used as a reference in other formula expressions. |
|
INDIRECT | One mandatory parameter: the reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If the a1 parameter is set to TRUE or omitted, the ref_text value is interpreted as an A1-style reference. If the a1 parameter is set to FALSE, the ref_text value is interpreted as an R1C1-style reference. | =INDIRECT(ref_text, a1) | Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use the INDIRECT function when you need to change the reference to a cell within a formula without changing the formula itself. If the ref_text parameter contains no valid cell reference, the INDIRECT function returns the #REF! error value. |
|
LOOKUP | Vector form: Two obligatory parameters: the value that LOOKUP searches for in the first vector, and the range that contains only one row or one column. Additionally, you can specify a range that contains only one row or column (result_vector). Array form: Two obligatory parameters: the value that LOOKUP searches for in the array and the range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. | Vector form: =LOOKUP(lookup_value, lookup_vector, [result_vector]) Array form: =LOOKUP(lookup_value, array) | Looks up values in a vector or array. The LOOKUP function returns a value either form a one-row or one-column range or form an array. The LOOKUP function has two syntax forms: the vector form and the array form. Vector form: A vector is a range of only one row or one column. LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use the LOOKUP function when you want to specify the range that contains the values that you want to match. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value. Array form: The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to match are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row. In general, it is best to use the HLOOKUP or VLOOKUP function instead of the array form of LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs. |
|
MATCH | Two obligatory parameters: the value that you want to match in lookup_array, and the range of cells being searched. Additionally, you can specify the match_type which can be the number -1, 0, or 1. The match_type specifies how the ExpressSpreadSheet matches lookup_value with values in lookup_array. The default value for this parameter is 1. If match_type is 1 or omitted, MATCH finds the largest value that is less than or equal to lookup_value. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. | =MATCH(lookup_value, lookup_array, [match_type]) | Looks up values in a reference or array. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. MATCH returns the position of the matched value within lookup_array, not the value itself. For instance, MATCH(“b”,{“a”,”b”,”c”},0) returns 2 which is the relative position of “b” within the array {“a”,”b”,”c”}. MATCH does not distinguish between uppercase and lowercase letters when matching text values. If MATCH is unsuccessful in finding a match, it returns the #N/A error value. If match_type is 0, and lookup_value is a text string, you can use the wildcard characters – the question mark (?) and asterisk (*) – in the lookup_value parameter. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. |
|
OFFSET | Accepts three obligatory parameters: the reference from which you want to base the offset, the number of rows, up or down that you want the upper-left cell to refer to, and the number of columns to the left or right that you want the upper-left cell of the result to refer to. Additionally, you can specify the height and width of the returned reference. Both the height and width must be positive. | =OFFSET(reference, rows, cols, [height], [width]) | Returns a reference to a range that is the specified number of rows and columns from a cell or cell range. The returned reference can be a single cell or cell range. You can specify the number of returned rows and/or columns. If the offset reference is outside the worksheet’s boundaries, the OFFSET function returns the #REF! error value. If the height or width parameter is omitted, the returned cell range has the same height or width as the specified reference. The OFFSET function does not actually move any cell or change the selection while returning a reference. You can use OFFSET with any function expecting a reference parameter. For example, the formula expression SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a range that is three rows in height and one column width that is 1 row below and 2 columns to the right of the C2 cell. |
|
ROW | The reference parameter is optional. If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears. If reference is a range of cells, and if ROW is entered as a vertical array, ROW returns the row numbers of reference as a vertical array. Reference cannot refer to multiple areas. | =ROW([reference]) | Returns the row number of a reference. |
|
ROWS | The array parameter is mandatory. An array, an array formula, or a reference to a range of cells for which you want the number of rows. | =ROWS(array) | Returns the number of rows in a reference or array. |
|
TRANSPOSE | One obligatory parameter: an array or range of cells within a worksheet that you want to transpose. The transposition of an array is created by using the first column of the new array, the second row of the array as the second column of the new array, and so on. | =TRANSPOSE(array) | Returns a vertical range of cells as a horizontal range, and vice versa. You must enter the TRANSPOSE function as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows. Use TRANSPOSE to swap the vertical and horizontal orientation of an array or range within a worksheet. |
|
VLOOKUP | Three obligatory parameters: the value to search in the first column of the table or range, the range of cells that contains the data, and the column number in the table_array parameter from which the matching value must be returned. Additionally, you can specify the range_lookup that switches the VLOOKUP finding between an exact match and an approximate match. | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Looks in the first column of an array and moves across the row to return the value of a cell. You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in columns to the left of the data that you want to find. When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value. If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters – the question mark (?) and asterisk (*) – in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. |
Arithmetic functions
A set of mathematic and trigonometric functions, including basic arithmetic, conditional sums and products, and trigonometric ratios. Mathematicians may also find the Statistical Functions useful.
Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
|
ABS | One parameter of double type. | =ABS(B6) | Returns the absolute value. |
|
ACOS | One parameter of double type. | =ACOS(B16) | Returns the arccosine. |
|
ACOSH | One parameter of double type. | =ACOSH(A1) | Returns the inverse hyperbolic cosine. |
|
ACOT | One parameter of double type. | =ACOT(A1) | Returns the principal value of the arccotangent, or inverse cotangent, of a number. The returned angle is given in radians in the range 0 (zero) to pi. |
|
ACOTH | One parameter of double type. The absolute value of the parameter must be greater than 1. | =ACOTH(A1) | Returns the inverse hyperbolic cotangent of a number. If the absolute value of the parameter is less than 1, the ACOT function returns the #VALUE! error value. |
|
ASIN | One parameter of double type. | =ASIN(B6) | Returns the arcsine. |
|
ASINH | One parameter of double type. | =ASINH(C2) | Returns the inverse hyperbolic sine of the parameter. |
|
ATAN | One parameter of double type. | =ATAN(C5) | Returns the arctangent. |
|
ATAN2 | Two parameters of double type. | =ATAN2(A1, A2) | Returns the arctangent using x- and y- coordinates. |
|
ATANH | One parameter of double type. | =ATANH(D4) | Returns the inverse hyperbolic tangent. |
|
BASE | Two parameters of integer type: the first is greater than 0 and less than 2^53; the second (radix) must be greater than or equal to 2 and less than or equal to 36. Third parameter is optional, which must be greater or equal to 0. | =BASE(A1, Radix[Min_length]) | Converts a number into a text representation with the given radix (base). If A1, Radix, or Min_length are outside the minimum or maximum constraints, BASE returns the #NUM! error value. If A1 is a non-numeric value, BASE returns the #VALUE! error value. Any non-integer number used as an parameter is truncated to an integer. If the Min_length parameter is included, leading zeros are added to the result if the result would otherwise be shorter than the minimum length specified. For example, BASE(16,2) returns 10000 but BASE(16,2,8) returns 00010000. |
|
CEILING | Two parameter of double type | =CEILING(2.5, 1) | Rounds the first parameter up to the nearest multiple based on the significance specified by the second parameter. |
|
CEILING.MATH | Three parameters. The first parameter must be less than 9.99E+307 and greater than -2.229E-308; the second parameter is the multiple to which the number is rounded; the optional Mode parameter. | =CEILING.MATH(A1, [significance], [mode]) | Rounds a number up to the nearest integer or to the nearest multitude of significance. By default, significance is +1 for positive numbers and -1 for negative numbers. By default, both positive and negative numbers with decimal portions are rounded up to the nearest integer. If you specify the Significance and Mode parameters, you can change the direction of rounding for negative numbers. For example, rounding -6.3 to a significance of 1 with a mode of 1 rounds away from 0, to -7. There are many combinations of Significance and Mode values that affect rounding of negative numbers in different ways. The Mode parameter does not affect positive numbers. |
|
CEILING.PRECISE | Two parameters. The first parameter is a number of double type. The Significance parameter is optional. | =CEILING.PRECISE(A1, [significance]) | Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, CEILING.PRECISE returns zero. If the Significance parameter is omitted, its default value is 1. |
|
CONCATENATE | At least one text item is mandatory; you can add more text items, up to a maximum of 255 items. Commas must separate the specified items. | =CONCATENATE(text1, [text2], …) | Joins several text items into a single text item. The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. Note that you can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For instance, =A1 & B1 returns the same value as =CONCATENATE(A1, B1). |
|
COS | One parameter of double type | =COS(A1) | Returns the cosine. |
|
COSH | One parameter of double type | =COSH(D7) | Returns the hyperbolic cosine. |
|
COMBIN | Two parameters of integer type. Non-integer numeric parameters are truncated to integers. | =COMBIN(number, number_chosen) | Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items. If either parameter is non-numeric, COMBIN returns the #VALUE! error value. If either parameter is negative, COMBIN returns the #NUM! error value. |
|
COMBINA | Two parameters of integer type. Non-integer numeric parameters are truncated to integers. | =COMBINA(number, number_chosen) | Returns the number of combinations (with repetitions) for a given number of items. If the value of either parameter is outside of its constraints, COMBINA returns the #NUM! error. If either parameter is a non-numeric value, COMBINA returns the #VALUE! error value. |
|
COT | One parameter of double type, which should be more than 0 and less than 2^27. | =COT(number) | Returns the cotangent of an angle specified in radians. If the specified parameter is outside its constraints, the COT function returns the #NUM! error value. If the specified parameter is not a numeric value, the COT function returns the #VALUE! error value. The COT(0) expression returns the #DIV/0! error value. |
|
COTH | One parameter of double type, which should be less than 2^27. | =COTH(number) | Returns the hyperbolic cotangent of a hyperbolic angle. If the specified parameter is outside its constraints, the COTH function returns the #NUM! error value. If the specified parameter is a non-numeric value, the COTH function returns the #VALUE! error value. |
|
CSC | One parameter of double type, which should be less than 2^27. | =CSC(number) | Returns the cosecant of an angle specified in radians. If the specified parameter is outside its constraints, the CSC function returns the #NUM! If the specified parameter is a non-numeric value, the CSC function returns the #VALUE! error value. CSC(n) equals 1/SIN(n). |
|
CSCH | One parameter of double type, which should be less than 2^27. | =CSCH(number) | Returns the hyperbolic cosecant of an angle specified in radians. If the specified parameter is outside its constraints, the CSCH function returns the #NUM! error value. If the specified parameter is a non-numeric value, the CSCH function returns the #VALUE! error value. |
|
DECIMAL | One parameter is a text string that must be less or equal to 255 characters, other parameter is an integer that must be greater or equal to 2 and less than or equal to 36. | =DECIMAL(text, radix) | Converts a text representation of a number in a given base into a decimal number. A radix greater than 10 uses the numeric values 0 – 9 and the letters A-Z as needed. For example, base 16 (hexadecimal) uses 0 – 9 and A – F, and base 36 uses 0 – 9 and A – Z. If either parameter is outside its constraints, DECIMAL may return the #NUM! or #VALUE! error value. |
|
DEGREES | One parameter of double type that specifies angle in radians | =DEGREES(PI()/2) | Converts radians to degrees. |
|
EVEN | One parameter of double type | =EVEN(-1) | Rounds the parameter up to the nearest even integer. |
|
EXP | One parameter of double type | =EXP(C1) | Returns the exponent value of the parameter. |
|
FACT | One non-negative parameter. If a number is not an integer, it is truncated. | =FACT(2) | Returns the factorial of the parameter. |
|
FACTDOUBLE | One non-negative parameter. If a number is not an integer, it is truncated. | =FACTDOUBLE(number) | Returns the double factorial of a number. If the number is non-numeric, the FACTDOUBLE function returns the #VALUE! error value. If the number is negative, the FACTDOUBLE function returns the #NUM! error value. |
|
FLOOR | Two parameters of double type | =FLOOR(-2.5, -2) | Rounds the first parameter down, towards zero, to the nearest multiple of the significance specified by the second parameter. |
|
FLOOR.MATH | Three parameters: first is the number to be rounded down, second is the multiple to which you want to round, and third is the direction (either towards or away from 0) to round negative numbers. | =FLOOR.MATH(number, significance, mode) | By default, both positive and negative numbers are rounded down to the nearest integer. By using 0 or a negative number as the Mode parameter, you can change the direction of the rounding for negative numbers. The Significance parameter rounds the number down to the nearest integer that is a multiple of the significance specified. The exception is where the number to be rounded is an integer. For example, for a Significance of 3, the number is rounded down to the next integer that is a multiple of 3. If Number is divided by a Significance of 2 or greater results in a remainder, the result is rounded. |
|
FLOOR.PRECISE | Two parameters. First is the number to be rounded down, second is the multiple to which the number is to be rounded. The Significance parameter is optional. | =FLOOR.PRECISE(number, [significance]) | Returns a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down. However, if the number of the significance is zero, zero is returned. If the significance is omitted, its default value is 1. The absolute value of the multiple is used, so that the FLOOR.PRECISE function returns the mathematical floor irrespective of the signs of the number and significance. |
|
INT | Parameter of type double | =INT(E4) | Rounds the parameter down to the nearest integer. |
|
ISO.CEILING | Two parameters. First is the value to be rounded; second is the optional multiple to which the number is to be rounded. | =ISO.CEILING(number, [significance]) | Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned. If the significance is omitted, its default value is 1. |
|
LN | One parameter of double type | =LN(C1) | Returns the natural logarithm. |
|
LOG | Two parameters: the number and the base | =LOG(100, 10) | Returns the logarithm of a number to the specified base. |
|
LOG10 | One parameter of double type | =LOG10(1000) | Returns the base-10 logarithm of the parameter. |
|
MMULT | Two obligatory parameters: the arrays you want to multiply. The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numeric values. Both arrays can be specified as cell ranges, array constants, or references. | =MMULT(array1, array2) | Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. You must enter formulas that return arrays only as array formulas. The MMULT function returns #VALUE! error when any of the specified cells are empty or contain non-numeric values. If the number of columns in array1 is different from the number of rows in array2, MMULT also returns the #VALUE! error. |
|
MOD | Two parameters: a number and divisor | =MOD(4,3) | Returns the remainder after the number is divided by the divisor. |
|
MROUND | Two parameters: the value to be rounded, and the multiple to which you want to round the value. | =MROUND(number, multiple) | Returns a number rounded to the desired multiple. MROUND rounds up, away from zero, if the remainder of dividing the number by the multiple is greater than or equal to half the value of the multiple. |
|
ODD | One parameter of double type | =ODD(3.5) | Rounds the parameter up to the nearest odd integer. |
|
PI | No parameters required | =PI() | Returns the value of Pi. |
|
POWER | Two parameters of double type: the number and the power | =POWER(A1,4) | Raises the number to the base. |
|
PRODUCT | The first parameter is the number or range that you want to multiply. Additional optional parameters that specify additional numbers or ranges that you want to multiply, up to a maximum of 255 parameters. | =PRODUCT(number1, [number2], …) | The PRODUCT multiplies all the numbers given as parameters and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2. The PRODUCT function is useful when you need to multiply multiple cells or cell ranges together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to = A1 * A2 * A3 * C1 * C2 * C3. |
|
QUOTIENT | Two parameters: the dividend and the divisor. | =QUOTIENT(numerator, denominator) | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
|
RADIANS | One parameter of type double that specifies angle in degrees. | =RADIANS(180) | Converts degrees to radians. |
|
RAND | No parameters required | =RAND() | Returns a random number between 0 and 1. |
|
RANDBETWEEN | Two parameters of integer type: bottom that specifies the smallest returned value, and top that specifies the largest returned value. | =RANDBETWEEN(bottom, top) | Returns a random integer between the numbers you specify. A new random integer is returned every time the worksheet is calculated. |
|
ROUND | Two parameters: the number of double type and the number of digits | =ROUND(20.57, 1) | Rounds the first parameter to the specified number of digits. |
|
ROUNDDOWN | Two parameters of double type | =ROUNDDOWN(2.75,0) | Rounds the parameter toward zero. The first parameter specifies a number to round up. The second parameter defines the number of digits to which you want to round the first parameter. |
|
ROUNDUP | Two parameters of double type | =ROUNDUP(-3.42,1) | Rounds the parameter toward infinity. The first parameter specifies a number to round up. The second parameter defines the number of digits to which you want to round the first parameter. |
|
SEC | One parameter of double type. Number is the angle in radians for which you want to calculate the secant. | =SEC(number) | Returns the secant of an angle. The absolute value of the parameter must be less than 2^27. If the parameter is outside of its constraints, SEC returns the #NUM! error value. If the parameter is a non-numeric value, SEC returns the #VALUE! error value. |
|
SECH | One parameter of double type. Number is the angle in radians for which you want to calculate the hyperbolic secant. | =SECH(number) | Returns the hyperbolic secant of an angle. The absolute value of the parameter must be less than 2^27. If the parameter is outside of its constraints, SECH returns the #NUM! error value. If the parameter is a non-numeric value, SECH returns the #VALUE! error value. |
|
SIGN | One parameter of double type | =SIGN(A2) | Returns the sign. |
|
SIN | One parameter of double type | =SIN(A1) | Returns the sine. |
|
SINH | One parameter of double type | =SINH(1) | Returns the hyperbolic sine. |
|
SQRT | One parameter of double type | =SQRT(B5) | Returns the square root. |
|
SQRTPI | One parameter of double type. | =SQRTPI(number) | Returns the square root of (number * pi). If the parameter is negative, SQRTPI returns the #NUM! error value. |
|
SUBTOTAL | The first parameter is a number from 1 to 11 (includes hidden values) or from 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. The second parameter is the first named range or reference for which you want the subtotal. Subsequent parameters are optional and specify named ranges or references 2 to 254 for which you want the subtotal. | =SUBTOTAL(function_num, ref1, [ref2], …) | Returns a subtotal in a list or database. If there are other subtotals within ref1, ref2, … (or nested subtotals), these nested subtotals are ignored to avoid double counting. The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. However, hiding a row in a subtotal of a vertical range does affect the subtotal. If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value. |
|
SUM | A list of parameters | =SUM(A1:C12) =SUM(A1, 3.14, 1.57) | Sums all the values in the list. |
|
SUMIF | Three parameters. The range of cells that you want to evaluate by criteria. Blank and text values are ignored. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. The optional sum_range parameter that allows you to add more cells to those specified ion the range parameter. | =SUMIF(range, criteria, [sum_range]) | The SUMIF function is designed to sum the values in a range that meet a criteria you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25, “>5”) In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, “John”, C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal “John”. Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not necessary. You can use both the question mark (?) and asterisk (*) – as the criteria parameter. A question mark matches any single character; an asterisk matches any sequence of characters. If you need to find an actual question mark or asterisk, type a tilde (~) preceding the character. |
|
SUMIFS | Three mandatory parameters: the range of cells to sum, the criteria range and its associated criteria. Optionally, you can specify additional ranges and their associated criteria, up to 127 range/criteria pairs. | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) | The SUMIFS function adds all of its arguments that meet multiple criteria. Include the criteria parameters in quotation marks if you are testing for text values. TRUE and FALSE values for the sum_range parameter are evaluated differently, which may cause unexpected results when they are added. Cells in the range passed as the sum_range parameter that contain TRUE evaluate to 1. Those that contain FALSE evaluate to 0 (zero). |
|
SUMPRODUCT | The function accepts from 1 to 255 identically sized arrays. | =SUMPRODUCT(array1, [array2], [array3], …) | The function multiplies the corresponding components of all specified arrays and sums the multiplication results. If a single array is passed as a parameter, the SUMPRODUCT function sums all the array values. All the provided arrays must have the same dimensions. Otherwise, the SUMPRODUCT function returns the #VALUE! error value. The function treats all non-numeric values as zeros in the parameter arrays. |
|
SUMSQ | A list of parameters | =SUMSQ(B1:D1) =SUM(1, 2, 3, 4) | Sums the square of values in the list. |
|
SUMX2MY2 | The function accepts two identically sized arrays. | =SUMX2MY2(array_x, array_y) | The function sums the differences of the squared corresponding array_x and array_y values. Both parameters should be numeric values, arrays of numeric values or cell references to arrays of numeric values. Text, logical values, or empty cells within the specified parameter arrays are ignored. If sizes of the specified arrays differ, the SUMX2MY2 function returns the #N/A! error value. The function uses the following equation to calculate the result: Result = Σ(x^2 - y^2) |
|
SUMX2PY2 | The function accepts two identically sized arrays. | =SUMX2PY2(array_x, array_y) | The function sums the sums of the squared corresponding array_x and array_y values. Both parameters should be numeric values, arrays of numeric values or cell references to arrays of numeric values. Text, logical values, or empty cells within the specified parameter arrays are ignored. If sizes of the specified arrays differ, the SUMX2PY2 function returns the #N/A! error value. The function uses the following equation to calculate the result: Result = Σ(x^2 + y^2) |
|
SUMXMY2 | The function accepts two identically sized arrays. | =SUMXMY2(array_x,. array_y) | The function sums the squares of differences of corresponding array_x and array_y values. Both parameters should be numeric values, arrays of numeric values or cell references to arrays of numeric values. Text, logical values, or empty cells within the specified parameter arrays are ignored. If sizes of the specified arrays differ, the SUMXMY2 function returns the #N/A! error value. The function uses the following equation to calculate the result: Result = Σ(x - y)^2 |
|
TAN | One parameter of double type | =TAN(C3) | Returns the tangent. |
|
TANH | One parameter of double type | =TANH(0) | Returns the hyperbolic tangent. |
|
TRUNC | One parameter of double type | =TRUNC(PI()) | Returns the integer part. Compare this with the INT function, which returns a double |
Financial functions
Financial functions are designed for financial calculations and analysis. Use them to perform many of the commonly used financial calculations, such as the calculation of yield, interest rates, investment valuations, internal rate of return, payments and asset depreciation.
Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
|
FV | Three mandatory parameters: the interest rate per period, the total number of payment periods in an annuity, and the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must specify the pv parameter instead. Additionally, you can specify the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt parameter. The second optional parameter is type that indicates when payments are due. If type is omitted, it is assumed to be 0. | =FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper. For all parameters, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers. |
|
IPMT | Four mandatory parameters include: the interest rate per period, the period for which you want to find the interest and must be in the range 1 to nper, the total number of payment periods in an annuity, and the lump-sum that a series of future payments is worth right now. Additionally, you can specify two more parameters that include: the future value, or a cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due (if type is omitted, it is assumed to be 0). | =IPMT(rate, per, nper, pv, [fv], [type]) | Returns the interest payment for an investment for a given period. Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper. For all parameters, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers. |
|
NPER | Three mandatory parameters include: the interest rate per period, the payment made each period which cannot change over the life of annuity, and the lump-sum amount that a series of future payments is worth right now. Additionally, you can specify the cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due. | =NPER(rate, pmt, pv, [fv], [type]) | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. |
|
NPV | Two mandatory parameters include the rate of discount over the length of one period and the value1 parameter specifies the payments and income. You can specify up to 254 value parameters. | =NPV(rate, value1, [value2], …) | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the value parameters. NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, refer to the description of the PV function. |
|
PMT | Three mandatory parameters include: the interest rate per period, the payment made each period which cannot change over the life of annuity, and the lump-sum amount that a series of future payments is worth right now. Additionally, you can specify the cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due. | =PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan based on constant payments and a constant interest rate. The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans. Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4 * 12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper. |
|
PPMT | Four mandatory parameters include: the interest rate per period, the period for which you want to find the interest and must be in the range 1 to nper, the total number of payment periods in an annuity, and the lump-sum that a series of future payments is worth right now. Additionally, you can specify two more parameters that include: the future value, or a cash balance you want to attain after the last payment is made (if this parameter is omitted, it is assumed to be 0), and the type that indicates when payments are due (if type is omitted, it is assumed to be 0). | =PPMT(rate, per, nper, pv, [fv], [type]) | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper. |
|
PV | Three mandatory parameters: the interest rate per period, the total number of payment periods in an annuity, and the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must specify the pv parameter instead. Additionally, you can specify the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt parameter. The second optional parameter is type that indicates when payments are due. If type is omitted, it is assumed to be 0. | =PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper. An annuity is a series of constant cash payments made over a continuous period. For example, a car loan or a mortgage is an annuity. For more information, refer to the description of each annuity function. In annuity functions, cash you pay out, such as a deposit to savings, is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number. For example, a $1000 deposit to the bank would be represented by the parameter -1000 if you are the depositor and by the parameter 1000 if you are the bank. |
Date and Time functions
Use this set of functions to identify the current date, extract a part of the date, convert the DateTime values to other data types, and perform other date-related calculations.
Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
|
DATE | Three operands defining the year, month and day. | =DATE(1900,1,1) | Calculates the serial number that represents a specified date. |
|
DATEVALUE | The date_text parameter specifies a date or reference to a cell that contains text representing a date in the Microsoft Excel® date format. For example, “1/30/2008” or “30-Jan-2008” are text strings within quotation marks that specify dates. | =DATEVALUE(date_text) | The DATEVALUE function converts a date that is stored as text to a serial number that the Spreadsheet control recognizes as a date. Like Microsoft Excel®, the Spreadsheet control stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is a serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900. Most functions automatically convert date values to serial numbers. The DATEVALUE function returns the #VALUE! error value if the value passed as the date_text parameter falls outside the range of dates between January 1, 1900 and December 31, 9999. |
|
DAY | One parameter defining the serial number of the required date. | =DAY(TODAY()) | Returns the day portion of a given date. |
|
DAYS | Two mandatory parameters: start_date and end_date are two dates between which you want to know the number of days. | =DAYS(end_date, start_date) | Returns the number of days between two dates. If both date parameters are numbers, DAYS uses EndDate-StartDate to calculate the number of days in between both dates. If either one of the date parameters is text, that parameter is treated as DATEVALUE(date_text) and returns an integer value instead of a time component. If date parameters are numeric values that fall outside the range of valid dates, DAYS returns the #NUM! error value. If date parameters are strings that cannot be parsed as valid dates, DAYS returns the #VALUE! error value. |
|
DAYS360 | Two mandatory parameters: start_date and end_date are two dates between which you want to know the number of days. If start_date occurs after end_date, the DAYS360 returns a negative number. Additionally, you can specify if the ExpressSpreadSheet should use the European method instead of the U.S. method in the calculation. | =DAYS360(start_date, end_date, [method]) | The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help calculate payments if your accounting system is based on twelve 30-day months. Like Excel, the ExpressSpreadSheet stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is a serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900. |
|
EDATE | Two mandatory parameters: start_date specifies the start date, and months specifies the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date. | =EDATE(start_date, months) | Returns the serial number of the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. Like Excel, the ExpressSpreadSheet stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is a serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900. |
|
EOMONTH | Two mandatory parameters: start_date (specifies the start date), and months (specifies the number of months before or after start_date). A positive value for months yields a future date; a negative value yields a past date. | =EOMONTH(start_date, months) | Returns the serial number of the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. |
|
HOUR | One parameter defining the serial number of the required date/time value. | =HOUR(NOW()) | Returns the hour portion of a given date/time value. |
|
ISOWEEKNUM | The date parameter is the date-time code used by the ExpressSpreadSheet for date and time calculations. | =ISOWEEKNUM(date) | Returns the number of ISO week number of the year for a given date. |
|
MONTH | One parameter defining the serial number of the required date. | =MONTH(TODAY()) | Returns the month portion of a given date. |
|
MINUTE | One parameter defining the serial number of the required date/time value. | =MINUTE(NOW()) | Returns the minutes portion of a given date/time value. |
|
NETWORKDAYS | Two mandatory parameters: “start_date” and “end_date” specify the beginning and end days of the calculated period. Additionally, you can exclude a number of days from calculation by listing them in the optional “holidays” parameter. | =NETWORKDAYS(start_date, end_date, [holidays]) | This function is useful for calculating employee benefits that accrue based on the number of days worked under specific terms. The NETWORKDAYS function returns the number of whole workdays between two specified dates. The function returns the #VALUE! error code if the start and/or end dates are invalid. |
|
NETWORKDAYS.INTL | Two mandatory parameters: “start_date” and “end_date” specify the beginning and end days of the calculated period. The optional “weekend” and “holidays” parameters allow you to specify a custom weekend and a list of holidays excluded from the calculated period. | =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) | The NETWORKDAYS.INTL function returns the number of whole workdays between two specified dates. In addition to the functionality provided by NETWORKDAYS, this function allows you to specify a custom weekend in the same way as the WORKDAYS.INTL function. |
|
NOW | No parameters required. | =NOW() | Returns the current time in general format. You can apply further formatting to the result of the function. |
|
SECOND | One parameter defining the serial number of the required date/time value. | =SECOND(NOW()) | Returns the seconds portion of a given date/time value. |
|
TIME | Three parameters defining hour, minute and second parts of a time value. | =TIME(16, 48, 10) | Returns a decimal number for a specified time. |
|
TIMEVALUE | The time_text parameter is a text string specifies a time in any one of the Microsoft Excel® time formats; for example, “6:45 PM” and “18:45” text strings within quotation marks that specify time. | =TIMEVALUE(time_text) | Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM). Date information in time_text is ignored. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day). |
|
TODAY | No parameters required. | =TODAY() | Returns the serial number of the current date. |
|
WEEKDAY | Two parameters: the serial number of the required date, weekday base. | =WEEKDAY(TODAY()) =WEEKDAY(DATE( 2002, 12, 1),1) returns 1(Sunday) | Returns the day of the week corresponding to the specified date. The weekday base identifies the first day of the week and determines the return value type: 1 or omitted: the first day of the week is Sunday. The function returns 1 for Sunday, 2 for Monday, etc. 2: the first day of the week is Monday. The function returns 1 for Monday, 2 for Tuesday, etc. 3: the first day of the week is Monday. The function returns 0 for Monday, 1 for Tuesday, etc. |
|
WEEKNUM | The serial_number parameter specifies a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. Additionally, you can specify the return_type parameter that determines on which day the week begins (the default value is 1). | =WEEKNUM(serial_number, [return_type]) | Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1. |
|
WORKDAY | Two mandatory parameters:
Additionally, you can exclude a number of days from calculation by listing them in the optional “holidays” parameter. | =WORKDAY(start_date, days, [holidays]) | This function is useful for excluding weekends and/or holidays in calculating expected delivery times, invoice due dates, etc. The WORKDAY function returns the workday separated from the initial date by the specified number of workdays. The returned day precedes or follows the initial date, depending on the “days” parameter’s sign. The function truncates the fractional part if a floating-point value is passed as the “days” parameter. The function returns the #VALUE! error code if a parameter accepts an invalid value. If the calculated result does not correspond to a valid date, the WORKDAY function returns the #NUM error code. |
|
WORKDAY.INTL | Two mandatory parameters:
The function also accepts the optional “weekend” parameter that allows you to select one of the available weekend presets listed in the Explanation column. Additionally, you can exclude a number of days from calculation by listing them in the optional “holidays” parameter. | =WORKDAY.INTL(start_date, days, [weekend], [holidays]) | The WORKDAY.INTL function returns the serial number of the date before or after the specified number of workdays with custom weekends. You can pass an integer number corresponding to one of the available weekend presets as the optional “weekend” parameter: 1 or unspecified – Saturday and Sunday; 2 – Sunday and Monday; 3 – Monday and Tuesday; 4 – Tuesday and Wednesday; 5 – Wednesday and Thursday; 6 – Thursday and Friday; 7 – Friday and Saturday; 11 – Sunday only; 12 – Monday only; 13 – Tuesday only; 14 – Wednesday only; 15 – Thursday only; 16 – Friday only; 17 – Saturday only. Alternatively, you can specify the “weekend” parameter as a string including only seven characters that can be either 0 (workday) or 1 (non-workday). For instance, 1100000 corresponds to a weekend including Monday and Tuesday. The WORKDAY.INTL function returns the #VALUE! error code if the “weekend” parameter accepts a string that includes unsupported characters, has an incorrect length, or equals 1111111. The “holidays” parameter works identically to the same parameter of the WORKDAY function. |
|
YEAR | One parameter defining the serial number of the required date. | =YEAR(TODAY()) | Returns the year portion of a given date. |
|
YEARFRAC | Two mandatory parameters include start_date and end_date that represent start date and end date, respectively. Additionally, you can specify which type of the day count basis to use. | =YEARFRAC(start_date, end_date, [basis]) | Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and end_date parameters). Use the YEARFRAC worksheet function to identify the proportion of a whole year’s benefits or obligations to assign to a specific term. |
Text functions
The text functions allow you to work with text strings.
Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
& (ampersand) | Two parameters of type string | = “Yellow “&”river” | Concatenates specified strings. |
|
CHAR | The number parameter allows you to specify a number between 1 and 255 representing the required character. The character is from the character set used by your computer. | =CHAR(number) | Returns the character specified y a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
|
CLEAN | The text parameter specifies any worksheet information from which you want to remove non-printable characters. | =CLEAN(text) | Removes all non-printable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed. The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. |
|
CODE | The text parameter specifies the text for which you want the code of the first character. | =CODE(text) | Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. |
|
CONCATENATE | A list of strings | =CONCATENATE(“Developer”, “ Express”) | Joins several text strings in one text string. An alternative to “&”. |
|
DOLLAR | Two parameters defining the value and the number of digits to the right of the decimal point in the output string. | =DOLLAR(957.344, 2) | Converts the number to text using currency format $#,##0.00_);($#,##0.00), with the decimals rounded to the specified number of places. |
|
EXACT | The text1 and text2 parameters specify the first and second text strings. | =EXACT(text1, text2) | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document. |
|
FIND | The find_text parameter specifies the text you want to find, the within_text parameter specifies the source text for the text fragment you want to find. Additionally, you can specify the character at which to start the search as the start_num parameter. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1. | =FIND(find_text, within_text, [start_num]) | FIND locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is. |
|
FIXED | The first parameter of type double is required. The second parameter of type integer is optional (the default value is 2). The third parameter of type Boolean is optional. | =FIXED(1234.567, 1) | Rounds the first parameter to the number of decimals determined by the second parameter and returns it as a string. The third parameter specifies whether to omit commas in the output string. |
|
LEFT | The first parameter of type string is required. The second parameter of type integer is optional. | =LEFT(A1) =LEFT(A1, 3) | Returns the first character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1. |
|
LEN | One parameter of type string. | =LEN(“ABC”) | Returns the length of a given string. |
|
LOWER | One parameter of type string. | =LOWER(A1) | Converts a string to lowercase. |
|
MID | The first parameter is of type string, the second and the third parameters are of type integer. | =MID(“ABC”,3,1) | Returns the substring of a given text string. The position of the substring is defined by the second parameter. The third parameter specifies the number of characters to extract. |
|
PROPER | The text parameter of the string type is mandatory. Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize. | =PROPER(text) | Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. |
|
REPLACE | Four mandatory parameters: the text in which you want to replace some characters, the position of the character in old_text that you want to replace with new_text, the number of characters in old_text that you want REPLACE to replace with new_text, and the text that will replace a specified number of characters in old_text. | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string, based on the number of characters you specify, with a different text string. REPLACE always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is. |
|
REPT | Two obligatory parameters: the text you want to repeat, and the positive number of times to repeat text. | =REPT(text, number_times) | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. If number_times is 0 (zero), REPT returns “” (empty string). If number_times is not an integer, it is truncated. The result of the REPT function cannot be longer than 32767 characters, otherwise REPT returns the #VALUE! error value. |
|
RIGHT | The first parameter of type string is required. The second parameter of type integer is optional. | =RIGHT(B2, 3) | Returns the last character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1. |
|
SEARCH | Two obligatory parameters of string type. | =SEARCH(“n”, “printer”) | The SEARCH function locates one text string with a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter “n” in the word “printer”, you can use this function as =SEARCH(“n”, “printer”). |
|
SUBSTITUTE | Three obligatory parameters of string type plus an optional instance_num parameter that allows you to specify what instance of the old text should be replaced. | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. |
|
T | One obligatory parameter of any type. | =T(value) | Returns the text referred by value. |
|
TEXT | Two obligatory parameters: first is a numeric value or a reference to a cell containing a numeric value; second is a numeric format as a text string enclosed in quotation marks, for example “m/d/yyyy” or “#,##0.00”. | =TEXT(A1, “$0.00”) | The TEXT function converts a numeric value to text and allows you to specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 23.5. To format the number as a dollar amount, you can use the following formula: =TEXT(A1, “$0.00”) |
|
TRIM | One parameter of string type | =TRIM(“ Express Spread Sheet “) | Removes all spaces from text except for single spaces between words. |
|
TRUNC | Two parameters: the number that is truncated, and the optional num_digits parameter that specifies the precision of the truncation. The default value for num_digits is 0 (zero). | =TRUNC(number, [num_digits]) | Truncates a number to an integer by removing the fractional part of the number. |
|
UPPER | One parameter of type string. | =UPPER(A1&A2) | Converts a string to uppercase. |
|
VALUE | One obligatory parameter of string type. | =VALUE(text) | Converts a text string that represents a number to a member. Text can be in any of the constant number, date, or time formats recognized by the ExpressSpreadSheet. If text is not in one of these formats, VALUE returns the #VALUE! error value. You do not generally need to use the VALUE function in a formula because the ExpressSpreadSheet automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet applications. |
Report Functions
These functions are used only in report templates to create placeholder fields. In resulting report documents, these placeholders are substituted with the actual data obtained from a data source bound to the Report designer control by using the DataBinding property.
Mnemonics | Operand Type | Example | Explanation |
|---|---|---|---|
|
FIELD | The Data_Field_Name parameter specifies the name of the data source field from which the value will be obtained. | =FIELD(“Data_Field_Name”) | The FIELD function is replaced by a data value from the corresponding field in the bound data source. |
|
FIELDPICTURE | The Data_Field_Name parameter specifies the name of the data source field from which the value will be obtained. | =FIELDPICTURE(“Data_Field_Name”,”Range”,C2,FALSE,50) | The FIELDPICTURE function is replaced by a picture from the specified data field, then resizes it according to the function parameters and inserts the picture into the specified position in the resulting report document. |
|
RANGE | =(RANGE($G$11) |