Formula
Use the Formula tool to apply a formula to an existing column. The tool will create a new column with the transformed data.
Add a new column by applying formula(s) to an existing column. The tool creates a new column with the transformed data.
With the Output column(s) option, you can add new columns or replace existing selected columns in your data set. The columns append to your data set, using the formula name as the column name. This option is available in both the standard pipe and the big pipe.
You can click the Info icon in the todo sidebar to see a list of all formula definitions with examples.
Formula functions
Combine formulas to transform data into an existing column. Symon.AI creates a new column with the output.
Function  Description  Example  Syntax  Notes 

 Returns the absolute value of a number.  If you want to show the difference between this month's sales and last month's sales as a percentage, but you do not want the percent change to be a negative value, use a formula similar to the following example:
In this example, the formula is subtracting last month's sales from this month's sales and dividing it by last month's sales to get the percentage difference. 


 Returns the arccosine of a number. 

 
 Returns the arcsine of a number. 

 
 Returns the arctangent of a number. 

 
 Rounds a number up to the nearest integer. 

 
 Concatenates all the given text and numbers. 

 
 Returns either true or false if Text contains the specified Target text. 

 
 Returns the cosine of a given angle. 

 
 Returns a date given the specified Year, Month, and Day. 

 
 Returns either true or false if Text ends with the specified Ending text. 

 
 Rounds a number down to the nearest integer. 

 
 Returns the first input if true and the second input if false.  If the sale amount is greater than $100, you receive a 5% commission. Otherwise, you receive a 2% commission.
Nested If your sale amount is greater than $100, you receive 5% commission. If your sale is greater than $50, you receive 3%; otherwise, you receive 2%:



 Returns true for empty rows. Returns false for records that are not empty. 
In general, any base operation between a real number and null results in null, such as the following examples:

 
 Returns the base 10 logarithm of a number. 

 
 Returns the base2 logarithm of a number. 

 
 Returns the maximum value between columns for numbers and dates.  If, for each deal, you receive a 5% commission, but there is a minimum of $5 per deal you earn, you can use the following formula to determine whether the 5% commission is higher than the $5 minimum:



 Returns the minimum value between columns for numbers and dates.  If 5% commission is paid on each deal to a maximum of $100, the following formula pays either 5% commission or $100, whichever is less:



 Returns a number rounded to the nearest multiple. 

 
 Returns the current time. 
 
 Adds leading or trailing characters to the text to meet the specified length. 

 
 Returns the result of a number raised to a power. 

 
 Rounds a number to a specified number of digits.  If you want to round your results to the nearest cent, use a formula similar to this example:
In this example, the formula rounds the output to 2. 


 Returns the sine of a given angle. 

 
 Returns a square root of a number. 

 
 Returns either true or false if Text starts with the specified Starting text. 

 
 Returns the tangent of a given angle. 


Operators
Symon.AI offers the following types of calculation operators to use in your formulas:
Operator  Description  Example 

Add  Returns result of the addition of specified numbers. 
For example: 
 Filters data based on the specified conditions. Use to combine multiple conditions. 

Divide  Returns the result of the division of specified numbers. 
For example: 
Equal  Returns a 
For example: 
Less than  Returns a 
For example: 
Greater than  Returns the result of the number is greater than another number. 
For example: 
Greater than or equal  Returns the result of the number is greater than or equal to another number. 
For example: 
Less than or equal  Returns the result of the number less than or equal to another number. 
For example: 
Multiply  Returns the result of the multiplication of specificied numbers. 
For example: 
Module  Returns the remaining value after the number is divided by a divisor. 
For example: 
 Returns the value when one condition is not true. 

Not equal  Returns a 
For example: 
 Filters data based on the specified conditions. Use to combine multiple conditions. Need at least one condition to be 

Subtract  Returns the result of the the subtraction of specified numbers. 
For example: 
Text  A text value within a formula. 

 Filters data based on the specified conditions. Use to combine multiple conditions. Need at least one condition to be 

Constants
Symon.AI offers the following types of constant operators to use in your formulas:
Constant  Description  Example 

Natural base  Returns the natural logarithm of a value to the base of the mathematical constant 

 Returns the sum of the value of two numbers based on the 

 Returns the sum of the value based on the 

 Returns the sum of the value of two numbers based on the 

Data types
Symon.AI uses the following data types to construct formulas:
Boolean: Used to indicate the result value of either
True
orFalse
.Double quote
""
: Used to to create strings or text, such as “This is a string” or “Male”.Numeric: Used to indicate the value is in numeric form, either float or integer, such as
1 or 3.12
.Single quote
''
: Used to indicate columns, such as‘Age’
refers to the column Age.
Limitations
The behavior of round()
for floats can be surprising because most decimal fractions cannot be represented exactly as a float.
For example, round(2.675, 2)
will return 2.67
instead of the expected 2.68
.
You can read Floating Point Arithmetic: Issues and Limitations for more information.
Using Row Viewer Format
When you use the Row Viewer format, the format inherits the format from the preceding tool. But, some formulas may have a different output, based on one of the following scenarios:
There is not a preceding tool to inherit the format from.
The column types between the two tools are different and cannot inherit the format.
The function applied in the current tool takes precedence over the inherited format.
For more information, see Row Viewer
Some formula functions affect the decimal place without the Row Viewer format. The following functions always take precedence and prevent the inherited decimal place formatting:
round()
floor()
ceil()
The following functions use the inherited format:
abs()
sqrt()
log10()
sin()
cos()
asin()
acos()
atan()
pow()
if()
outputs the same format if the true results and the false results have the same format. If they do not have the same format, the input formats are ignored.
Error messaging
The Formula tool can find the following errors in your data set:
Syntax
error: An unknown function is occurring, or the function cannot parse due to a special character, or a missing bracket.Invalid argument
error: The tool detects that the argument is invalid due to a wrong data type.Too many arguments
error: The tool detects that there are too many arguments.Missing arguments
error: The tool detects that there are one or more arguments missing from the range of the number of allowed arguments.Unsupported data type
error: The tool detects an unsupported data type. The Formula tool only acceptsnumber
,boolean
,string
, anddate
data types.