# Formula field

#### Who can use this feature?

👤  Available on all plans.

Formulas let you transform and combine existing fields into a new Formula field. For example, you can:

• Concatenate `First name` and `Last name` fields into a `Full name` Formula field.
• Multiply `Price` and `Units` field to get the result in the `Revenue` Formula field.

The field will show only in your Stacker app, and won't show up in your Airtable base or Google Sheets.

## Create a formula field

1. Go to  Manage Fields and data
2. Select the table and select Fields
4. Give your field a name
5. Select the field type: Formula
6. Type in your formula and click Save

## Fields in formulas

To use a field in a formula, the field needs to be in the table where you're creating the formula field. It also needs to be one of the following data types:

• Text
• Long Text
• Number
• Checkbox
• URL
• Single Select Dropdown
• Percentage
• Currency
• Rich Text
• Date
• Date and Time
• Multiple Select Dropdown

To use a field in a formula, you will need to wrap it in curly brackets. For example, {Price}. If you type in the opening curly bracket, we will suggest all available fields. ## List of formulas

Functions work with any valid field data types, while operators work only on number fields. If you use an operator for a non-number field, we will try to convert it into a number. If we can't, you'll see an error message.

#### SUM

Returns the sum of two or more numbers.

Example:

`SUM({Sold},{Not sold})`

or

`{Sold} + {Not sold}`

#### SUBTRACT

Returns the difference of two numbers.

Example:

`{Total stock} - {Sold stock}`

#### MULTIPLY

Returns the product of two numbers.

Example:

`{Sold} * {Price}`

#### DIVIDE

Returns one number divided by another.

Example:

`{Total sold} / {Price}`

#### AVERAGE

Returns the average of the values in two or more fields.

Example:

`AVERAGE({Sold},{Not sold})`

#### NOT EQUAL TO

Check if one value is not equal to another.

Example:

`1!=2 =>True` (represented as a checked checkbox)

#### EQUAL TO

Compare if one value is equal to another value.

Example:

`1=1 =>True` (represented as an empty checkbox)

#### GREATER THAN

Compare if one value is greater than another value.

Example:

`1>5 =>False` (represented as an empty checkbox)

#### GREATER THAN OR EQUAL TO

Compare if one value is greater than or equal to another value.

Example:

`2>=2 =>True` (represented as a checked checkbox)

#### LESS THAN

Compare if one value is less than another value.

Example:

`5<1 =>False` (represented as an empty checkbox)

#### LESS THAN OR EQUAL TO

Compare if one value is less than another value.

Example:

`2<=2 =>True` (represented as a checked checkbox)

#### CONCAT( , ) or &

Concatenate two values.

Example:

`CONCAT("Hello ", {Fullname}, " !")`

or

`"Hello " & {Fullname} & " !"`

#### IF (statement, Action A, Action B)

Check whether the statement is true. If it is true, then do action A, if it is false do action B.

Example:

`IF({UserId}, "https://dashboard.com/" + {UserId}, "")`

#### AND( )

Returns true if all the arguments are true, returns false otherwise.

Example:

`AND({Field 1}),{Field 2})`

or

`{Field 1} AND {Field 2}`

#### OR( )

Returns true id any one of the argument is true.

Example:

`OR({Field 1}),{Field 2})`

or

`{Field 1} OR {Field 2}`

#### NOT( )

Reverse a true to false.

Example:

`NOT(a<b)` Returns the same as `a>=b`

#### LEN

Returns the length of a string.

Example:

`LEN("Hello World") =>11`

#### REGEX_MATCH( )

Returns whether the input text matches a regular expression.

Example:

`REGEX_MATCH("Good Morning","Good.Morning" =>True` (Represented as a checked checkbox)

#### REGEX_REPLACE( )

Substitutes all matching substrings with a replacement string value.

Example:

`REGEX_REPLACE("Good Morning","M*",""=>"Good"`

#### REGEX_EXTRACT( )

Returns the first substring that matches a regular expression.

Example:

`REGEX_EXTRACT("Good Morning","M*",""=>"Morning"`

#### TRIM( )

Removes the whitespace at the beginning and end of string.

Example:

`TRIM(" Hello ") =>"Hello"`

#### UPPER( )

Makes string uppercase.

Example:

`UPPER("Hello") =>"HELLO"`

#### LOWER( )

Makes string lowercase.

Example:

`LOWER("Hello") =>"hello"`

#### RIGHT( )

Extract how many characters from the end of the string. Accepts string for first arg, number for second.

Example:

`RIGHT("Hello",2) =>"lo"`

#### IS_SAME( )

Compares two dates up to a unit and determines whether they are identical. Returns true or false.

Example:

`IS_SAME({date 1},{date 2}, 'unit')`

current acceptable units are:

`'exact'`(matches all units)

`'year'`, `'month'`, `'day'`

#### IS_BEFORE( )

Determines if [date1] is earlier than [date2]. Returns 1 is yes, 0 if no.

Example:

`IS_BEFORE({date 1},{date 2})`

#### IS_AFTER( )

Determines if [date1] is later than [date2]. Returns 1 is yes, 0 if no.

Example:

`IS_AFTER({date 1},{date 2})`

#### YEAR( )

Returns the four-digit year of datetime.

Example:

`YEAR({Date},[timezone])`

#### MONTH( )

Returns the month of a datetime as a number between 1 (January) and 12 (Decemeber).

Example:

`MONTH({Date},[timezone])`

#### DAY( )

Returns the day of a month of a datetimein the form of a number between 1-31.

Example:

`DAY({Date},[timezone])`

#### HOUR( )

Returns the hour of a datetime as a number between 0 (12.00am) and 23 (11.00pm).

Example:

`HOUR({Date},[timezone])`

#### MINUTE( )

Returns the minute if datetime as a number.

Example:

`MINUTE({Date},[timezone])`

#### LEFT( )

Extract how many characters from the beginning of the string. Accepts the string for first arg, number for second.

Example:

`LEFT("Hello",2) =>"He"`

#### DATEDIF( )

Returns the difference between datetimes in the unit specified.

Unit accepts:

`'year'`,`'month'`,`'week'`,`'day'`,`'hour'`,`'minute'`

Example:

`DATEDIF([date1],[date2],"units")`

#### MIN( )

Returns the item with the lowest value.

Example:

`MIN(number 1, number 2, number 3...)`

#### MAX( )

Returns the largest item between two or more parameters.

Example:

`MAX(number 1, number 2, number 3...)`

#### ABS(number)

Returns the absolute value.

Example:

`ABS(-5)`

Adds a time/date interval to a date and then returns the date.

Example:

`DATEADD([date],[#],"units")` (accepts years, months, weeks and days as well as singular counter-parts)

#### TODAY(timezone)

Returns the current date, but not the current time.

#### NOW( )

Returns the current date and time.

#### ROUND( )

Rounds to a number of decimal places as specified by precision e.g "0", "1", etc.

Example:

`ROUND({Unit Price},0)`

#### ROUNDUP( )

Rounds the value to the number of decimal places given by "precision" always rounding up. e.g "0", "1", etc.

Example:

`ROUNDUP({Unit Price},0)`

#### VALUE(string)

Coverts text string to a number.

Example:

`VALUE({Quoted Price})`

#### INT( )

Returns the greatest integer that is less than or equal to the number.

Example:

`INT({Unit Price})`

## Error messages

If a formula is invalid, you will see an error message that might help you understand what went wrong.

 Error Meaning `Missing formula function` or `Unexpected formula function` Means that you were very creative, but we don't have the formula function yet. `Function only works with field_type but field is a another_field_type` Shows when using a field with an incompatible field type. ```In an IF function, field_name and field_name_2 must be of similar types``` Shows in an IF function when the Action A and Action B are different types. `Missing keys in formula 'IF' function: else` Means that there is a missing else (Action B) in the IF function.