Formula transform Icon Formula

Description

The Formula transform allows you to apply Excel-like formulas and functions on fields in a pipeline.

With a formula you are not limited to comparing to three fields like in the Calculator. This is where you can set and compare Date and Times as well.

You have the option of creating a new field (use New field column) or setting a field value (use Replace value column).

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.

Formula Examples using the TEXT function:

  • Int to Text: "size=" & TEXT([RowLimitInteger],"0").

  • DateTime to Text: [StartDateField] & "=" & TEXT([StartDateTime], "yyyy-mm-dd")

  • String to Text: "new_counter=" & TEXT([counter]+1, "0")

Supported Engines

Hop Engine

Supported

Spark

Maybe Supported

Flink

Maybe Supported

Dataflow

Maybe Supported

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 needed, you can add a row generator with a single row as the first transform in your pipeline.

Formula

the formula that will be used to create the new field. Click on the field to open the Formula Editor (see the Formula Editor section for more details). 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 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

)

description

Closing bracket to influence calculation order in an expression

syntax

returns

semantics

constraints

*

description

Multiply 2 numeric values

syntax

returns

semantics

constraints

+

description

Sum of 2 numeric values

syntax

returns

semantics

constraints

-

description

Subtract 2 numeric values

syntax

returns

semantics

constraints

/

description

Divide 2 numeric values

syntax

returns

semantics

constraints

^

description

Calculates a number to the nth power, for example 2^6 resolves to 64

syntax

returns

semantics

constraints

Comparisons

<

description

See if one value is less than the other

syntax

returns

semantics

constraints

description

See if one value is less than or equal to the other

syntax

returns

semantics

constraints

<>

description

See if 2 values are different

syntax

returns

semantics

constraints

=

description

Verify that 2 values are equal

syntax

returns

semantics

constraints

>

description

See if one value is larger than the other

syntax

returns

semantics

constraints

>=

description

See if one value is larger than or equal to the other

syntax

returns

semantics

constraints

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.

VALUE

description

Returns the number value of a String.

syntax

VALUE( Text number )

returns

Decimal

semantics

Converts a String to a number

constraints

The number parameter must be parseable as a number

Examples

expression

result

comment

VALUE("123")

123

Simple value.

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 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

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

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).

VAR

description

Calculates the variance based on a sample.

syntax

VAR( { NumberSequence N }+ )

returns

Number

semantics

Returns the variance.

constraints

None.

Examples

expression

result

comment

VAR(10;5;1)

20.333333

Simple VAR

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)

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