Formula (Extended) Function List
Overview
These functions can be used in "Formula (Extended)".
Cautions
- The functions described on this page are available when [Extended] is selected as the calculation method on the "Formula" tab. For the detailed setup steps, refer to "Manage Table: Formula (Extended)".
- Depending on the "Decimal Places" setting of a numerical value column, the number of decimal digits that can be entered or displayed may be limited. Check the advanced settings of the relevant column as necessary.
- In the numerical calculations performed with "Formula (Extended)", small errors caused by the nature of general floating-point arithmetic may occur. This happens because some decimal fractions (0.1 and 0.2, for example) cannot be represented as binary fractions and are therefore calculated as approximate values. A similar phenomenon can be observed in many software environments. For calculations that expect an integer, such as amounts and quantities, and for processing where an error could change the result of a conditional branch, we recommend rounding (with $ROUND, for example) before comparing or saving the value.
For example, suppose you set 32.8 in the numerical value column NumA and 3095 in NumB, and put in NumC the result of multiplying the two and truncating the decimal part. You want 101516 in NumC, but 101515 is actually stored because of the error.
| Column | Setting | Content |
| NumA | Decimal Places: 1 | 32.8 |
| NumB | Decimal Places: 0 | 3095 |
| NumC | $ROUNDDOWN(NumA * NumB, 0) | Expected result: 101516
Actual result: 101515 |
You can check this easily with the developer tools of a web browser. Press the [F12] key to open the console.
> 32.8*3095
101515.99999999999 // An error occurs, so the value is not 101516
> Math.floor(32.8*3095,0) // 0.99999999999 is truncated
101515
To avoid this kind of error, absorb the error with $ROUND to make the value an integer, and then truncate it with $ROUNDDOWN.
| Column | Setting | Content |
| NumC | $ROUNDDOWN($ROUND(NumA * NumB, 0), 0) | Expected result: 101516
Actual result: 101516 |
Date/Time
| No | Function name | Description |
| 1 | $DATE | Generate a date. |
| 2 | $DATEDIF | Calculate the number of days, months, or years between two dates. |
| 3 | $DATETIME | Generate a datetime. |
| 4 | $DAY | Get the number of days in a date. |
| 5 | $DAYS | Calculate the number of days between two dates. |
| 6 | $EOMONTH | Find the last day of the month before or after a specified number of months from a start date. |
| 7 | $HOUR | Get the hour of the date. |
| 8 | $MINUTE | Get the minute of the date. |
| 9 | $MONTH | Get the month of the date. |
| 10 | $NOW | Get the current datetime. |
| 11 | $SECOND | Get the second of the date. |
| 12 | $TODAY | Get the current date. |
| 13 | $WEEKDAY | Return the day of the week for the date. |
| 14 | $YEAR | Get the year of the date. |
String Manipulation
| No | Function name | Description |
| 1 | $ASC | Convert full-width characters to half-width characters. |
| 2 | $CONCAT | Concatenate the specified strings. |
| 3 | $FIND | Search for the search string within the target string, and find the position where the search string first appears, counting from the left. The search is case-sensitive. |
| 4 | $JIS | Convert half-width characters to full-width characters. |
| 5 | $LEFT | Return a specified number of characters from the beginning of a string. |
| 6 | $LEN | Return the number of characters in a string. |
| 7 | $LOWER | Convert uppercase letters in a string to lowercase letters. |
| 8 | $MID | Return a specified number of characters from a specified position in a string. |
| 9 | $REPLACE | Convert a specified number of characters in a target string to another character. |
| 10 | $RIGHT | Return a specified number of characters from the end of a string. |
| 11 | $SEARCH | Search for a search string in a target string, counting from the left to find the first occurrence of the search string. The search is not case sensitive. |
| 12 | $SUBSTITUTE | Convert a specific string in the target string to a specified string. |
| 13 | $TEXT | Convert a string with a display format applied. |
| 14 | $TRIM | Remove unnecessary spaces from a string. |
| 15 | $UPPER | Convert lowercase letters in a string to uppercase letters. |
| 16 | $VALUE | Convert numbers entered as a string to a numerical value. |
Logical
| No | Function name | Description |
| 1 | $AND | Return TRUE if all arguments are TRUE. |
| 2 | $IF | Return a specified value depending on the result of a logical expression (TRUE or FALSE). |
| 3 | $IFERROR | Return a specified value if the value is an error. If it is not an error, returns the value. |
| 4 | $IFS | Check whether one or more conditions are met and return the value that corresponds to the first true condition. |
| 5 | $NOT | Return TRUE if the argument is FALSE, and FALSE if the argument is TRUE. |
| 6 | $OR | Return TRUE if any argument is TRUE. If all arguments are FALSE, returns FALSE. |
| No | Function name | Description |
| 1 | $ISBLANK | Return true if the argument is blank. |
| 2 | $ISERROR | Return true if the argument is an error. |
| 3 | $ISEVEN | Return TRUE if the argument is an even number, and FALSE if it is an odd number. |
| 4 | $ISNUMBER | Return TRUE if the cell contains a numerical value. |
| 5 | $ISODD | Return TRUE if the argument is an odd number, and FALSE if it is an even number. |
| 6 | $ISTEXT | Return TRUE if the cell contains a string. |
Mathematics
| No | Function Name | Description |
| 1 | $ABS | Returns the absolute value. |
| 2 | $MOD | Returns the remainder when dividing a numerical value. |
| 3 | $POWER | Returns the power of a numerical value. |
| 4 | $RAND | Returns a random real number greater than or equal to 0 and less than 1. Returns a new random number each time it is calculated. |
| 5 | $ROUND | Returns the numerical value rounded to the specified number of digits. |
| 6 | $ROUNDDOWN | Truncate a numerical value to the specified number of digits. |
| 7 | $ROUNDUP | Rounds a numerical value up to the specified number of digits. |
| 8 | $SQRT | Returns the positive square root. |
| 9 | $TRUNC | Truncate the decimal part of a numerical value to convert it to an integer or to a specified number of digits. |
Statistics
| No | Function Name | Description |
| 1 | $AVERAGE | Return the average value of the arguments. |
| 2 | $MAX | Return the maximum value of the arguments. Logical values and strings are ignored. |
| 3 | $MIN | Return the minimum value of the arguments. Logical values and strings are ignored. |
| No | Function Name | Description |
| 1 | $LOGINUSERID | Return the ID (number) of the logged-in user. |
| 2 | $LOGINUSERNAME | Return the display name (string) of the logged-in user. |