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/evaldevguide.html#What+functions+are+supported
The formula will not evaluate a field unless the field preexists. 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], "yyyyMMdd")
DateTime Examples:

In Excel, dates and times are stored as floatingpoint 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: CONCATENATE([field1], “${Param1}”). * 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 precreate 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 19000229). 
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  Nonleap 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 (YYYYMMDD), which is localeindependent. 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. YYYYMMDD 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:nullyear and table:nulldate affect this function. 
constraints  None 
Examples
expression  result  comment 
DATEVALUE("20041225")=DATE(2004;12;25)  True  DATEVALUE 
DATEVALUE("20041225 12:34:56")=DATE(2004;12;25)  True  Only the integer part is returned 
DATEVALUE("20041225 12:34:56")  20041225  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("20061215")  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("20041223");DATEVALUE("20041225"))  2  DAYS 
DAYS(DATEVALUE("20041225");DATEVALUE("20041223"))  2  DAYS 
DAYS360
description  The DAYS360 function returns the number of days between two dates based on a 360day year (twelve 30day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30day 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("20061215")  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)  15Feb22  The date, one month after the date above 
EDATE([DATE_FIELD],1)  15Dec22  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 24hour clock. This is equal to: DayFraction=(TINT(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/241/(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 (059) 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("20041223 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("20041223"))  20041231  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 nonworking 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)  20190507  If this were run on 20190508. 
SECOND
description  Determines the sequential number for the second of the minute (059) 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("20041223 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 localeindependent. 
constraints  None 
Examples
expression  result  comment 
TIMEVALUE("12:56:45")  12:56:45  TIMEVALUE 
TIMEVALUE("20041225 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  Yearmonthdate 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 dateformatted string in the current locale’s format and returns the year portion. If a year is given as a twodigit number, as in "052115", 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:nullyear. Applications shall support extracting the year from a date beginning in 1900. Threedigit year numbers precede adoption of the Gregorian calendar, and may return either an error or the year number. Fourdigit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an error or the year number. Fourdigit 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 nonexistent 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  Nonblank 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 outofrange 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 implementationdefined 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 implementationdefined 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 
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 shortcircuits. 
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 nonzero is considered true. 
IF(5,7,8)  7  A nonzero 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 
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 ycoordinates. The arctangent is the angle from the xaxis 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 twoperson 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  Noneven positive integers round up. 
EVEN(0.3)  2  Positive floating values round up. 
EVEN(1)  2  Noneven 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*(n2)*(n4)…(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*(n2)*(n4)…(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("0j")  1  Imaginary coefficient of the complex number 0j 
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("69i")  6  Real coefficient of 69i 
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 base10 logarithm of a number. 
syntax  LOG10( Number ) 
returns  Number 
semantics  Returns the base10 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. Nonnumbers are ignored. Note that if logical types are a distinct type, they are not included. What happens when MAX is provided 0 parameters is implementationdefined, 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 implementationdefined, 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  Nonnumbers 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  Nonnumeric 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  Nonodd positive integers round up. 
ODD(0.3)  1  Positive floating values round up. 
ODD(2)  3  Nonodd 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(44)  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 lefttailed tdistribution. The tdistribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the tdistribution. 
syntax  T.DIST(x,deg_freedom, cumulative) 
returns  Number 
semantics  
constraints 
Examples
expression  result  comment 
T.DIST(60,1,TRUE)  0.99469533  Student’s lefttailed tdistribution for 60, returned as the cumulative distribution function, using 1 degree of freedom. 
T.DIST(8,3,FALSE)  0.00073691  Student’s lefttailed tdistribution for 8, returned as the probability density function, using 3 degrees of freedom. 
T.DIST.2T
description  Returns the twotailed Student’s tdistribution. The Student’s tdistribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the tdistribution. 
syntax  T.DIST.2T(x,deg_freedom) 
returns  Number 
semantics  
constraints 
Examples
expression  result  comment 
T.DIST.2T(1.959999998, 60)  5.46%  Twotailed distribution (0.054645, or 5.46 percent) 
T.DIST.RT
description  Returns the righttailed Student’s tdistribution. The tdistribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the tdistribution. 
syntax  T.DIST.RT(x,deg_freedom) 
returns  Number 
semantics  
constraints 
Examples
expression  result  comment 
T.DIST.RT(1.959999998,60)  0.027322  Twotailed 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 tdistribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The tdistribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the tdistribution. 
syntax  TDIST(x,deg_freedom,tails) 
returns  Number 
semantics  
constraints 
Examples
expression  result  comment 
TDIST(1.959999998,60,2)  5.46%  Twotailed distribution (0.054644930, or 5.46 percent) 
TDIST(1.959999998,60,1)  2.73%  Onetailed 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&21  “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 lowlevel computer code that is frequently at the beginning and end of data files and cannot be printed. 
syntax  CLEAN( Text ) 
returns  Text 
semantics  Removes nonprintable 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 casesensitive 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 casesensitive. 
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 nonintegers to integers; if 0.1 were incorrectly rounded to 0 (as it would be by roundtonearest or roundtowardzero), this would incorrectly return a null string. 
LEFT("Hello";2^151)  "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 sideeffect free (it does not modify the source values). All implementations shall convert AZ to az. 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 sideeffect 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^151)  “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 roundtonearest or roundtowards 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 0length 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 typeconversion 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)  ""  Nontext 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 applicationdefined 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"  Nontext converted to text. This is localespecific. 
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 sideeffect free (it does not modify the source values). All implementations shall convert az to AZ. 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 URLEncoding to a text given in the first parameter using the encoding specified in the second parameter. If no encoding is given, UTF8 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 