|
The following sections list computed column functions by category. Many are designed to operate like Excel, clicking on the Excel like functions will take you to Excel help on Microsoft website. When appropriate, clicking on the name of the function will link to specific XpandedReports information.
Tips and Tricks
In this topic
Operators Date and time (Excel like)
Date and time (XR)
Information functions
Logical functions
Math functions
Text functions
|
| | Operators
| Function | Syntax | Description |
|---|
| x + y | addition | Add x and y |
| x - y | subtraction | Subtract y from x |
| x * y | multiplication | Multiply x and y |
| x / y | division | Divide x by y |
| x ^ y | exponentiation, pow | Raise x to the exponent y |
| x % y | modulo | Return the remainder of x divded by y |
| x == y | equality | Indicates if x and y are equal |
| x != y, x <> y | inequality | Indicates if x and y are not equal |
| x > y | greater than | Indicates if x is greater than y |
| x >= y | greater than or equal to | Indicates if x is greater than or equal to y |
| x < y | less than | Indicates if x is less than y |
| x <= y | less than or equal to | Indicates if x is less than or equal to y |
| Top of Page
|
| |
| | Date and time (Excel like)
| Function | Syntax | Description |
|---|
| DATE | DATE(year,month,day) | Returns the serial number of a particular date |
| DATEDIF | DATEDIF(start_date,end_date,unit) | Calculates the number of days, months, or years between two dates |
| DATEVALUE | DATEVALUE(date_text) | Converts a date in the form of text to a serial number |
| DATE_XR | DATE_XR(report_date) | Returns the serial number of a particular date |
| DAY | DAY(serial_number) | Converts a serial number to a day of the month |
| DAYS360 | DAYS360(start_date,end_date,method) | Calculates the number of days between two dates based on a 360-day year |
| HOUR | HOUR(serial_number) | Converts a serial number to an hour |
| MINUTE | MINUTE(serial_number) | Converts a serial number to a minute |
| MONTH | MONTH(serial_number) | Converts a serial number to a month |
| NETWORKDAYS | NETWORKDAYS(start_date,end_date) | Returns the number of whole workdays between two dates |
| NOW | NOW() | Returns the serial number of the current date and time |
| SECOND | SECOND(serial_number) | Converts a serial number to a second |
| TIME | TIME(hour,minute,second) | Returns the serial number of a particular time |
| TIMEVALUE | TIMEVALUE(time_text) | Converts a time in the form of text to a serial number |
| TIME_XR | TIME(report_timestamp) | Returns the serial number of a particular time. |
| TODAY | TODAY( ) | Returns the serial number of today's date |
| WEEKDAY | WEEKDAY(serial_number,return_type) | Converts a serial number to a day of the week |
| WEEKNUM | WEEKNUM(serial_num,return_type) | Converts a serial number to a number representing where the week falls numerically with a year |
| YEAR | YEAR(serial_number) | Converts a serial number to a year |
| YEARFRAC | YEARFRAC(start_date,end_date,basis) | Returns the year fraction representing the number of whole days between start_date and end_date |
| Top of Page
|
| | Date and time (XR)
| Function | Syntax | Description |
|---|
| DATEDIF_XR | DATEDIF_XR(start_date,end_date,unit) | Calculates the number of days, months, or years between two dates |
| DATE_FROM_TEXT | DATE_FROM_TEXT(text_column, text_format) | Converts a text value to a Date value. |
| DATE_XR | DATE_XR(report_date) | Returns the serial number of a particular date |
| DAYS360_XR | DAYS360_XR(start_date,end_date,method) | Calculates the number of days between two dates based on a 360-day year |
| DAY_XR | DAY_XR(report_date) | Converts a report date to a day of the month |
| HOUR_XR | HOUR_XR(report_date) | Converts a report date to an hour |
| MINUTE_XR | MINUTE_XR(report_timestamp) | Converts a report date to a minute |
| MONTH_XR | MONTH_XR(report_date) | Converts a report date to a month |
| NETWORKDAYS_XR | NETWORKDAYS_XR(start_date,end_date) | Returns the number of whole workdays between two dates |
| NOW_XR | NOW_XR() | Returns the report date of the current date and time. Used in with other Date XR functions. |
| TEXT_TO_DATE | TEXT_TO_DATE(text_column, text_format) | Converts a text value to a Date value. |
| TIME_XR | TIME(report_timestamp | Returns the serial number of a particular time. |
| WEEKDAY_XR | WEEKDAY_XR(report_date,return_type) | Converts a report date to a day of the week |
| WEEKNUM_XR | WEEKNUM_XR(report_date,return_type) | Converts a report date to a number representing where the week falls numerically with a year |
| YEARFRAC_XR | YEARFRAC_XR(start_date,end_date,basis) | Returns the year fraction representing the number of whole days between start_date and end_date |
| YEAR_XR | YEAR_XR(report_date) | Converts a report date to a year |
| Top of Page
|
| | Information
| Function | Syntax | Description |
|---|
| ISBLANK | ISBLANK(value) | Returns TRUE if the value is blank |
| ISEVEN | ISEVEN(value) | Returns TRUE if the number is even |
| ISLOGICAL | ISLOGICAL(value) | Returns TRUE if the value is a logical value |
| ISNONTEXT | ISNONTEXT(value) | Returns TRUE if the value is not text |
| ISNUMBER | ISNUMBER(value) | Returns TRUE if the value is a number |
| ISODD | ISODD(value) | Returns TRUE if the number is odd |
| ISTEXT | ISTEXT(value) | Returns TRUE if the value is text |
| N | N(value) | Returns a value converted to a number |
| TYPE | TYPE(value) | Returns a number indicating the data type of a value |
| Top of Page
|
| | Logical
| Function | Syntax | Description |
|---|
| AND | AND(logical1,logical2, ...) | Returns TRUE if all of its arguments are TRUE |
| FALSE | FALSE() | Returns the logical value FALSE |
| IF | IF(logical_test,value_if_true,value_if_false) | Specifies a logical test to perform |
| NOT | NOT(logical) | Reverses the logic of its argument |
| OR | OR(logical1,logical2,...) | Returns TRUE if any argument is TRUE |
| TRUE | TRUE() | Returns the logical value TRUE |
| XOR | XOR(logical1,logical2,...) | Indicates if one, but not both, of logical values is true. The result is true if they are different or false if they are not the same. |
| Top of Page
|
| | Math
| Function | Syntax | Description |
|---|
| ABS | ABS(number) | Returns the absolute value of a number |
| ACOS | ACOS(number) | Returns the inverse cosine (arc cosine) of a x |
| CEIL | CEIL(number) | Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel. |
| CEILING | CEILING(number,significance) | Rounds a number to the nearest integer or to the nearest multiple of significance |
| EVEN | EVEN(number) | Rounds a number up to the nearest even integer |
| EXP | EXP(number) | Returns e raised to the power of a given number |
| FACT | FACT(number) | Returns the factorial of a number |
| FLOOR | FLOOR(number,significance) | Rounds a number down, toward zero |
| INT | INT(number) | Rounds a number down to the nearest integer |
| LN | LN(number) | Returns the natural logarithm of a number |
| LOG | LOG(number,base) | Returns the logarithm of a number to a specified base |
| LOG10 | LOG10(number) | Returns the base-10 logarithm of a number |
| MAX | MAX(number1, number2, number3,...) | Returns the largest value in a set of values. |
| MIN | MIN(number1, number2, number3,...) | Returns the smallest number in a set of values. |
| MOD | MOD(number,divisor) | Returns the remainder from division |
| MROUND | MROUND(number,multiple) | Returns a number rounded to the desired multiple |
| ODD | ODD(number) | Rounds a number up to the nearest odd integer |
| POW | POWER(number,power) | Returns the result of a number raised to a power |
| POWER | POWER(number,power) | Returns the result of a number raised to a power |
| QUOTIENT | QUOTIENT(numerator,denominator) | Returns the integer portion of a division |
| RAND | RAND( ) | Returns a random number between 0 and 1 |
| RANDBETWEEN | RANDBETWEEN(bottom,top) | Returns a random number between the numbers you specify |
| ROMAN | ROMAN(number,form) | Converts an arabic numeral to roman, as text |
| ROUND | ROUND(number,num_digits) | Rounds a number to a specified number of digits |
| ROUNDDOWN | ROUNDDOWN(number,num_digits) | Rounds a number down, toward zero |
| ROUNDUP | ROUNDUP(number,num_digits) | Rounds a number up, away from zero |
| SIGN | SIGN(number) | Returns the sign of a number. 1 for positive, -1 for negative |
| SQRT | SQRT(number) | Returns a positive square root |
| SUM | SUM(number1, number2, number3,...) | Adds all the numbers in a range of cells. |
| Top of Page
|
| | Text
| Function | Syntax | Description |
|---|
| ABBREVIATE | ABBREVIATE(text,number_times) | Abbreviates a text using ellipses. This will turn "Now is the time for all good men" into "Now is the time for...". |
| CAP | CAP(text) | Capitalize words in the text. Individual words/names will be given uppercase first letters, with all other letters in lowercase. |
| CHAR | CHAR(number) | Returns the character specified by the code number |
| CODE | CODE(text) | Returns a numeric code for the first character in a text string |
| COMPARE_IGNORECASE | COMPARE_IGNORECASE(text1,text2) | Compares two text and returns TRUE if they are equal ignoring the case, FALSE otherwise. |
| CONCAT | CONCAT(text1,text2,...) | Joins several text items into one text item |
| CONCATENATE | CONCATENATE(text1,text2,...) | Joins several text items into one text item |
| CONCAT_WS | CONCAT_WS(sep, text1,text2,...) | Concatenate with separator. Joins several text items into one text item. |
| CONTAINS_ANY | CONTAINS_ANY (text,searchChars) | Checks if the text contains any character in the given set of characters. |
| CONTAINS_NONE | CONTAINS_NONE(text,invalidText) | Checks that the text does not contain certain characters. An empty text ("") always returns true. |
| CONTAINS_ONLY | CONTAINS_ONLY(text1,text2) | Checks if the text contains only certain characters. An empty text ("") always returns true. |
| COUNTMATCHES | COUNTMATCHES(text,sub) | Counts the number of occurrences of one text in another |
| DIFFERENCE | DIFFERENCE(text1,text2) | Compares two text, and returns the portion where they differ. (More precisely, return the remainder of the second text, starting from where it's different from the first.) |
| ENDS_WITH | ENDS_WITH(text,suffix) | Check if a text ends with a suffix. The comparison is case sensitive. |
| ENDS_WITH_IGNORECASE | ENDS_WITH_IGNORECASE(text,suffix) | Check if a text ends with a suffix. The comparison is case insensitive. |
| EXACT | 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 | FIND(find_text,within_text,start_num) | Finds one text value within another (case-sensitive) |
| INSERT | INSERT(old_text, pos, len, new_text) | Replaces characters within text. Replaces part of a text string, based on the number of characters you specify, with a different text string. |
| LCASE | LCASE(text) | Converts text to lowercase |
| LEFT | LEFT(text,num_chars) | LEFT returns the first character or characters in a text string, based on the number of characters you specify. |
| LEN | LEN(text) | Returns the number of characters in a text string |
| LENGTH | LENGTH(text) | Returns the number of characters in a text string |
| LPAD | LPAD(text, max_len, pad_text) | Pad the left side of string str with copies of string pad, up to a total padding of len characters. |
| MID | MID(text,start_num,num_chars) | Returns a specific number of characters from a text string starting at the position you specify |
| POSITION | POSITION(find_text,within_text) | Returns the starting position of the first occurrence of find_text in within_text. 0 is returned if not found. |
| REMOVE | REMOVE(text,remove) | Removes all occurrences of a sub text from within the source string. Removes a text only if it is at the begining of a source text, otherwise returns the source text. An empty ("") source text will return the empty string. A empty search text will return the source string. |
| REMOVEEND | REMOVEEND(text, remove) | Removes a sub text only if it is at the end of a source string, otherwise returns the source string. An empty ("") source text will return the empty string. A empty search text will return the source string. |
| REMOVESTART | REMOVESTART(text, remove) | Removes a sub text only if it is at the begining of a source string, otherwise returns the source string. An empty ("") source text will return the empty string. A empty search text will return the source string. |
| REPEAT | REPEAT(text,number_times) | Repeats text a given number of times |
| REPLACE | REPLACE(old_text,start_num,num_chars,new_text) | Replaces characters within text |
| REPLACEALL | REPLACEALL(text, search, replacement) | Parameters: |
| REPT | REPT(text,number_times) | Repeats text a given number of times |
| REVERSE | REVERSE(text) | Returns a reversed copy of the input string str |
| RIGHT | RIGHT(text,num_chars) | Returns the rightmost characters from a text value |
| RPAD | RPAD(text, max_len, pad_text) | Pad the right side of string str with copies of string pad, up to a total padding of len characters. |
| SEARCH | SEARCH(find_text,within_text,start_num) | Finds one text value within another (not case-sensitive) |
| SPLIT_TEXT | SPLIT_TEXT(text, splitChar, section) | SPLIT_TEXT returns the section or characters in a text string, based on the split character and sub section you specify. |
| STARTS_WITH | STARTS_WITH(text,prefix) | Check if text starts with a prefix. The comparison is case sensitive. |
| STARTS_WITH_IGNORECASE | STARTS_WITH_IGNORECASE(text,prefix) | Check if text starts with a prefix. The comparison is case insensitive. |
| SUBSTRING | SUBSTRING(text,num_chars,num_chars) | SUBSTRING returns the first character or characters in a text string, based on the number of characters you specify. |
| SUB_STRING_BETWEEN | SUB_STRING_BETWEEN(text, text open, text close) | Gets the text that is nested in between two Strings. Only the first match is returned. A empty input text returns empty. An empty ("") open and close returns an empty string. |
| T | T(value) | Converts its arguments to text |
| TRIM | TRIM(text) | Removes spaces from text. (removes leading and trailing whitespace) |
| UCASE | UCASE(text) | Converts text to uppercase |
| UNCAPITALIZE | UNCAPITALIZE(text) | Returns a uncapitalized copy of the input text. Only the first letter of each word is changed. |
| VALUE | VALUE(text) | Converts a text argument to a number |
| Top of Page
| |
