# 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 > [Table name] > Fields
2. Click Add field > Formula
4. Click Save

Press space in the formula field box to see a list of all available fields and functions.

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

 Type Name Description Syntax Examples Operator SUM Returns the sum of two or more numbers. `SUM( , )` or `+` `SUM({Sold},{Not sold})` or `{Sold} + {Not sold}` Operator Subtract Returns the difference of two numbers. `-` `{Total stock} - {Sold stock}` Operator Multiply Returns the product of two numbers. `*` `{Sold} * {Price}` Operator Divide Returns one number divided by another. `/` `{Total sold} / {Price}` Operator Average Returns the average of the values in two or more fields. `AVERAGE( , )` `AVERAGE({Sold},{Not sold})` Operator Not equal to Check if one value is not equal to another value. `!=` `1!=2 =>True` (represented as a checked checkbox) Operator Equal to Compare if one value is equal to another value. `=` `1=1 =>True` (represented as a checked checkbox) Operator Greater than Compare if one value is greater than another value. `>` `1>5 =>False` (represented as an empty checkbox) Operator Less than Compare if one value is less than another value. `<` `5>1 =>False` (represented as an empty checkbox) Operator Less than or equal to Compare if one value is less than, or equal to, another value. `<=` `2<=2 =>True` (represented as a checked checkbox) Operator Greater than or equal to Compare if one value is greater than, or equal to, another value. `>=` `2<=2 =>True` (represented as a checked checkbox) Function CONCAT Concatenate two values. `CONCAT( , )` or `&` `CONCAT("Hello ", {Fullname}, " !")` or `"Hello " & {Fullname} & " !"` Function Condition Check whether the statement is true. If it is true, then do Action A. If it is false, then do Action B. `IF(statement, Action A, Action B)` `IF({UserId}, "https://dashboard.com/" + {UserId}, "")` Function Expression Returns true if all the arguments are true, returns false otherwise. `AND()` `AND({Field 1}),{Field 2})` or `{Field 1]AND{Field 2}` Function Expression Returns true if any one of the arguments is true. `OR()` `OR({Field 1}),{Field 2})` or `{Field 1]OR{Field 2}` Function Expression Reverses a true to false. `NOT()` `NOT(a` Returns the same as `a>=b` Function string Returns the length of a string. `LEN` `LEN("Hello World") =>11` Function string, regex Returns whether the input text matches a regular expression. `REGEX_MATCH()` `REGEX_MATCH("Good Morning","Good.Morning" =>True` (represented as a checked checkbox) Function string, regex, replacement Substitutes all matching substrings with a replacement string value. `REGEX_REPLACE()` `REGEX_REPLACE("Good Morning","M*",""=>"Good"` Function string, regex Returns the first substring that matches a regular expression. `REGEX_EXTRACT()` `REGEX_EXTRACT("Good Morning","M*",""=>"Morning"` Function string Removes whitespace at the beginning and end of string. `TRIM()` `TRIM(" Hello ") =>"Hello"` Function string Makes string uppercase. `UPPER()` `UPPER("Hello") =>"HELLO"` Function string Makes a string lowercase. `LOWER()` `LOWER("Hello") =>"hello"` Function string, howMany Extract howMany characters from the end of the string.Accepts string for first arg, number for second `RIGHT()` `RIGHT("Hello",2) =>"lo"` Function Compares two dates up to a unit and determines whether they are identical. Returns 1 if yes, 0 if no. `IS_SAME()` `IS_SAME({date 1},{date 2}, 'unit' =>` current acceptable units are: `'exact'`(matches all units) `'year', 'month','day'` Function Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no. `IS_BEFORE()` `IS_BEFORE({date 1},{date 2})` Function Determines if [date1] is later than [date2]. Returns 1 if yes, 0 if no. `IS_AFTER()` `IS_AFTER({date 1},{date 2})` Function Returns the four-digit year of a datetime. `YEAR()` `YEAR({Date},[timezone])` Function Returns the month of a datetime as a number between 1 (January) and 12 (December). `MONTH()` `MONTH({Date},[timezone])` Function Returns the day of the month of a datetime in the form of a number between 1-31. `DAY()` `DAY({Date},[timezone])` Function Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm). `HOUR()` `HOUR({Date},[timezone])` Function Returns the minute if datetime as a number. `MINUTE()` `MINUTE({Date},[timezone])` Function Extract how many characters from the beginning of the string.Accepts string for first arg, number for second `LEFT()` `LEFT("Hello",2) =>"He"` Function Calculates the number of the specified periods between two datetime values. `DATETIME_DIFF()` `DATETIME_DIFF([date1],[date2],'units')` Function Returns the item with the lowest value, or the item with the lowest value in an iterable. `MIN()` `MIN(number 1, number 2, number 3...)` Function Returns the largest item in an iterable. It can also be used to find the largest item between two or more parameters. `MAX()` `MAX(number 1, number 2, number 3...)` Function Returns the absolute value of the given number. If the number is a complex number, abs() returns its magnitude. `ABS()` `ABS(value)` Function Adds a time/date interval to a date and then returns the date. `DATEADD()` `DATEADD([date],[#],'units)` accepts years, months, weeks and days 9as well as singular counter-parts)

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