Formula
Usage
You have the option of creating a new field (use New field column) or setting a field value (use Replace value column).
Check the Apache POI docs page for a list of supported formulas at https://poi.apache.org/components/spreadsheet/eval-devguide.html#What+functions+are+supported
| The formula will not evaluate a field unless the field pre-exists. Either start the execution of that pipeline to create missing fields or check if the field exists by viewing fields in the Replace value dropdown. The formula editor will not always open if the pipeline properties parameters are NOT set. |
Examples
Formula Examples using the TEXT function:
-
Int to Text: "size=" & TEXT([RowLimitInteger],"0").
-
Int to Text: "new_counter=" & TEXT([counter]+1, "0")
-
DateTime to Text: [StartDateField] & "=" & TEXT([StartDateTime], "yyyy-MM-dd")
DateTime Examples:
-
In Excel, dates and times are stored as floating-point numbers, as amount of days since midnight 01/01/1900.
-
To add one hour to the current datetime, the formula would be: NOW()+(1/24)
-
Set a Null Timestamp with formula: DATEVALUE("0")
Options
| Option | Description |
|---|---|
transform name | Change the name of this transform to explain the purpose of the formula fields you’re adding. |
New Field | the name of the new field that will be created from the formula. The Formula transform requires a data row to create a new field (if using a transform that queries for rows, you will have to preview it first to generate a row). You can add a row generator with a single row as the first transform in your pipeline. |
Formula | To use a value from a data row field, use square brackets like [field1]. To use a parameter or variable, use the normal syntax ${param1}. If you use a parameter in place of text like with CONCATENATE (see samples) you must put double quotes around it. Example: * Note: You may have to add quotes around variables in formulas as variables have no type. |
Value type | the data type for the new formula field |
Length | field length |
Precision | field precision |
Replace value | select a pipeline field from the dropdown to replace that field’s value with the formula result. It is not recommended to pre-create a field with a NULL value in a "Get variables" transform as that is considered a hack and is not supported behavior. |
Formula Editor
The Formula Editor is opened when you click on the Formula field in the options table (or go there through tab navigation).
The Formula Editor consists of three main areas:
-
the formula list (left hand side) shows a categorized list of functions that are available for use. Click on a formula to open it’s description, syntax and example formulas in the documentation panel.
-
documentation panel (lower right) shows the documentation for the selected formula. The information shown in this panel consists of a formula’s
-
name
-
description
-
syntax
-
return type
-
constraints (optional)
-
semantics (optional)
-
examples (optional)
-
| Check the Apache POI docs page for a list of support formulas. |
Notes
The kettle import process is able to migrate the existing Formula steps contained in kettle processes in the new Hop Formula transform. Even if the migration will go without any problem, the migration process is unable to enter in the inner details of the Formula’s transform macros and perform all the necessary checks to guarantee the correct behavior and the expected correct results. Therefore, even if the majority of the macros are the same as the existing macros you can find in Kettle Formula step, we strongly suggest to perfom a review of all of your fomulas transforms to verify that they behave as expected after the migration to the new Hop format.
Functions
Basic Computation
%
description | Percentage calculation, for example 12% resolves to 0.12 |
syntax | |
returns | |
semantics | |
constraints |
(
description | Opening bracket to influence calculation order in an expression |
syntax | |
returns | |
semantics | |
constraints |
Comparisons
DateTime
DATE
description | Construct date from year, month, and day of month. |
syntax | DATE( Integer Year ; Integer Month ; Integer Day ) |
returns | Date |
semantics | This computes the date’s serial number given Year, Month, and Day. Fractional values are truncated. The value of the serial number depends on the current epoch. Note that some applications may not handle correctly dates before 1904; in particular, many spreadsheets incorrectly claim that 1900 is a leap year (it was not; there was no 1900-02-29). |
constraints | 1 ⇐ Month ⇐ 12; 1 ⇐ Day ⇐ 31 |
Examples
expression | result | comment |
DATE(2005;1;31)=[.C7] | True | Simple date value. |
DATE(2005;12;31)-DATE(1904;1;1) | 37255 | Date differences are computed correctly. |
DATE(2004;2;29)=DATE(2004;2;28)+1 | True | 2004 was a leap year. |
DATE(2000;2;29)=DATE(2000;2;28)+1 | True | 2000 was a leap year. |
DATE(2005;3;1)=DATE(2005;2;28)+1 | True | 2005 was not a leap year. |
DATE(2017.5; 1; 2)=DATE(2017; 1; 2) | True | Fractional values for year are truncated |
DATE(2006; 2.5; 3)=DATE(2006; 2; 3) | True | Fractional values for month are truncated |
DATE(2006; 1; 3.5)=DATE(2006; 1; 3) | True | Fractional values for day are truncated |
DATE(2006; 13; 3)=DATE(2007; 1; 3) | True | Months > 12 roll over to year |
DATE(2006; 1; 32)=DATE(2006; 2; 1) | True | Days greater than month limit roll over to month |
DATE(2006; 25; 34)=DATE(2008;2;3) | True | Days and months roll over transitively |
DATE(2006;-1; 1)=DATE(2005;11;1) | True | Negative months roll year backward |
DATE(2006;4;-1)=DATE(2006;3;30) | True | Negative days roll month backward |
DATE(2006;-4;-1)=DATE(2005;7;30) | True | Negative days and months roll backward transitively |
DATE(2003;2;29)=DATE(2003;3;1) | True | Non-leap year rolls forward |
DATEVALUE
description | Return date serial number from given text |
syntax | DATEVALUE( Text D ) |
returns | Date |
semantics | This computes the serial number of the text string D, using the current locale. This function must accept ISO date format (YYYY-MM-DD), which is locale-independent. It is semantically equal VALUE(Date) if Date has a date format, since text matching a date format is automatically converted to a serial number when used as a Number. If the text of D has a combined date and time format, e.g. YYYY-MM-DD HH:MM:SS, the integer part of the date serial number is returned. If the text of Date does not have a date or time format, an implementation may return an error. See VALUE for more information on date formats. In an OpenDocument file, the calculation settings table:null-year and table:null-date affect this function. |
constraints | None |
Examples
expression | result | comment |
DATEVALUE("2004-12-25")=DATE(2004;12;25) | True | DATEVALUE |
DATEVALUE("2004-12-25 12:34:56")=DATE(2004;12;25) | True | Only the integer part is returned |
DATEVALUE("2004-12-25 12:34:56") | 2004-12-25 | Only the date part of the date time is returned |
DAY
description | Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. |
syntax | DAY( DateParam Date ) |
returns | Number |
semantics | Returns the day portion of the date. |
constraints | None |
Examples
expression | result | comment |
DAY(DATE(2006;5;21)) | 21 | Basic extraction. |
DAY("2006-12-15") | 12 | Text allowed too, since it’s a DateParam |
DAYS
description | Calculates the number of days between two dates. |
syntax | DAYS( Date endDate; Date startDate ) |
returns | Integer |
semantics | Computers the number of days between 2 dates |
constraints | None |
Examples
expression | result | comment |
DAYS(DATEVALUE("2004-12-23");DATEVALUE("2004-12-25")) | 2 | DAYS |
DAYS(DATEVALUE("2004-12-25");DATEVALUE("2004-12-23")) | -2 | DAYS |
DAYS360
description | 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 compute payments if your accounting system is based on twelve 30-day months. |
syntax | DAYS360(start_date,end_date,[method]) |
returns | Number |
semantics | Returns the day portion of the date. Method (optional): A logical value that specifies whether to use the U.S. or European method in the calculation. check the Microsoft docs for details. |
constraints | None |
Examples
expression | result | comment |
DAY(DATE(2006;5;21)) | 21 | Basic extraction. |
DAY("2006-12-15") | 12 | Text allowed too, since it’s a DateParam |
EDATE
description | Returns the serial number that represents 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. |
syntax | EDATE(start_date, months) |
returns | Date |
semantics | |
constraints |
Examples
expression | result | comment |
EDATE([DATE_FIELD],1) | 15-Feb-22 | The date, one month after the date above |
EDATE([DATE_FIELD],-1) | 15-Dec-22 | The date, one month before the date above |
EOMONTH
description | Returns the serial number for 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. |
syntax | EOMONTH(start_date, months) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
EOMONTH([DATE_FIELD]],1) | 2/28/2022 | Date of the last day of the month, one month after the date in [DATE_FIELD]. |
HOUR
description | Extract the hour (0 through 23) from a time. |
syntax | HOUR( TimeParam T ) |
returns | Number |
semantics | Semantics: Extract from T the hour value, 0 through 23, as per a 24-hour clock. This is equal to: DayFraction=(T-INT(T)) Hour=INT(DayFraction*24) |
constraints | None |
Examples
expression | result | comment |
HOUR(5/24) | 5 | 5/24ths of a day is 5 hours, aka 5AM. |
HOUR(5/24-1/(24*60*60)) | 4 | A second before 5AM, it’s 4AM. |
HOUR("14:00") | 14 | TimeParam accepts text |
MINUTE
description | Determines the sequential number for the minute of the hour (0-59) for the time value. |
syntax | MINUTE( DateTime ) |
returns | Integer |
semantics | Returns the sequential number for the minute of the hour |
constraints | None |
Examples
expression | result | comment |
MINUTE(DATETIMEVALUE("2004-12-23 12:25:45")) | 25 | MINUTE |
MONTH
description | Extract the month from a date |
syntax | MONTH( DateParam Date ) |
returns | Number |
semantics | Takes a date and returns the month portion. |
constraints | None |
Examples
expression | result | comment |
MONTH([.C7]) | 1 | Month extraction from date in cell. |
MONTH(DATE(2006;5;21)) | 5 | Month extraction from DATE() value. |
MONTHEND
description | Return the last day of the month. |
syntax | MONTHEND( Date ) |
returns | Date |
semantics | Returns the date for the last day of the month |
constraints | None |
Examples
expression | result | comment |
MONTHEND(DATEVALUE("2004-12-23")) | 2004-12-31 | MINUTE |
NETWORKDAYS
description | Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term. |
syntax | NETWORKDAYS(start_date, end_date, [holidays]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
NETWORKDAYS([FIELD1],[FIELD2]) | 110 | Number of workdays between the start (10/1/2021) and end date (3/1/2022). |
NETWORKDAYS([FIELD1],[FIELD2],[FIELD3]) | 109 | Number of workdays between the start (10/1/2012) and end date (3/1/2013), with the 11/22/2012 holiday as a non-working day. |
NOW
description | Return the serial number of the current date and time. |
syntax | NOW() |
returns | DateTime |
semantics | This returns the current day and time serial number, using the current locale. If you want only the serial number of the current day, use TODAY. |
constraints | None |
Examples
expression | result | comment |
NOW()>DATE(2006;1;3) | True | NOW constantly changes, but we know it’s beyond this date. |
INT(NOW())=TODAY() | True | NOW() is part of TODAY(). WARNING: this test is allowed to fail if the locale transitions through midnight while computing this test; this failure is incredibly unlikely to occur in practice. |
NPER
description | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. |
syntax | NPER(rate,pmt,pv,[fv],[type]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
NPER([FIELD1]/12, [FIELD2], [FIELD3], [FIELD4], 1) | 59.673865 | Periods for the investment with the above terms |
PREVWEEKDAY
description | Return the date of the previous weekday from the current date. |
syntax | PREVWEEKDAY( Integer weekdayDefinition ) |
returns | Date |
semantics | weekdayDefinition fixes the start of the weekend and the type of calculation to be used. (1 = weekend = saturday + sunday, 2 = weekend = friday + saturday) |
constraints | weekdayDefinition in values 1 or 2 |
Examples
expression | result | comment |
PREVWEEKDAY(1) | 2019-05-07 | If this were run on 2019-05-08. |
SECOND
description | Determines the sequential number for the second of the minute (0-59) for the time value. |
syntax | SECOND( DateTime ) |
returns | Integer |
semantics | Returns the sequential number for the second of the minute |
constraints | None |
Examples
expression | result | comment |
SECOND(DATETIMEVALUE("2004-12-23 12:25:45")) | 45 | MINUTE |
TIME
description | Construct time from hours, minutes, and seconds. |
syntax | TIME( Number hours ; Number minutes ; Number seconds ) |
returns | Time |
semantics | Returns the fraction of the day consumed by the given time, i.e.: ((hours*60*60)+(minutes*60)+seconds)/(24*60*60) Time is a subtype of number, where a time value of 1 = 1 day = 24 hours. Note that the time inside one day is a fraction between 0 and 1, so typical implementations will only be able to compute approximations of the correct time value. Implementations may first perform INT() on the hour, minute, and second before doing the calculation. Therefore, only integer values are portable between implementations. Hours, minutes, and seconds may be arbitrary numbers (they must not be limited to the ranges 0..24, 0..59, or 0..60 respectively). Note that in typical implementations, a value displayed as time has its integer portion discarded and then time is computed; for computational purposes, though, the entire value is retained. |
constraints | None |
Examples
expression | result | comment |
TIME(0;0;0) | 0 | All zero arguments becomes midnight, 12:00:00 AM. |
TIME(23;59;59)*60*60*24 | 86399±ε | This is 11:59:59 PM. |
TIME(11;125;144)*60*60*24 | 47244±ε | Seconds and minutes roll over transitively; this is 1:07:24 PM. |
TIME(11;0; -117)*60*60*24 | 39483±ε | Negative seconds roll minutes backwards, 10:58:03 AM |
TIME(11;-117;0)*60*60*24 | 32580±ε | Negative minutes roll hours backwards, 9:03:00 AM |
TIME(11;-125;-144)*60*60*24 | -31956±ε | Negative seconds and minutes roll backwards transitively, 8:52:36 AM |
TIMEVALUE
description | Returns an internal number for a text having a time format. |
syntax | TIMEVALUE( Text D ) |
returns | Integer |
semantics | This computes the serial number of the text string D, using the current locale. This function acceptS ISO time format (HH:mm:ss), which is locale-independent. |
constraints | None |
Examples
expression | result | comment |
TIMEVALUE("12:56:45") | 12:56:45 | TIMEVALUE |
TIMEVALUE("2004-12-25 12:56:45") | 12:56:45 | TIMEVALUE |
TODAY
description | Return the serial number of today |
syntax | TODAY() |
returns | Date |
semantics | This returns the current day’s serial number, using current locale. This only returns the date, not the datetime value; if you need the specific time of day as well, use NOW(). |
constraints | None |
Examples
expression | result | comment |
TODAY()>DATE(2006;1;3) | True | Every date TODAY() changes, but we know it’s beyond this date. |
INT(TODAY())=TODAY() | True | TODAY() returns an integer. WARNING: this test is allowed to fail if the locale transitions through midnight while computing this test; because TODAY() is referenced twice, in some implementations this would result in a race condition) This is incredibly unlikely to occur in practice. |
WEEKDAY
description | Extract the day of the week from a date; if text, uses current locale to convert to a date. |
syntax | WEEKDAY( DateParam Date [ ; Integer Type = 1 ] ) |
returns | Number |
semantics | Returns the day of the week from a date, as a number from 0 through 7. The exact meaning depends on the value of Type: 1.When Type is 1, Sunday is the first day of the week, with value 1; Saturday has value 7. 2.When Type is 2, Monday is the first day of the week, with value 1; Sunday has value 7. 3.When Type is 3, Monday is the first day of the week, with value 0; Sunday has value 6. |
constraints | None |
Examples
expression | result | comment |
WEEKDAY(DATE(2006;5;21)) | 1 | Year-month-date format |
WEEKDAY(DATE(2005;1;1)) | 7 | Saturday. |
WEEKDAY(DATE(2005;1;1);1) | 7 | Saturday. |
WEEKDAY(DATE(2005;1;1);2) | 6 | Saturday. |
WEEKDAY(DATE(2005;1;1);3) | 5 | Saturday. |
WEEKNUM
description | 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. There are two systems used for this function: System 1 The week containing January 1 is the first week of the year, and is numbered week 1. System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system. |
syntax | WEEKNUM(serial_number,[return_type]) |
returns | Integer |
semantics | |
constraints |
Examples
expression | result | comment |
WEEKNUM("3/9/2012") | 10 | Number of the week in the year that 3/9/2012 occurs, based on weeks beginning on Sunday (default). |
WEEKNUM("3/9/2012",2) | 11 | Number of the week in the year that 3/9/2012 occurs, based on a week beginning on Monday (the second argument, 2). |
WORKDAY
description | Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. |
syntax | WORKDAY(start_date, days, [holidays]) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
WORKDAY([DATE_FIELD],[NB_DAYS_FIELD]) |
YEAR
description | Extract the year from a date given in the current locale of the application. |
syntax | YEAR( DateParam D ) |
returns | Number |
semantics | Parses a date-formatted string in the current locale’s format and returns the year portion. If a year is given as a two-digit number, as in "05-21-15", then the year returned is either 1915 or 2015, depending upon the a break point in the calculation context. In an OpenDocument document, this break point is determined by table:null-year. Applications shall support extracting the year from a date beginning in 1900. Three-digit year numbers precede adoption of the Gregorian calendar, and may return either an error or the year number. Four-digit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an error or the year number. Four-digit year numbers following 1582 should return the year number. |
constraints | None |
Examples
expression | result | comment |
YEAR(DATE(1904;1;1)) | 1904 | Extracts year from a given date. |
YEARFRAC
description | YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year’s benefits, or obligations to assign to a specific term. |
syntax | YEARFRAC(start_date, end_date, [basis]) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
YEARFRAC("1/1/2012","7/30/2012") | 0.58055556 | Fraction of the year between 1/1/2012 and 7/30/12, omitting the Basis argument. |
YEARFRAC("1/1/2012","7/30/2012",1) | 0.57650273 | Fraction between same dates, using the Actual/Actual basis argument. Because 2012 is a Leap year, it has a 366 day basis. |
YEARFRAC("1/1/2012","7/30/2012",3) | 0.57808219 | Fraction between same dates, using the Actual/365 basis argument. Uses a 365 day basis. |
Financial
PMT
description | PMT, one of the financial functions, calculates the payment for a loan based on constant payments and a constant interest rate. |
syntax | PMT(rate, nper, pv, [fv], [type]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
PMT([FIELD1]/12,[FIELD2],[FIELD3]) | ($1,037.03) | Monthly payment for a loan with terms specified as arguments in [FIELD1]:[FIELD3]. |
PV
description | PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that’s your investment goal. |
syntax | PV(rate, nper, pmt, [fv], [type]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
PV([FIELD2]/12, 12*[FIELD3], [FIELD1], , 0) | ($59,777.15) | Present value of an annuity with the terms in [FIELD1]:[FIELD3]. |
Information
CHOOSE
description | Uses an index to return a value from a list of values. Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num. |
syntax | CHOOSE( Integer Index ; { Any Value }+ ) |
returns | Any |
semantics | Uses Index to determine which value, from a list of values, to return. If Index is 1, CHOOSE returns the first Value; if Index is 2, CHOOSE returns the second value, and so on. Note that the Values may be formula expressions. Expression paths of parameters other than the one chosen are not calculated or evaluated for side effects. |
constraints | Returns an error if Index < 1 or if there is no corresponding value in the list of Values. |
Examples
expression | result | comment |
CHOOSE(3;"Apple";"Orange";"Grape";"Perry") | "Grape" | Simple selection. |
CHOOSE(0;"Apple";"Orange";"Grape";"Perry") | Error | Index has to be at least 1. |
CHOOSE(5;"Apple";"Orange";"Grape";"Perry") | Error | Index can’t refer to non-existent entry. |
CHOOSE(2;SUM([.B4:.B5]);SUM([.B5])) | 3 | Simple selection, using a set of formulas. |
SUM(CHOOSE(2;[.B4:.B5];[.B5])) | 3 | CHOOSE can pass references |
COUNT
description | The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. |
syntax | COUNT( { Any Value }+ ) |
returns | Integer |
semantics | Counts the parameters where the parameter’s value is parseable as a number. |
constraints | None |
Examples
expression | result | comment |
COUNT(1;2;3) | 3 | Simple count. |
COUNT("1","ABC","9EF") | 1 | Only counts parameters that are numbers. |
COUNTA
description | The COUNTA function counts the number of cells that are not empty in a range. |
syntax | COUNTA( { Any Value }+ ) |
returns | Integer |
semantics | Counts the parameters where the parameter’s value is not null. |
constraints | None |
Examples
expression | result | comment |
COUNTA(1,2,3) | 3 | Simple count. |
COUNTA("1","ABC","9EF") | 3 | Simple count. |
COUNTA("1","ABC","9EF") | 3 | Does not count the null 3rd parameter. |
COUNTBLANK
description | Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells. |
syntax | COUNTBLANK( { Any Value }+ ) |
returns | Integer |
semantics | Counts the parameters where the parameter’s value is null. |
constraints | None |
Examples
expression | result | comment |
COUNTBLANK(1;;;2;3) | 2 | Simple count. |
COUNTBLANK(1;2;3) | 0 | Simple count. |
COUNTBLANK("1";"ABC";"9EF") | 0 | Simple count. |
COUNTBLANK("1";"ABC";;"9EF") | 1 | Simple count. |
DELTA
description | Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. Use this function to filter a set of values. For example, by summing several DELTA functions you calculate the count of equal pairs. This function is also known as the Kronecker Delta function. |
syntax | DELTA(number1, [number2]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
DELTA(5, 4) | 0 | Checks whether 5 equals 4 |
DELTA(5, 5) | 1 | Checks whether 5 equals 5 |
DELTA(0.5, 0) | 0 | Checks whether 0.5 equals 0 |
ERROR
description | Raises an evaluation error with the given message. |
syntax | ERROR( Text errorMessage; Text errorCode ) |
returns | Error |
semantics | errorCode defaults to -1 if not set. |
constraints | None |
Examples
expression | result | comment |
ERROR("Threw an error";1) | Error | Simple error. |
ISBLANK
description | Return TRUE if the referenced cell is blank, else return FALSE |
syntax | ISBLANK( Scalar X ) |
returns | Logical |
semantics | If X is of type Number, Text, or Logical, return FALSE. If X is a reference to a cell, examine the cell; if it is blank (has no value), return TRUE, but if it has a value, return FALSE. A cell with the empty string is not considered blank. |
constraints | None |
Examples
expression | result | comment |
ISBLANK(1) | False | Numbers return false. |
ISBLANK("") | False | Text, even empty string, returns false. |
ISBLANK([.B8]) | True | Blank cell is true. |
ISBLANK([.B7]) | False | Non-blank cell is false. |
ISERR
description | Return True if the parameter has type Error and is not NA, else return False. |
syntax | ISERR( Scalar X ) |
returns | Logical |
semantics | If X is of type Error, and ISNA(X) is not true, returns TRUE. Otherwise it returns FALSE. Note that this function returns False if given NA(); if this is not desired, use ISERROR. Note that this function does not propagate error values. ISERR(X) is the same as: IF(ISNA(X),FALSE(),ISERROR(X)) |
constraints | None |
Examples
expression | result | comment |
ISERR(1/0) | True | Error values other than NA() return true. |
ISERR(NA()) | False | NA() does NOT return True. |
ISERR("#N/A") | False | Text is not an error. |
ISERR(1) | False | Numbers are not an error. |
ISERROR
description | Return TRUE if the parameter has type Error, else return FALSE |
syntax | ISERROR( Scalar X ) |
returns | Logical |
semantics | If X is of type Error, returns TRUE, else returns FALSE. Note that this function returns True if given NA(); if this is not desired, use ISERR. Note that this function does not propagate error values. |
constraints | None |
Examples
expression | result | comment |
ISERROR(1/0) | True | Error values return true. |
ISERROR(NA()) | True | Even NA(). |
ISERROR("#N/A") | False | Text is not an error. |
ISERROR(1) | False | Numbers are not an error. |
ISERROR(CHOOSE(0; "Apple"; "Orange"; "Grape"; "Perry")) | True | If CHOOSE given out-of-range value, ISERROR needs to capture it. |
ISEVEN
description | Return TRUE if the value is even, else return FALSE |
syntax | ISEVEN( Number X ) |
returns | Logical |
semantics | First, compute X1=TRUNC(X). Then, if X is even (a division by 2 has a remainder of 0), return True, else return False. The result is implementation-defined if given a logical value; an application may return either an Error or the result of converting the logical value to a number (per Conversion to Number). |
constraints | X must not be Logical |
Examples
expression | result | comment |
ISEVEN(2) | True | 2 is even, because (2 modulo 2) = 0 |
ISEVEN(6) | True | 6 is even, because (6 modulo 2) = 0 |
ISEVEN(2.1) | True | |
ISEVEN(2.5) | True | |
ISEVEN(2.9) | True | TRUNC(2.9)=2, and 2 is even. |
ISEVEN(3) | False | 3 is not even. |
ISEVEN(3.9) | False | TRUNC(3.9)=3, and 3 is not even. |
ISEVEN(-2) | True | |
ISEVEN(-2.1) | True | |
ISEVEN(-2.5) | True | |
ISEVEN(-2.9) | True | TRUNC(-2.9)=-2, and -2 is even. |
ISEVEN(-3) | False | |
ISEVEN(NA()) | NA | |
ISEVEN(0) | True |
ISLOGICAL
description | Return TRUE if the parameter has type Logical, else return FALSE |
syntax | ISLOGICAL( Scalar X ) |
returns | Logical |
semantics | If X is of type Logical, returns TRUE, else FALSE. For applications that do not have a distinct logical type, also ISNUMBER(X) will return TRUE. |
constraints | None |
Examples
expression | result | comment |
ISLOGICAL(TRUE()) | True | Logical values return true. |
ISLOGICAL(FALSE()) | True | Logical values return true. |
ISLOGICAL("TRUE") | False | Text values are not logicals, even if they can be converted. |
ISNA
description | Return True if the parameter is of type NA, else return False. |
syntax | ISERR( Scalar X ) |
returns | Logical |
semantics | If X is NA, return True, else return False. Note that if X is a reference, the value being referenced is considered. This function does not propagate error values. |
constraints | None |
Examples
expression | result | comment |
ISNA(1/0) | False | Error values other than NA() return False – the error does not propagate. |
ISNA(NA()) | True | By definition |
ISNA(#N/A) | True | By definition |
ISNA("#N/A") | False | Text is not NA |
ISNA(1) | False | Numbers are not NA |
ISNONTEXT
description | Return TRUE if the parameter does not have type Text, else return FALSE |
syntax | ISNONTEXT( Scalar X ) |
returns | Logical |
semantics | If X is of type Text, returns TRUE, else FALSE. If X is a reference, examines what X references. References to blank cells are NOT considered text, so a reference to a blank cell will return TRUE. ISNONTEXT(X) is the same as: NOT(ISTEXT(X)) |
constraints | None |
Examples
expression | result | comment |
ISNONTEXT(1) | True | Numbers are not text |
ISNONTEXT(TRUE()) | True | Logical values are not text. |
ISNONTEXT("1") | False | Text values are text, even if they can be converted into a number. |
ISNONTEXT([.B7]) | False | B7 is a cell with text |
ISNONTEXT([.B9]) | True | B9 is an error, thus not text |
ISNONTEXT([.B8]) | True | B8 is a blank cell, so this will return TRUE |
ISNUMBER
description | Return TRUE if the parameter has type Number, else return FALSE |
syntax | ISNUMBER( Scalar X ) |
returns | Logical |
semantics | If X is of type Number, returns TRUE, else FALSE. Level 1 implementations may not have a distinguished logical type; in such implementations, ISNUMBER(TRUE()) is TRUE. |
constraints | None |
Examples
expression | result | comment |
ISNUMBER(1) | True | Numbers are numbers |
ISNUMBER("1") | False | Text values are not numbers, even if they can be converted into a number. |
ISODD
description | Return TRUE if the value is even, else return FALSE |
syntax | ISODD( Number X ) |
returns | Logical |
semantics | First, compute X1=TRUNC(X). Then, if X is odd (a division by 2 has a remainder of 1), return True, else return False. The result is implementation-defined if given a logical value; an application may return either an Error or the result of converting the logical value to a number (per Conversion to Number). |
constraints | X must not be Logical |
Examples
expression | result | comment |
ISODD(3) | True | 3 is odd, because (3 modulo 2) = 1 |
ISODD(5) | True | 5 is odd, because (5 modulo 2) = 1 |
ISODD(3.1) | True | TRUNC(3.1)=3, and 3 is odd |
ISODD(3.5) | True | 3 is odd. |
ISODD(3.9) | True | TRUNC(3.9)=3, and 3 is odd. |
ISODD(4) | False | |
ISODD(4.9) | False | |
ISODD(-3) | True | |
ISODD(-3.1) | True | |
ISODD(-3.5) | True | |
ISODD(-3.9) | True | TRUNC(-3.9)=-3, and -3 is odd. |
ISODD(-4) | False | |
ISODD(NA()) | NA | |
ISODD(0) | False | |
ISODD(1) | True | |
ISODD(2) | False | |
ISODD(2.9) | False |
ISREF
description | Return True if the parameter is of type reference, else return False. |
syntax | ISREF( Any X ) |
returns | Logical |
semantics | If X is of type Reference or ReferenceList, return True, else return False. Note that unlike nearly all other functions, when given a reference this function does not then examine the value being referenced. Some functions and operators return references, and thus ISREF will return True when given their results. X may be a ReferenceList, in which case ISREF returns True. |
constraints | None |
Examples
expression | result | comment |
ISREF([.B3]) | True | |
ISREF([.B3]:[.C4]) | True | The range operator produces references |
ISREF(1) | False | Numbers are not references |
ISREF("A1") | False | Text is not a reference, even if it looks a little like one |
ISREF(NA()) | NA | Errors propagate through this function |
ISTEXT
description | Return TRUE if the parameter has type Text, else return FALSE |
syntax | ISTEXT( Scalar X ) |
returns | Logical |
semantics | If X is of type Text, returns TRUE, else FALSE. References to blank cells are NOT considered text. |
constraints | None |
Examples
expression | result | comment |
ISTEXT(1) | False | Numbers are not text |
ISTEXT("1") | True | Text values are text, even if they can be converted into a number. |
NA
description | Return the constant error value #N/A. |
syntax | NA() |
returns | Error |
semantics | This function takes no arguments and returns the error NA. |
constraints | Must have 0 parameters |
Examples
expression | result | comment |
ISERROR(NA()) | True | NA is an error. |
ISNA(NA()) | True | Obviously, if this doesn’t work, NA() or ISNA() is broken. |
ISNA(5+NA()) | True | NA propagates through various functions and operators, just like any other error type. |
Logical
AND
description | Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE. |
syntax | AND( { Logical|NumberSequenceList L }+ ) |
returns | Logical |
semantics | Computes the logical AND of the parameters. If all parameters are True, returns True; if any are False, returns False. When given one parameter, this has the effect of converting that one parameter into a logical value. When given zero parameters, applications may return a Logical value or an error. Also in array context a logical AND of all arguments is computed, range or array parameters are not evaluated as a matrix and no array is returned. This behavior is consistent with functions like SUM. To compute a logical AND of arrays per element use the * operator in array context. |
constraints | Must have 1 or more parameters |
Examples
expression | result | comment |
AND(FALSE(),FALSE()) | False | Simple AND. |
AND(FALSE(),TRUE()) | False | Simple AND. |
AND(TRUE(),FALSE()) | False | Simple AND. |
AND(TRUE(),TRUE()) | True | Simple AND. |
AND(TRUE(),NA()) | NA | Returns an error if given one. |
AND(1,TRUE()) | True | Nonzero considered TRUE. |
AND(0,TRUE()) | False | Zero considered FALSE. |
AND(TRUE(),TRUE(),TRUE()) | True | More than two parameters okay. |
AND(TRUE()) | True | One parameter okay - simply returns it. |
FALSE
description | Returns constant FALSE |
syntax | FALSE() |
returns | Logical |
semantics | Returns logical constant FALSE. Although this is syntactically a function call, semantically it is a constant, and typical applications optimize this because it is a constant. Note that this may or may not be equal to 0 when compared using “=”. It always has the value of 0 if used in a context requiring Number (because of the automatic conversions), so if ISNUMBER(FALSE()), then it must have the value 0. |
constraints | Must have 0 parameters |
Examples
expression | result | comment |
FALSE() | False | Constant. |
IF(ISNUMBER(FALSE()),FALSE()=0;FALSE()) | True | Applications that implement logical values as 0/1 must map FALSE() to 0 |
2+FALSE() | 2 | FALSE converts to 0 in Number context |
IF
description | Return one of two values, depending on a condition |
syntax | IF( Logical Condition [ , [ Any IfTrue ] [ , [ Any IfFalse ] ] ] ) |
returns | Any |
semantics | Computes Condition. If it is TRUE, it returns IfTrue, else it returns IfFalse. If there is only 1 parameter, IfTrue is considered to be TRUE(). If there are less than 3 parameters, IfFalse is considered to be FALSE(). Thus the 1 parameter version converts Condition into a Logical value. If there are 2 or 3 parameters but the second parameter is null (two consecutive ; semicolons), IfFalse is considered to be 0. If there are 3 parameters but the third parameter is null, IfFalse is considered to be 0. This function only evaluates IfTrue, or ifFalse, and never both; that is to say, it short-circuits. |
constraints | None. |
Examples
expression | result | comment |
IF(FALSE(),7,8) | 8 | Simple if. |
IF(TRUE(),7,8) | 7 | Simple if. |
IF(TRUE(),"HI",8) | "HI" | Can return strings, and the two sides need not have equal types |
IF(1,7,8) | 7 | A non-zero is considered true. |
IF(5,7,8) | 7 | A non-zero is considered true. |
IF(0,7,8) | 8 | A zero is considered false. |
IF(TRUE(),[.B4],8) | 2 | The result can be a reference. |
IF(TRUE(),[.B4]+5,8) | 7 | The result can be a formula. |
IF("x",7,8) | Error | Condition has to be convertible to Logical. |
IF("1",7,8) | Error | Condition has to be convertible to Logical. |
IF("",7,8) | Error | Condition has to be convertible to Logical; empty string is not the same as False |
IF(FALSE(),7) | FALSE | Default IfFalse is FALSE |
IF(3) | TRUE | Default IfTrue is TRUE |
IF(FALSE(),7,) | 0 | Empty parameter is considered 0 |
IF(TRUE(),7) | 0 | Empty parameter is considered 0 |
IF(TRUE(),4,1/0) | 4 | If condition is true, ifFalse is not considered – even if it would produce Error. |
IF(FALSE(),1/0,5) | 5 | If condition is false, ifTrue is not considered – even if it would produce Error. |
IFNA
description | Returns the value unless it is null, then return an alternate value |
syntax | IFNA( Text string; Text alternateValue ) |
returns | Text |
semantics | If string is not null return string, else return alternateValue. |
constraints | None |
Examples
expression | result | comment |
IFNA(,"Null String") | Null String | |
IFNA("ABC","Null String") | ABC |
IFS
description | The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions. |
syntax | IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
IFS([FIELD]>89,"A",[FIELD]>79,"B",[FIELD]>69,"C",[FIELD]>59,"D",TRUE,"F") | one character | IF([FIELD] is Greater Than 89, then return a "A", IF [FIELD] is Greater Than 79, then return a "B", and so on and for all other values less than 59, return an "F"). |
NOT
description | Compute logical NOT |
syntax | NOT( Logical L ) |
returns | Logical |
semantics | Computes the logical NOT. If given TRUE, returns FALSE; if given FALSE, returns TRUE. |
constraints | Must have 1 parameter |
Examples
expression | result | comment |
NOT(FALSE()) | True | Simple NOT, given FALSE. |
NOT(TRUE()) | False | Simple NOT, given TRUE. |
NOT(1/0) | Error | NOT returns an error if given an error value |
OR
description | Compute logical OR of all parameters. |
syntax | OR( { Logical|NumberSequenceList L }+ ) |
returns | Logical |
semantics | Computes the logical OR of the parameters. If all parameters are False, it shall return False; if any are True, it shall returns True. When given one parameter, this has the effect of converting that one parameter into a logical value. When given zero parameters, applications may return a Logical value or an error. Also in array context a logical OR of all arguments is computed, range or array parameters are not evaluated as a matrix and no array is returned. This behavior is consistent with functions like SUM. To compute a logical OR of arrays per element use the + operator in array context. |
constraints | Must have 1 or more parameters |
Examples
expression | result | comment |
OR(FALSE(),FALSE()) | False | Simple OR. |
OR(FALSE(),TRUE()) | True | Simple OR. |
OR(TRUE(),FALSE()) | True | Simple OR. |
OR(TRUE(),TRUE()) | True | Simple OR. |
OR(FALSE(),NA()) | NA | Returns an error if given one. |
OR(FALSE(),FALSE(),TRUE()) | True | More than two parameters okay. |
OR(TRUE()) | True | One parameter okay - simply returns it |
TRUE
description | Returns constant TRUE |
syntax | TRUE() |
returns | Logical |
semantics | Returns logical constant TRUE. Although this is syntactically a function call, semantically it is a constant, and typical applications optimize this because it is a constant. Note that this may or may not be equal to 1 when compared using “=”. It always has the value of 1 if used in a context requiring Number (because of the automatic conversions), so if ISNUMBER(TRUE()), then it must have the value 1. |
constraints | Must have 0 parameters |
Examples
expression | result | comment |
TRUE() | True | Constant. |
IF(ISNUMBER(TRUE()),TRUE()=1,TRUE()) | True | Applications that implement logical values as 0/1 must map TRUE() to 1 |
2+TRUE() | 3 | TRUE converts to 1 in Number context |
Mathematical
ABS
description | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
syntax | ABS( NUMBER N ) |
returns | Number |
semantics | If N < 0, returns -N, otherwise returns N. |
constraints |
Examples
expression | result | comment |
ABS(2) | 2 | Positive values return unchanged. |
ABS(-2) | 2 | If less than zero, return negation |
ABS([FIELD_NAME]) | 4 (sample) | the absolute value of a (numeric) field with name FIELD_NAME is returned. |
ACOS
description | Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. |
syntax | ACOS( NUMBER N ) |
returns | |
semantics | |
constraints | The cosine of the angle you want and must be from -1 to 1. |
Examples
expression | result | comment |
ACOS(-0.5) | 2.094395102 | Arccosine of -0.5 in radians, 2*pi/3 |
ACOSH
description | Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number. |
syntax | ACOSH( NUMBER N ) |
returns | |
semantics | |
constraints | The field or number passed as an argument needs to be any real number equal to or greater than 1 |
Examples
expression | result | comment |
ACOSH(1) | 0 | Inverse hyperbolic cosine of 1 |
ASIN
description | Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
syntax | ASIN( Number N ) |
returns | Number |
semantics | Returns the inverse sine of N. |
constraints | Value N must be between -1 and 1 |
Examples
expression | result | comment |
ASIN(0.5) | 0.5235987755 |
ASINH
description | Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number, so ASINH(SINH(number)) equals number. |
syntax | ASIN( Number N ) |
returns | Number |
semantics | Returns the inverse sine of N. |
constraints | Value N must be between -1 and 1 |
Examples
expression | result | comment |
ASIN(0.5) | 0.5235987755 |
ATAN
description | Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
syntax | ATAN( Number N ) |
returns | Number |
semantics | Returns the arc tangent of N. |
constraints | None |
Examples
expression | result | comment |
ATAN(10) | 1.4711276743 |
ATAN2
description | Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi. |
syntax | ATAN2( Number X; Number Y ) |
returns | Number |
semantics | X is the x coordinate, Y is the y coordinate |
constraints | None |
Examples
expression | result | comment |
ATAN2(10;5) | 0.785398163 | |
ATAN2(1, 1) | 0.785398163 | Arctangent of the point 1,1 in radians, pi/4 |
ATANH
description | Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number, so ATANH(TANH(number)) equals number. |
syntax | ATANH( Number N ) |
returns | Number |
semantics | X is the x coordinate, Y is the y coordinate |
constraints | None |
Examples
expression | result | comment |
ATANH(0.76159416) | 1.00000001 | Inverse hyperbolic tangent of 0.76159416 |
ATANH(-0.1) | -0.100335348 |
AVEDEV
description | Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. |
syntax | AVEDEV( Number X, Number Y, …) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
AVEDEV(4,5,6,7,5,4,3) | 1.020408 |
AVERAGE
description | Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers. |
syntax | AVERAGE( { NumberSequence N }+ ) |
returns | Number |
semantics | Computes SUM(List) / COUNT(List). |
constraints | At least one number included. Returns an error if no numbers provided. |
Examples
expression | result | comment |
AVERAGE(2;4) | 3 | Simple average |
CEILING
description | 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. |
syntax | CEILING( Number N, significance) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
CEILING(2.5, 1) | Rounds 2.5 up to nearest multiple of 1 | |
CEILING(-2.5, -2) | -4 | Rounds -2.5 up to nearest multiple of -2 |
CEILING(0.234, 0.01) | 0.24 | Rounds 0.234 up to the nearest multiple of 0.01 |
COMBIN
description | 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. |
syntax | COMBIN(Number N, number_chosen) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
COMBIN(8,2) | 28 | Possible two-person teams that can be formed from 8 candidates. |
COMPLEX
description | Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj. |
syntax | COMPLEX(real_num, i_num, [suffix]) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
COMPLEX(3,4) | 3+4i | Complex number with 3 and 4 as the real and imaginary coefficients |
COMPLEX(3,4,"j") | 3+4j | Complex number with 3 and 4 as the real and imaginary coefficients, and j as the suffix |
COMPLEX(0,1) | i | Complex number with 0 and 1 as the real and imaginary coefficients |
COS
description | Returns the cosine for the specified number. |
syntax | COS( Number ) |
returns | Number |
semantics | Number is the angle in the radians for which the cosine is to be returned. |
constraints | None |
Examples
expression | result | comment |
COS(10) | -0.839071529 |
COSH
description | Returns the hyperbolic cosine of a number. |
syntax | COSH( Number ) |
returns | Number |
semantics | Number is the angle in the radians for which the cosine is to be returned. |
constraints | None |
Examples
expression | result | comment |
COSH(4) | 27.308233 | Hyperbolic cosine of 4 |
DEGREES
description | Converts radians into degrees. |
syntax | DEGREES(angle) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
DEGREES(PI()) | 180 | Degrees of pi radians |
EVEN
description | Rounds a number up to the nearest even integer. Rounding is away from zero. |
syntax | EVEN( Number N ) |
returns | Number |
semantics | Returns the even integer whose sign is the same as N’s and whose absolute value is greater than or equal to the absolute value of N. That is, if rounding is required, it is rounded away from zero. |
constraints | None |
Examples
expression | result | comment |
EVEN(6) | 6 | Positive even integers remain unchanged. |
EVEN(-4) | -4 | Negative even integers remain unchanged. |
EVEN(1) | 2 | Non-even positive integers round up. |
EVEN(0.3) | 2 | Positive floating values round up. |
EVEN(-1) | -2 | Non-even negative integers round down. |
EVEN(-0.3) | -2 | Negative floating values round down. |
EVEN(0) | 0 | Since zero is even, EVEN(0) returns zero. |
EXP
description | Calculates the exponent for basis e. |
syntax | EXP( Number ) |
returns | Number |
semantics | Number is the exponent applied to base e. |
constraints | None |
Examples
expression | result | comment |
EXP(10) | 22026.46579 |
FACT
description | Returns the factorial of a number. The factorial of a number is equal to 1*2*3*…* number. |
syntax | FACT(number) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
FACT(5) | 120 | Factorial of 5, or 1*2*3*4*5 |
FACT(1.9) | 1 | Factorial of the integer of 1.9 |
FACTDOUBLE
description | Returns the double factorial of a number. |
syntax | FACTDOUBLE(number) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
FACTDOUBLE(5) | 120 | Double factorial of 6. For 6, an even number, the double factorial is equivalent to 6*4*2; using this equation: n!! = n*(n-2)*(n-4)…(4)(2) |
FACTDOUBLE(5) | 120 | Double factorial of 7. For 7, an odd number, the double factorial is equivalent to 7*5*3; using this equation: n!! = n*(n-2)*(n-4)…(3)(1) |
FIXED
description | Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. |
syntax | FIXED(number, [decimals], [no_commas]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
FIXED([NUMBER_FIELD]], 1) | 1,234.6 | Rounds the number in [NUMBER_FIELD] one digit to the right of the decimal point. |
FIXED([NUMBER_FIELD]], -1, TRUE) | -1230 | Rounds the number in [NUMBER_FIELD] one digit to the left of the decimal point, without commas (the TRUE argument). |
FIXED("190") | 190.000 | Decimal places not specified |
FIXED("190.89";1) | 190.9 | Rounds to 1 decimal place |
FIXED("1190.89";1;TRUE()) | 1190.9 | No grouping character |
FIXED("1190.89";1;FALSE()) | 1,190.9 | With grouping character |
FLOOR
description | Rounds number down, toward zero, to the nearest multiple of significance. |
syntax | FLOOR(number, significance) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
FLOOR(3.7,2) | 2 | Rounds 3.7 down to nearest multiple of 2. |
FLOOR(-2.5,-2) | -2 | Rounds -2.5 down to nearest multiple of -2. |
FLOOR(0.234,0.01) | 0.23 | Rounds 0.234 down to the nearest multiple of 0.01. |
HEX2DEC
description | Converts a hexadecimal number to decimal. |
syntax | HEX2DEC(number) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
HEX2DEC("FFFFFFFF5B") | -165 | Converts hexadecimal FFFFFFFF5B to decimal |
HYPERLINK
description | The HYPERLINK function creates a shortcut that opens a document stored on a network server or opens a link on an intranet or the Internet |
syntax | HYPERLINK(link_location, [friendly_name]) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
HYPERLINK("https://hop.apache.org/manual/latest/pipeline/transforms/formula.html", "Formula Transform") | returns a hyperlink to the Formula Transform doc page | returns a hyperlink to the Formula Transform doc page |
IMAGINARY
description | Returns the imaginary coefficient of a complex number in x + yi or x + yj text format. |
syntax | |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
IMAGINARY("3+4i") | 4 | Imaginary coefficient of the complex number 3+4i |
IMAGINARY("0-j") | -1 | Imaginary coefficient of the complex number 0-j |
IMAGINARY(4) | 0 | Imaginary coefficient 4 |
IMREAL
description | Returns the real coefficient of a complex number in x + yi or x + yj text format. |
syntax | |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
IMREAL("6-9i") | 6 | Real coefficient of 6-9i |
INT
description | Rounds a number down to the nearest integer. |
syntax | |
returns | Integer |
semantics | |
constraints |
Examples
expression | result | comment |
INT(8.9) | 8 | Rounds 8.9 down |
INT(-8.9) | -9 | Rounds -8.9 down. Rounding a negative number down rounds it away from 0. |
INT(2) | 2 | Positive integers remain unchanged |
INT(-3) | -3 | Negative integers remain unchanged |
INT(1.2) | 1 | Positive floating values are truncated |
INT(1.7) | 1 | It doesn’t matter if the fractional part is > 0.5 |
INT(-1.2) | -2 | Negative floating values round towards negative infinity |
INT((1/3)*3) | 1 | Naive users expect INT to "correctly" make integers even if there are limits on precision. |
INT
description | Rounds a number down to the nearest integer. |
syntax | INT( Number N ) |
returns | Number |
semantics | Returns the nearest integer whose value is less than or equal to N. Rounding is towards negative infinity. |
constraints | None |
Examples
expression | result | comment |
INT(8.9) | 8 | Rounds 8.9 down |
INT(-8.9) | -9 | Rounds -8.9 down. Rounding a negative number down rounds it away from 0. |
INT(2) | 2 | Positive integers remain unchanged |
INT(-3) | -3 | Negative integers remain unchanged |
INT(1.2) | 1 | Positive floating values are truncated |
INT(1.7) | 1 | It doesn’t matter if the fractional part is > 0.5 |
INT(-1.2) | -2 | Negative floating values round towards negative infinity |
INT((1/3)*3) | 1 | Naive users expect INT to "correctly" make integers even if there are limits on precision. |
LN
description | Calculates the natural logarithm of a number. |
syntax | LN( Number ) |
returns | Number |
semantics | Returns the natural logarithm of number. |
constraints | Number must be greater than 0 |
Examples
expression | result | comment |
LN(10) | 2.30258509 |
LOG
description | Returns the logarithm of a number to the base you specify. |
syntax | LOG(number, [base]) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
LOG(10) | 1 | Logarithm of 10. Because the second argument (base) is omitted, it is assumed to be 10. The result, 1, is the power to which the base must be raised to equal 10. |
LOG(8, 2) | 3 | Logarithm of 8 with base 2. The result, 3, is the power to which the base must be raised to equal 8. |
LOG(86, 2.7182818) | 4.4543473 | Logarithm of 86 with base e (approximately 2.718). The result, 4.454, is the power to which the base must be raised to equal 86. |
LOG10
description | Calculates the base-10 logarithm of a number. |
syntax | LOG10( Number ) |
returns | Number |
semantics | Returns the base-10 logarithm of number. |
constraints | Number must be greater than 0 |
Examples
expression | result | comment |
LOG10(10) | 1 |
MAX
description | Return the maximum from a set of numbers. |
syntax | MAX( { NumberSequenceList N } ) |
returns | Number |
semantics | Returns the value of the maximum number in the list passed in. Non-numbers are ignored. Note that if logical types are a distinct type, they are not included. What happens when MAX is provided 0 parameters is implementation-defined, but MAX with no parameters should return 0. |
constraints | None |
Examples
expression | result | comment |
MAX(2;4;1;-8) | 4 | Negative numbers are smaller than positive numbers. |
MAX([.B4:.B5]) | 3 | The maximum of (2,3) is 3. |
MIN
description | Return the minimum from a set of numbers. |
syntax | MIN( { NumberSequenceList N } ) |
returns | Number |
semantics | Returns the value of the minimum number in the list passed in. Returns zero if no numbers are provided in the list. What happens when MIN is provided 0 parameters is implementation-defined, but MIN() with no parameters should return 0. |
constraints | None. |
Examples
expression | result | comment |
MIN(2;4;1;-8) | -8 | Negative numbers are smaller than positive numbers. |
MIN([.B4:.B5]) | 2 | The minimum of (2,3) is 2. |
MIN([.B3]) | 0 | If no numbers are provided in all ranges, MIN returns 0 |
MIN("a") | Error | Non-numbers inline are NOT ignored. |
MIN([.B3:.B5]) | 2 | Cell text is not converted to numbers and is ignored. |
MOD
description | Calculates the remainder of a division. |
syntax | MOD( Number divisor, Number dividend ) |
returns | Integer |
semantics | Returns the remainder of divisor/dividend |
constraints | Dividend must not be 0 |
Examples
expression | result | comment |
MOD(10,3) | 1 | 3 goes into 10 three times with a remainder of 1 |
N
description | Returns the number of a value. |
syntax | N( String ) |
returns | Integer |
semantics | Parses the value as a number. If the value cannot be parsed as a number, returns 0. |
constraints | None |
Examples
expression | result | comment |
N("10") | 10 | Simple N |
N(TRUE()) | 1 | True evaluates to 1 |
N("ABC") | 0 | Non-numeric strings return 0 |
NPV
description | 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). |
syntax | NPV(rate,value1,[value2],…) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
NPV([FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5]) | $1,188.44 | Net present value of this investment |
OCT2DEC
description | Converts an octal number to decimal. |
syntax | OCT2DEC(number) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
OCT2DEC(7777777533) | -165 | Converts octal 7777777533 to decimal form. |
ODD
description | Rounds a number up to the nearest odd integer, where "up" means "away from 0". |
syntax | ODD( Number N ) |
returns | Number |
semantics | Returns the odd integer whose sign is the same as N’s and whose absolute value is greater than or equal to the absolute value of N. In other words, any "rounding" is away from zero. By definition, ODD(0) is 1. |
constraints | None |
Examples
expression | result | comment |
ODD(5) | 5 | Positive odd integers remain unchanged. |
ODD(-5) | -5 | Negative odd integers remain unchanged. |
ODD(2) | 3 | Non-odd positive integers round up. |
ODD(0.3) | 1 | Positive floating values round up. |
ODD(-2) | -3 | Non-odd negative integers round down. |
ODD(-0.3) | -1 | Negative floating values round down. |
ODD(0) | 1 | By definition, ODD(0) is 1. |
PI
description | Returns the value of PI. |
syntax | PI() |
returns | Number |
semantics | Returns the constant value of PI 3.14159… |
constraints | None |
Examples
expression | result | comment |
FIXED(PI();5) | 3.14159 | Constant |
POISSON
description | Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute. |
syntax | POISSON(x,mean,cumulative) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
POISSON([FIELD1],[FIELD2],TRUE) | 0.124652 | Cumulative Poisson probability with the terms above (0.124652) |
POISSON([FIELD1],[FIELD2],FALSE) | 0.084224 | Poisson probability mass function with the terms above (0.084224) |
POWER
description | Computes a number raised to the power by another number. |
syntax | POWER( Number number, Number power ) |
returns | Number |
semantics | Returns number ^ power |
constraints | None |
Examples
expression | result | comment |
POWER(2;3) | 8 | Simple POWER |
QUOTIENT
description | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
syntax | QUOTIENT(numerator, denominator) |
returns | Integer |
semantics | |
constraints |
Examples
expression | result | comment |
QUOTIENT(5, 2) | 2 | Integer portion of 5/2 |
QUOTIENT(4.5, 3.1) | 1 | Integer portion of 4.5/3.1 |
QUOTIENT(-10, 3) | -3 | Integer portion of -10/3 |
RADIANS
description | Converts degrees to radians. |
syntax | RADIANS(angle) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
RADIANS(270) | 4.712389 | 270 degrees as radians (4.712389 or 3π/2 radians) |
RAND
description | RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated. |
syntax | RAND() |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
RAND() | varies | A random number greater than or equal to 0 and less than 1 |
RANDBETWEEN
description | Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated. |
syntax | RANDBETWEEN(bottom, top) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
RANDBETWEEN(1,100) | varies | Random number between 1 and 100 (varies) |
RANDBETWEEN(-1,1) | varies | Random number between -1 and 1 (varies) |
ROUND
description | The ROUND function rounds a number to a specified number of digits. For example, if field [FIELD1] contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: |
syntax | ROUND(number, num_digits) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
ROUND(-1.475, 2) | -1.48 | Rounds -1.475 to two decimal places |
ROUND(626.3,-3) | 1000 | Rounds 626.3 to the nearest multiple of 1000 |
ROUND(-50.55,-2) | -100 | Rounds -50.55 to the nearest multiple of 100 |
ROUNDDOWN
description | Rounds a number down, toward zero. |
syntax | ROUNDDOWN(number, num_digits) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
ROUNDDOWN(3.14159, 3) | 3.141 | Rounds 3.14159 down to three decimal places. |
ROUNDDOWN(31415.92654, -2) | 31400 | Rounds 31415.92654 down to 2 decimal places to the left of the decimal point. |
ROUNDUP
description | Rounds a number up, away from 0 (zero). |
syntax | ROUNDUP(number, num_digits) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
ROUNDUP(3.2,0) | 4 | Rounds 3.2 up to zero decimal places. |
ROUNDUP(31415.92654, -2) | 31500 | Rounds 31415.92654 up to 2 decimal places to the left of the decimal point. |
SIGN
description | Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative. |
syntax | SIGN(number) |
returns | Integer |
semantics | |
constraints |
Examples
expression | result | comment |
SIGN(10) | 1 | Sign of a positive number. |
SIGN(4-4) | 0 | Sign of the result of 4 minus 4 (zero). |
SIGN(-0.00001) | -1 | Sign of a negative number. |
SIN
description | Returns the sine of a number. |
syntax | SIN( Number ) |
returns | Number |
semantics | Number is the angle in radians for which the sine is to be calculated. |
constraints | None |
Examples
expression | result | comment |
SIN(10) | -0.54402111 | Simple SIN |
SINH
description | Returns the hyperbolic sine of a number. |
syntax | SINH(number) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
2.868*SINH(0.0342*1.03) | 0.1010491 | Probability of obtaining a result of less than 1.03 seconds. |
SQRT
description | Returns the square root of a number. |
syntax | SQRT( Number ) |
returns | Number |
semantics | Returns the square root of number. |
constraints | Number must be positive. |
Examples
expression | result | comment |
SQRT(4) | 2 | Simple SQRT |
SUM
description | Sum (add) the set of numbers, including all numbers in ranges |
syntax | SUM( { NumberSequenceList N }+ ) |
returns | Number |
semantics | Adds numbers (and only numbers) together (see the text on conversions). Applications may allow SUM to receive 0 parameters (and return 0), but portable documents must not depend on SUM() with zero parameters returning 0. |
constraints | None |
Examples
expression | result | comment |
SUM(1;2;3) | 6 | Simple sum. |
SUM(TRUE();2;3) | 6 | TRUE() is 1. |
SUM([.B4:.B5]) | 5 | 2+3 is 5. |
SUMSQ
description | Returns the sum of the squares of the arguments. |
syntax | SUMSQ(number1, [number2], …) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
SUMSQ(3, 4) | 25 | Sum of the squares of 3 and 4 (25) |
SWITCH
description | The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. |
syntax | |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
SWITCH(WEEKDAY([DATE_FIELD]),1,"Sunday",2,"Monday",3,"Tuesday","No match") | Because [DATE_FIELD]=2, and Monday is the result argument corresponding to the value 2, SWITCH returns Monday | |
SWITCH([DATE_FIELD],1,"Sunday",2,"Monday",3,"Tuesday","No match") | Tuesday |
T.DIST
description | Returns the Student’s left-tailed t-distribution. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
syntax | T.DIST(x,deg_freedom, cumulative) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
T.DIST(60,1,TRUE) | 0.99469533 | Student’s left-tailed t-distribution for 60, returned as the cumulative distribution function, using 1 degree of freedom. |
T.DIST(8,3,FALSE) | 0.00073691 | Student’s left-tailed t-distribution for 8, returned as the probability density function, using 3 degrees of freedom. |
T.DIST.2T
description | Returns the two-tailed Student’s t-distribution. The Student’s t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
syntax | T.DIST.2T(x,deg_freedom) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
T.DIST.2T(1.959999998, 60) | 5.46% | Two-tailed distribution (0.054645, or 5.46 percent) |
T.DIST.RT
description | Returns the right-tailed Student’s t-distribution. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
syntax | T.DIST.RT(x,deg_freedom) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
T.DIST.RT(1.959999998,60) | 0.027322 | Two-tailed distribution (0.027322, or 2.73 percent) |
TAN
description | Returns the tangent of the given angle. |
syntax | TAN(number) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
TAN(0.785) | 0.99920 | Tangent of 0.785 radians (0.99920) |
TAN(45*PI()/180) | 1 | Tangent of 45 degrees (1) |
TAN(RADIANS(45)) | 1 | Tangent of 45 degrees (1) |
TANH
description | Returns the hyperbolic tangent of a number. |
syntax | TANH(number) |
returns | |
semantics | |
constraints |
Examples
expression | result | comment |
TANH(-2) | -0.964028 | Hyperbolic tangent of -2 (-0.96403) |
TANH(0) | 0 | Hyperbolic tangent of 0 (0) |
TANH(0.5) | 0.462117 | Hyperbolic tangent of 0.5 (0.462117) |
TDIST
description | Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
syntax | TDIST(x,deg_freedom,tails) |
returns | Number |
semantics | |
constraints |
Examples
expression | result | comment |
TDIST(1.959999998,60,2) | 5.46% | Two-tailed distribution (0.054644930, or 5.46 percent) |
TDIST(1.959999998,60,1) | 2.73% | One-tailed distribution (0.027322465 or 2.73 percent) |
TRUNC
description | Truncates a number to an integer by removing the fractional part of the number. |
syntax | TRUNC(number, [num_digits]) |
returns | Integer |
semantics | |
constraints |
Examples
expression | result | comment |
TRUNC(8.9) | 8 | Truncates 8.9 to return the integer part (8). |
TRUNC(-8.9) | -8 | Truncates a negative number to return the integer part (-8). |
TRUNC(0.45) | 0 | Truncates a number between 0 and 1, returning the integer part (0). |
Text
&
description | Concatenate two strings. |
syntax | Text Left & Text Right |
returns | Text |
semantics | Concatenates two text (string) values. Due to the way conversion works, numbers are converted to strings. Note that this is equivalent to CONCATENATE(Left,Right). (Note: CONCATENATE is not yet available in libformula version 0.1.18.2) |
constraints | None |
Examples
expression | result | comment |
"Hi " & "there" | "Hi there" | Simple concatenation. |
"H" & "" | "H" | Concatenating an empty string produces no change. |
-5&"b" | “-5b” | Unary “-” has higher precedence than “&” |
3&2-1 | “31” | Binary “-” has higher precedence than “&” |
CHAR
description | Converts a code number into a ASCII character or letter. Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
syntax | CHAR( Integer ) |
returns | Text |
semantics | Returns the text representation of an ASCII decimal code |
constraints | Integer between 0 and 255 inclusive |
Examples
expression | result | comment |
CHAR(100) | d | Ascii character decimal 100 is a lower case d. |
CHAR(65) | A | Displays the character represented by 65 in the computer’s character set. |
CHAR(33) | ! | Displays the character represented by 33 in the computer’s character set. |
CLEAN
description | Removes all nonprintable 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. |
syntax | CLEAN( Text ) |
returns | Text |
semantics | Removes non-printable characters such as ASCII 0 through 31 from a string |
constraints | None |
CODE
description | Returns the numeric code for the first character character in a text string. The returned code corresponds to the character set used by your computer. |
syntax | CODE( Text ) |
returns | Text |
semantics | Returns the Unicode decimal code |
constraints | None |
Examples
expression | result | comment |
CODE("d") | 100 | Unicode character d is decimal 100. |
CONCATENATE
description | The CONCAT function combines the text from multiple ranges and/or strings, but it doesn’t provide delimiter or IgnoreEmpty arguments. |
syntax | CONCAT( Text t1 ; Text t2; Text tN ) |
returns | Text |
semantics | Returns the text strings concatenated together |
constraints | Requires one or more parameters |
Examples
expression | result | comment |
CONCATENATE("A";"B") | AB | 2 parameter concatenation. |
CONCATENATE("AB";"CD";"EF") | ABCDEF | 3 parameter concatenation |
DOLLAR
description | Convert the parameters to Text formatted as currency. |
syntax | DOLLAR( Text text, Integer decimalPlaces ) |
returns | Text |
semantics | Text is the incoming string or number to format, decimalPlaces is the number of decimal places to use. If i2 is not provided defaults to 2. |
constraints | None |
Examples
expression | result | comment |
DOLLAR("190") | $190.00 | Decimal places not specified |
DOLLAR("190.89";1) | $190.9 | Rounds to 1 decimal place |
EXACT
description | Report if two text values are exactly equal using a case-sensitive comparison |
syntax | EXACT( Text t1 ; Text t2 ) |
returns | Logical |
semantics | Converts both sides to text, and then returns TRUE if the two text values are "exactly" equal, including case, otherwise it returns FALSE. |
constraints | None |
Examples
expression | result | comment |
EXACT("A";"A") | True | Trivial comparison. |
EXACT("A";"a") | False | EXACT, unlike "=", considers different cases different. |
EXACT(1;1) | True | EXACT does work with numbers. |
EXACT((1/3)*3;1) | True | Numerical comparisons ignore "trivial" differences that depend only on numeric precision of finite numbers. |
EXACT(TRUE();TRUE()) | True | Works with Logical values. |
EXACT("1";2) | False | Different types with different values are different. |
EXACT("h";1) | False | If text and number, and text can’t be converted to a number, they are different and NOT an error. |
EXACT("1";1) | True | If text and number, see if number converted to text is equal. |
EXACT(“ 1”;1) | False | This converts 1 into the Text value “1”, the compares and finds that it’s not the same as “ 1” (note the leading space). |
FIND
description | Return the starting position of a given text. |
syntax | FIND( Text Search ; Text T [ ; Integer Start = 1 ] ) |
returns | Number |
semantics | |
constraints | Start >= 1 |
Examples
expression | result | comment |
FIND("b";"abcabc") | 2 | Simple FIND() |
FIND("b";"abcabcabc"; 3) | 5 | Start changes the start of the search |
FIND("b";"ABC";1) | Error | Matching is case-sensitive. |
FIND("b";"bbbb") | 1 | Simple FIND(), default is 1 |
FIND("b";"bbbb";2) | 2 | |
FIND("b";"bbbb";2.9) | 2 | INT(Start) used as starting position |
FIND("b";"bbbb";0) | Error | Start >= 0 |
FIND("b";"bbbb";0.9) | Error |
FIXED
description | Round the number to a specified number of decimals and format the result as a text. |
syntax | FIXED( Text text, Integer decimalPlaces, Boolean omitGroupChar ) |
returns | Text |
semantics | Text is the incoming string or number to format, decimalPlaces is the number of decimal places to use. If decimalPlaces is not provided defaults to 3. omitGroupChar indicates if the grouping character (for example ",") should be omitted from the result. Defaults to false. |
constraints | None |
Examples
expression | result | comment |
FIXED([NUMBER_FIELD]], 1) | 1,234.6 | Rounds the number in [NUMBER_FIELD] one digit to the right of the decimal point. |
FIXED([NUMBER_FIELD]], -1, TRUE) | -1230 | Rounds the number in [NUMBER_FIELD] one digit to the left of the decimal point, without commas (the TRUE argument). |
FIXED("190") | 190.000 | Decimal places not specified |
FIXED("190.89";1) | 190.9 | Rounds to 1 decimal place |
FIXED("1190.89";1;TRUE()) | 1190.9 | No grouping character |
FIXED("1190.89";1;FALSE()) | 1,190.9 | With grouping character |
LEFT
description | Return a selected number of text characters from the left. |
syntax | LEFT( Text T [ ; Integer Length ] ) |
returns | Text |
semantics | Returns the INT(Length) number of characters of text T, starting from the left. If Length is omitted, it defaults to 1; otherwise, it computes Length=INT(Length). If T has fewer than Length characters, it returns T. This means that if T is an empty string (which has length 0) or the parameter Length is 0, LEFT() will always return an empty string. Note that if Length<0, an Error is returned. This function must return the same string as MID(T; 1; Length). |
constraints | Length >= 0 |
Examples
expression | result | comment |
LEFT("Hello";2) | "He" | Simple LEFT(). |
LEFT("Hello";2.9) | "He" | INT(), not round to nearest or round towards positive infinity, must be used to convert length into an integer. |
LEFT("Hello") | "H" | Length defaults to 1. |
LEFT("Hello";20) | "Hello" | If Length is longer than T, returns T. |
LEFT("Hello";0) | "" | If Length 0, returns empty string. |
LEFT("";4) | "" | Given an empty string, always returns empty string. |
LEFT("xxx";-0.1) | Error | It makes no sense to request a negative number of characters. Also, this tests to ensure that INT() is used to convert non-integers to integers; if -0.1 were incorrectly rounded to 0 (as it would be by round-to-nearest or round-toward-zero), this would incorrectly return a null string. |
LEFT("Hello";2^15-1) | "Hello" | If Length > LEN(T) entire string is returned. |
LEN
description | Return the length, in characters, of given text |
syntax | LEN( Text T ) |
returns | Integer |
semantics | Computes number of characters (not the number of bytes) in T. Implementations that support ISO 10646 / Unicode shall consider any character in the Basic Multilingual Plane (BMP) basic plane as one character, even if they occupy multiple bytes. (The BMP are the characters numbered 0 through 65535 inclusive). Implementations should consider any character not in the BMP as one character as well. |
constraints | None. |
Examples
expression | result | comment |
LEN("Hi There") | 8 | Space is a character. |
LEN("") | 0 | Empty string has zero characters. |
LEN(55) | 2 | Numbers are automatically converted. |
LOWER
description | Return input string, but with all uppercase letters converted to lowercase letters. |
syntax | LOWER( Text T ) |
returns | Text |
semantics | Return input string, but with all uppercase letters converted to lowercase letters. As with most functions, it is side-effect free (it does not modify the source values). All implementations shall convert A-Z to a-z. However, as this function can be locale aware, results may be unexpected in certain cases. For example in a Turkish locale an upper case "I without dot" U+0049 is converted to a lower case "i without dot" U+0131. |
constraints | None |
Examples
expression | result | comment |
LOWER("HELLObc7") | "hellobc7" | Uppercase converted to lowercase; other characters just copied to result. |
MID
description | MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |
syntax | MID(text, start_num, num_chars) |
returns | String |
semantics | |
constraints |
Examples
expression | result | comment |
MID([FIELD],1,5) | Returns 5 characters from the string in [FIELD], starting at the 1st character. | |
MID([FIELD],7,20) | Returns 20 characters from the string in [FIELD], starting at the 7th character. Because the number of characters to return (20) is greater than the length of the string (10), all characters, beginning with the 7th, are returned. No empty characters (spaces) are added to the end. | |
MID("123456789";5;3) | "567" | Simple use of MID. |
MID("123456789";20;3) | "" | If Start is beyond string, return empty string. |
MID("123456789";-1;0) | Error | Start cannot be less than one; even if the length is 0 |
MID("123456789";1;0) | "" | But otherwise, length=0 produces the empty string |
MID("123456789";2.9;1) | "2" | INT(Start) is used |
MID("123456789";2;2.9) | "23" | INT(Length) is used |
MID
description | Returns extracted text, given an original text, starting position, and length. |
syntax | MID( Text T ; Integer Start ; Integer Length ) |
returns | Text |
semantics | Returns the characters from T, starting at character position Start, for up to Length characters. For the integer conversions, Start=INT(Start), and Length=INT(Length). If there are less than Length characters starting at start, it returns as many characters as it can beginning with Start. In particular, if Start > LEN(T), it returns the empty string (""). If Start < 0, it returns an Error. If Start >=0, and Length=0, it returns the empty string. Note that MID(T;1;Length) produces the same results as LEFT(T;Length). |
constraints | Start >= 1, Length >= 0. |
Examples
expression | result | comment |
MID([FIELD],1,5) | Returns 5 characters from the string in [FIELD], starting at the 1st character. | |
MID([FIELD],7,20) | Returns 20 characters from the string in [FIELD], starting at the 7th character. Because the number of characters to return (20) is greater than the length of the string (10), all characters, beginning with the 7th, are returned. No empty characters (spaces) are added to the end. | |
MID("123456789";5;3) | "567" | Simple use of MID. |
MID("123456789";20;3) | "" | If Start is beyond string, return empty string. |
MID("123456789";-1;0) | Error | Start cannot be less than one; even if the length is 0 |
MID("123456789";1;0) | "" | But otherwise, length=0 produces the empty string |
MID("123456789";2.9;1) | "2" | INT(Start) is used |
MID("123456789";2;2.9) | "23" | INT(Length) is used |
PROPER
description | Return the input string with the first letter of each word converted to an uppercase letter. |
syntax | PROPER(Text T) |
returns | Text |
semantics | Return input string with the first letter of each word as an uppercase letter. As with most functions, it is side-effect free (it does not modify the source values). |
constraints | None |
Examples
expression | result | comment |
PROPER("hello there") | Hello There | The first letter is uppercase and the following letter are lowercase. |
REPLACE
description | Returns text where an old text is substituted with a new text. |
syntax | REPLACE( Text T ; Number Start ; Number Len ; Text New ) |
returns | Text |
semantics | Returns text T, but remove the characters starting at character position Start for Len characters, and instead replace them with New. Character positions defined by Start begin at 1 (for the leftmost character). If Len=0, the text New is inserted before character position Start, and all the text before and after Start is retained. |
constraints | Start >= 1. |
Examples
expression | result | comment |
REPLACE("123456789";5;3;"Q") | "1234Q89" | Replacement text may have different length. |
REPLACE("123456789";5;0;"Q") | "1234Q56789" | If Len=0, 0 characters removed. |
REPT
description | Return text repeated Count times. |
syntax | REPT( Text T ; Integer Count ) |
returns | Text |
semantics | Returns text T repeated Count number of times; if Count is zero, an empty string is returned. If Count < 0, the result is Error. |
constraints | Count >= 0 |
Examples
expression | result | comment |
REPT("X";3) | "XXX" | |
REPT("XY";2) | "XYXY" | Repeated text can have length > 1. |
REPT("X";2.9) | "XX" | INT(Count) used if count is a fraction |
REPT("X";0) | "" | If Count is zero, empty string |
REPT("X";-1) | Error | If Count is negative, Error. |
RIGHT
description | Return a selected number of text characters from the right. |
syntax | RIGHT( Text T [ ; Integer Length ] ) |
returns | Text |
semantics | Returns the Length number of characters of text T, starting from the right. If Length is omitted, it defaults to 1; otherwise, it computes Length=INT(Length). If T has fewer than Length characters, it returns T (unchanged). This means that if T is an empty string (which has length 0) or the parameter Length is 0, RIGHT() will always return an empty string. Note that if Length<0, an Error is returned. |
constraints | Length >= 0 |
Examples
expression | result | comment |
RIGHT("Hello";2) | "lo" | Simple RIGHT(). |
RIGHT("Hello") | "o" | Length defaults to 1. |
RIGHT("Hello";20) | "Hello" | If Length is longer than T, returns T. |
RIGHT("Hello";0) | "" | If Length 0, returns empty string. |
RIGHT("Hello";2^15-1) | “Hello” | If Length is larger than T and is very large, it still returns the original short string. |
RIGHT("";4) | "" | Given an empty string, always returns empty string. |
RIGHT("Hello";-1) | Error | It makes no sense to request a negative number of characters. |
RIGHT("Hello";-0.1) | Error | Must use INT, not round-to-nearest or round-towards zero, to convert Length to Integer |
ROMAN
description | Converts an arabic numeral to roman, as text. |
syntax | ROMAN(number, [form]) |
returns | Text |
semantics | |
constraints |
Examples
expression | result | comment |
ROMAN(499,0) | CDXCIX | Classic roman numeral style for 499 (CDXCIX) |
ROMAN(499,1) | LDVLIV | More concise version for 499 (LDVLI |
ROMAN(499,2) | XDIX | More concise version for 499 (XDIX) |
ROMAN(499,3) | VDIV | More concise version for 499 (VDIV) |
ROMAN(499,4) | ID | Simplified version for 499 (ID) |
SEARCH
description | Looks for a string of text within another (NOT case sensitive) |
syntax | SEARCH( Text findText, Text text, Integer startPosition ) |
returns | Text |
semantics | findText is the text to search for, text is the String to search in, startPosition is the integer position within the string to start searching |
constraints | startPosition > 0 |
Examples
expression | result | comment |
SEARCH("p";"Apple") | 2 | |
SEARCH("p";"Apple";3) | 3 | Starts searching at index 3 |
STRINGCOUNT
description | Counts the occurrences of text in a string. |
syntax | SEARCH( Text text, Text findText ) |
returns | Text |
semantics | text is the String to search in, findText is the text to search for. |
constraints | None |
Examples
expression | result | comment |
STRINGCOUNT("Apple";"p") | 2 | |
STRINGCOUNT("APPLE";"p") | 0 | Function is case sensitive |
SUBSTITUTE
description | Returns text where an old text is substituted with a new text. |
syntax | SUBSTITUTE( Text T ; Text Old ; Text New [ ; Number Which ] ) |
returns | Text |
semantics | Returns text T, but with text Old replaced by text New (when searching from the left). If Which is omitted, every occurrence of Old is replaced with New; if Which is provided, only that occurrence of Old is replaced by New (starting the count from 1). If there is no match, or if Old has length 0, the value of T is returned. Note that Old and New may have different lengths. If Which is present and Which < 1, returns Error. |
constraints | Which >= 1 (when provided) |
Examples
expression | result | comment |
SUBSTITUTE("121212";"2";"ab") | "1ab1ab1ab" | Without Which, all replaced. |
SUBSTITUTE("121212";"2";"ab";2) | "121ab12" | Which starts counting from 1. |
SUBSTITUTE("Hello";"x";"ab") | "Hello" | If not found, returns unchanged. |
SUBSTITUTE("xyz";"";"ab") | "xyz" | Returns T if Old is Length 0. |
SUBSTITUTE("";"";"ab") | "" | Returns T if Old is Length 0, even if T is empty (it does not consider an empty T to “match” an empty Old). |
SUBSTITUTE("Hello"; "H"; "J"; 0) | Error | Which cannot be less than 1. |
T
description | Return the text (if text), else return 0-length Text value |
syntax | T( Any X ) |
returns | Text |
semantics | The type of (a dereferenced) X is examined; if it is of type Text, it is returned, else an empty string (Text value of zero length) is returned. This is not a type-conversion function; T(5) produces an empty string, not "5". |
constraints | None |
Examples
expression | result | comment |
T("HI") | "HI" | T does not change text. |
T([.B3]) | "7" | References transformed into what they reference. |
T(5) | "" | Non-text converted into null string. |
TEXT
description | Return the value converted to a text. |
syntax | TEXT( Scalar X ; Text FormatCode ) |
returns | Text |
semantics | Converts the value X to a text according to the rules of a number format code passed as FormatCode and returns it. |
constraints | The FormatCode is a sequence of characters with an application-defined meaning. Portable Contraints: The result of this function may change across locales. If separators such as decimal or group separator are involved, conversion may give unexpected results if the separators don’t match that of the current locale. Across applications the result may change to the extend to which number format codes and their subtleties are supported. Portable documents should not use this function. |
Examples
expression | result | comment |
TEXT(12345.6789;",#0.00") | "12,345.68" | Non-text converted to text. This is locale-specific. |
TEXT(3;"0"" good things""") | "3 good things" |
TRIM
description | Remove leading and trailing spaces, and replace all internal multiple spaces with a single space. |
syntax | TRIM( Text T ) |
returns | Text |
semantics | Takes T and removes all leading and trailing space. Any other sequence of 2 or more spaces is replaced with a single space. |
constraints | None |
Examples
expression | result | comment |
TRIM(" ABC ") | "ABC" |
UNICHAR
description | Converts a code number into a Unicode character or letter. |
syntax | UNICHAR( Integer ) |
returns | Text |
semantics | Returns the text representation of an Unicode decimal code |
constraints | Integer must be a valid Unicode character code |
Examples
expression | result | comment |
UNICHAR(100) | d | Unicode character decimal 100 is a lower case d. |
UNICODE
description | Returns the numeric Unicode code for the first character character in a text string. |
syntax | UNICODE( Text ) |
returns | Text |
semantics | Returns the Unicode decimal code |
constraints | None |
Examples
expression | result | comment |
UNICODE("d") | 100 | Unicode character d is decimal 100. |
UPPER
description | Return input string, but with all lowercase letters converted to uppercase letters. |
syntax | UPPER( Text T ) |
returns | Text |
semantics | Return input string, but with all lowercase letters converted to uppercase letters. As with most functions, it is side-effect free (it does not modify the source values). All implementations shall convert a-z to A-Z. However, as this function can be locale aware, results may be unexpected in certain cases, for example in a Turkish locale a lower case "i with dot" U+0069 is converted to an upper case "I with dot" U+0130. |
constraints | None |
Examples
expression | result | comment |
UPPER("Habc7") | "HABC7" | Lowercase converted to upper case; other characters just copied to result. |
URLENCODE
description | Applies URL-Encoding to a text given in the first parameter using the encoding specified in the second parameter. If no encoding is given, UTF-8 is assumed. |
syntax | URLENCODE( Text text, Text encoding ) |
returns | Text |
semantics | text is the string to encode, encoding is the character set to use when encoding. |
constraints | Encoding must be a valid character set. |
Examples
expression | result | comment |
URLENCODE("&PARAM1=value?param2=value2") | %26PARAM1%3Dvalue%3Fparam2%3Dvalue2 |