# Formula Editor

The formula editor is where you write the formula's actual mathematical expressions and calculations. You can find the formula editor at the bottom of the “Add/Edit formula step” form.

For example, based on the data a client inserts as their KYC, the formula editor applies a calculation to return data such as the net income of a loan applicant or the age limit for a contract or the insurance premium for a policy. You can also test your formula before activating it. For more information about running tests, see Define Formula Expressions.

## Syntax

In the editor, you insert your formula expression based on a mathematical syntax comprised of the formula body and a call for calculation (the returned result). IntelliSense is available for quick selection of definitions and to provide additional information (such as properties and attributes) about the selected item.

IMPORTANT!
When writing a formula, make sure the calculations match the step you are on, because the result may be used as input in the subsequent steps.

Use the following syntax in the formula editor to define a formula output:

Copy
``result = <formula expression>;``

For formulas that return collections, use the following syntax (make sure that the formula expression includes an iteration argument):

Copy
``result[i] = < ... iterationArgument[i] ... >;``

For example, if based on a collection of item prices (`Values[i]`), you wish to generate a collection of objects that pair together item numbers with their corresponding prices, you can use the following formula:

Copy
``result[i] = new {Name = "Item" + i, Price = Values[i]}``

If you wish to aggregate preexisting objects into a collection, use the `EBS.Core.Formulas.ObjectVector` method:

Copy
``var item1 = new {Name = "Item1" , Price = Price1}var item2 = new {Name = "Item2" , Price = Price2}result = new EBS.Core.Formulas.ObjectVector<dynamic>(item1, item2);``

For recursive formulas, define a simple type whole number output and include a whole number argument in the Number of Iterations field (see Define Formula Expressions for details). For example, to generate a collection with the first x numbers in Fibonacci sequence, use a whole number input argument called x for the number of iterations (make sure x is greater than or equal to 2) and use the following formula expression:

Copy
``result = 1; result =1; result[i] = result[i-1] + result[i-2];``

When the formula body is complex, needs periodic update or must be simplified for transparency and traceability, we suggest you split it in separate steps, each with its own expression.

NOTE
You can include multi-line C# code in the formula expression, as long as you assign a result value:
Copy
``var a = 1;var b = 2;result = a + b;``

The usual operator precedence for operations such as addition “+”, substraction “-“, multiplication “*” and division “/” is enforced and you can further control this by using parantheses () or by splitting the formula in steps.

In a formula receiving an argument data of a complex type with properties A,B,C of type string and D,E of type numeric, we can write:

Copy
``var result = FROM(data).GROUPBY(["A","B"]).SUM("D");``

The result will be a collection of items with properties A,B and C, where C will be the SUM for the group determined by the content of properties A and B

Pressing Ctrl+Space will launch the IntelliSense that can help you learn more about the formula you are editing, keep track of parameters you’re typing, add calls to functions and various information with only a few keystrokes.

Formula can be of the following types:

• constant f = sum(1,n)
• linear f(x) = 2*x+1 , where x = 1,100
• 2-dimensional f(x,y) = x*y+30;
• n-dimensional etc.
• recursive f(x) = f(x-1)+20.
HINT
Already pre-defined Steps also appear in IntelliSense since they can be called in any subsequent step.

Click Save and Close.

## Formula Arguments

Primary formula arguments are defined in the formula input (see Define Formula Inputs). For example, if we have defined an argument called days in our formula input, we can create a formula step called years to convert the number of days into years with the following expression:

Copy
``result = days / 365;``

We can also use previous steps' outputs as arguments for subsequent step inputs. For example, after the above step, we can create a step that calculates an interest by multiplying the principal and rate input parameters with the years result from the previous step:

Copy
``result = principal * rate * years``

## Built-in Functions

You can include the following built-in functions in your formula expressions:

Function Description Example Result
SUM(Vector) Adds all items in a collection of whole numbers or decimals. SUM(myArray) 6
ABS(Number) It returns the absolute value of the number. ABS(-3) 3
POWER(Number, Exponent) Calculates how many times to use the number in a multiplication. POWER(10,3) 1000
ODD(Number) Specifies if the number is odd. ODD(12) false
EVEN(Number) Specifies if the number is even. EVEN(26) true
TRUNC(Number) Truncates the number to a specified number of decimal places. TRUNC(17.51) 17
ROUND(Number, Precision) Rounds the number to the specified number of digits. ROUND(17.51) 18
ROUNDUP(Number) Rounds the number upward to the specified number of digits. ROUNDUP(-0.6) 0
ROUNDDOWN(Number) Rounds the number downward to the specified number of digits. ROUNDDOWN(1.9) 1
FLOOR(Number) Receives one parameter and rounds the number down. FLOOR(6.7) 6
IIF(Condition, TrueExpression, FalseExpression) IF function is a "conditional function" because it returns a value based on the condition that you specify.
COUNT(Vector) Returns the number of numerical values (numbers and dates) in the list of arguments.
COUNTIF(Vector, FilterExpression) Counts the number of cells within the range that meet the specified criteria.
MIN(Collection) Returns the minimum value from an input parameter of type collection (whole number or decimal).
If the desired parameters are not available in a formula input, create your input as follows:
Copy
``var a = new decimal[] {100, 25, 3};result = MIN(a)``
NOTE
The code above may be tagged as incorrect by IntelliSense, however it is valid.
MIN(new decimal[] {100, 25, 3}) 3
MAX(Collection) Returns the maximum value from an input parameter of type collection (whole number or decimal).
If the desired parameters are not available in a formula input, create your input as follows:
Copy
``var a = new decimal[] {100, 25, 3};result = MAX(a)``
NOTE
The code above may be tagged as incorrect by IntelliSense, however it is valid.
MAX(new decimal[] {100, 25, 3}) 100
AVERAGE(Number1, Number2, Number3, …) Returns the average of the arguments. AVERAGE(1234,67543,5752) 24843
AVERAGE(Vector) Returns the average of the arguments in a collection.
Copy
``var a = new decimal[] {0, 25, 50};result = AVERAGE(a)``
25
SUMIF(Vector, FilterExpression) Adds up the cells in a specified range that meet a certain condition. SUMIF can evaluate only a single criteria. “it” is a mandatory term, standing for “item” of an array, for all Filter Expressions in all formulas which evaluate an expression.

SELECT(Vector, TransformExpression)   SELECT (IncomeList, it * Database (''IncomeAdjuster''))
RANGE(Vector, RangeOperators) Navigate inside an array
Copy
``var myRange = new decimal[] {1,2,3,4,5,6};result = RANGE(myArray,SKIP(2)])``

RANGE([1,2,3,4,5,6],SKIP(2)])

VECTOR(3,4,5,6)
SKIP(Number) Combined with RANGE function, you can skip an item of an array, in case for example you would like to add all items except the 3rd one in the array. TAKE(Number) Combined with RANGE function, you can take as many array items as indicated by the input parameter.
RATE (nper, pmt, pv, [fv], [type], [guess]) Returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate.
Arguments:
• nper - The total number of payment periods.
• pmt - The payment made each period.
• pv - The present value, or total value of all loan payments now.
• fv - [optional] The future value, or desired cash balance after last payment. Default is 0.
• type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
• guess - [optional] Your guess on the rate. Default is 10%.
RATE(36, 150, -5000) 0.42
PMT (rate, nper, pv, [fv], [type]) Calculates the payment for a loan based on constant payments and a constant interest rate.
Arguments:
• rate - The interest rate for a loan.
• nper - The total number of payments for a loan.
• pv - The present value, or the total amount that a series of future payments is worth now; also known as the principal.
• fv - [optional] The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• type - [optional] Indicates when payments are due:
• 0 or omitted - at the end of the period
• 1 - at the beginning of the period
A 3-year (36 months) loan for 10000 USD at 8% interest rate:
PMT(0.08/12, 36, 10000)
-313.36

## Data Set Calls

To extract value mappings from data sets (see Data Sets for details), use the following syntax:

Copy
``DataSet("<data set name>", ("<discriminant 1 name>", <discriminant 1 value>), ("<discriminant 2 name>", <discriminant 2 value>) ... )``

If all the discriminants are given as a parameter, the result will be a value. However, if one of the discriminants is not sent as a parameter, the result will be an array.

IMPORTANT!
You can only call data sets that are active. For details, see Data Set Versioning.