A formula field will automatically calculate and derives its value by referring to the value of the specified fields or the numeric, text, or date values given in the formula. You can customize fields with formulas using functions like numeric, logical, date/time, & text functions. To create a formula, select the functions and pass any argument like numbers, date, time, already existing fields, custom fields, and text strings. You can set return type for every formula based on the function and arguments passed into the function. Also, you can use numeric and logic operators to create a formula.
The return type of a formula determines the type of data that you want to be returned from the formula.
Joins the elements of an array along with a separator.
Example: ARRAYJOIN([{EMPLOYEE ID},{WORK_IN_HRS}],'-')
In this example, a list of values is referred to and displayed as an array. The value of the Emp ID is Zyl01 and the value of work hours is 5, hence the output will be Zyl01-5.
Emp ID is a custom field. Its value will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Joins multiple values or expressions into one. If you want to concatenate text strings, you must enclose them within single quotes.
Example: CONCATENATE({FEATURE},'-',{SCOPE})
In this example, multiple custom fields are referred to in a single field and their values are concatenated. Similarly, you can refer to the custom fields or values which you want to display in a single field.
Feature and Scope are custom fields. Their values will be referred to calculate the output. You can replace the values with other custom fields, text strings, numeric values, date/time arguments, or other expressions.
Returns the number of characters in the given text string.
Example: LEN({DESIGN})
In this example, the character count including blank space in the text field “DESIGN” will be identified. If the DESIGN field contains the text “New Design to be implemented”, the output will be 28.
Design is a custom field. Its value will be referred to calculate the output. You can replace the values with text strings or text custom fields.
Evaluates if the given value is greater than the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.
Example: {REVENUE} > {EXPENSE}
Revenue and Expense are custom fields. Their value will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.
Evaluates if the given value is lesser than the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.
Example: {PERCENTAGE COMPLETION} < 80
Percentage completion is a custom field. Its values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.
Evaluates if the given value is greater than or equal to the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.
Example: {PROFIT} >= 10000
Profit is a custom field. Its values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.
Evaluates if the given value is lesser than or equal to the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.
Example: {TIME} <= 15
TIME is a custom field. Its values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Evaluates if the given value is equal to the other value in a logical expression. Returns true as output when logic satisfies, otherwise, returns false.
Example: {COMMISSION} == 15000
Commission is a custom field. Its values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Evaluates if the given value is not equal to the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.
Example: {WORK_IN_HRS} != 5
With this formula, you can calculate the cost per person. If the values of the fields Total cost and People count are 10000 and 20 respectively, then cost per person is 500.
Duration is a custom field. Its values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Evaluates if all the given arguments are true.
Example: AND({PERCENTAGE COMPLETE}<=90,{PERCENTAGE COMPLETE}>=50)
With this formula, you can identify if the percentage completion is between 50 and 90 percent. When the percentage completion value is greater than or equal to 50 and less than or equal to 90 and the output will be True. Otherwise, the output will be False.
Percentage Complete is a default field. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or other expressions.
Evaluates if at least one of given two values or parameters is true.
Example: OR({PERCENT}>50,{REVENUE}>{EXPENSE})
With this formula, you can identify whether a deal is qualified. When either percent value is greater than 50 or revenue is greater than expense, the output will be True. Otherwise, the output will be False.
Percent, Revenue, and Expense are custom fields. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Evaluates the logic and returns value1 if true or else returns value2.
Example: IF({PLANNED COST}>{ACTUAL COST},'OVERRUN','SURPLUS')
With this formula, you can identify if the budget is overrun or surplus. If the value of Planned cost is greater than Actual Cost, the output will be Overrun, otherwise, the output will be Disqualified.
Percent is a custom field. Its value will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
You can also nest the IF functions.
Example: IF({COMMISSION}>10, '$150', IF({COMMISSION}<=7, '$70', '$110))
In this example, if the commission is greater than 10, the output is $150, otherwise the nested IF function will be evaluated.
Adds the given numeric values or field parameters.
Example: {MATERIAL COST} + {ACTUAL COST}
With this example, you can create a formula field named “Total cost” with the above formula. If the value of the field Material Cost is 2000 and Actual Cost is 3000, then the output will be 5000.
Material cost and Actual cost are custom field. Their value will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.
Subtracts the given numeric values or field parameters.
Example: {REVENUE} - {EXPENSE}
With this formula you can calculate profit. If the values of the fields Revenue and Expense are 8000 and 5000 respectively, then profit is 3000.
Revenue and Expense are custom fields. Their values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.
Multiplies the given numeric values or field parameters.
Example: {COST PER UNIT} * {QUANTITY}
With this formula you can find the total cost. If the values of the fields Cost per unit and Quantity are 200 and 80 respectively, then the total cost will be 16000.
Cost per unit and Quantity are custom fields. Their values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.
Divides the given numeric values or field parameters.
Example: {TOTAL COST} / {PEOPLE COUNT}
With this formula you can calculate the cost per person. If the values of the fields Total cost and People count are 10000 and 20 respectively, then cost per person is 500.
Total cost and People count are custom fields. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Returns the average of the given set of numbers or field parameter.
Example: AVERAGE({PHASE1},{PHASE2},{PHASE3})
With this formula, you can calculate the average of different phase values. If the values of the fields phase1, phase2, and phase3 are 9000, 8000, and 10000 respectively, then the average phase value is 9000.
Phase1, Phase2, and Phase3 are custom fields. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.
Returns the number of given numeric values or fields only. Other text strings or characters will not be counted.
Example: COUNT(1,2,3,'','FOUR')
With this formula, you can count the numeric values or number field parameters. However, text string or blank space will not be counted. You can replace the values with custom numeric fields or numeric values.
Returns the integer that is less than or equal to the specified value.
Example: INT({PROFIT})
With this formula, you can find the profit value round off to a value less than or equal to itself. If the value of the field profit is 89.48, then the output will be 89.
Returns the largest of the given set of numbers or numeric fields.
Example: MAX(0.06*{COST PRICE},100)
With this formula you can identify the largest commission value. If the value of the field Cost price is 2000, then 0.06*2000 is 120 which is greater than the minimum value 100. The commission value is 120.
Returns the smallest of the given numbers or numeric fiels.
Example: MIN({PHASE1},{PHASE2})
With this formula, you can find the lowest phase value. If the value of the fields phase1 and phase2 are 2500 and 3000 respectively, the lowest phase value is 2500.
Returns the remainder by dividing the value by the divisor.
Example: MOD({TOTAL UNITS},{PEOPLE COUNT})
With this formula, you can find the remaining units available. If the value of the fields Total units and People count are 5000 and 2300 respectively, the remaining units are 400.
Returns the integer nearest to the specified value, constraining the new value as per the given precision.
Example: ROUND({PRICE} * {DISCOUNT}, 2)
With this formula, you can calculate the discounted price rounded off to the nearest value and decimal places constrained based on precision. If the value of the fields Price and Discount are 999 and 0.07 respectively, the output of Discounted price is 69.93.
Calculates the sum of the given values or field parameters.
Example: SUM({LIABILITIES},{EQUITY})
With this formula, you can calculate the value of the assets. If the value of the fields Liabilities and Equity are 100000 and 10000 respectively, the value of the assets is 110000.
Returns the difference between the two specified dates in the given units.
Example: DATETIME_DIFF({DATE},TODAY(),'DAYS')
With this formula, you can find the difference between two given dates. If the given date is 07-15-2019, today's date is 07-20-2019, and the specified unit is "days", the output will be 5 days. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.
Returns the day value of the given date parameter as a number from 1 through 31.
Example: DAY({COMPLETION DATE})
With this formula, you can find the day value of the given date. If the given date is 07-15-2019, the output will be 15. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.
Returns the hour value of the given date parameter as a number from 0 (12:00 am) through 23 (11:00 pm).
Example: HOUR('07-12-2019 12:45 PM')
To pass a date/time value as an argument, you must enclose them within single quotes. Supported date and time format is mm-dd-yyyy and hh:mm.
Returns the minute value of the given date parameter as a number from 0 through 59.
Example: MINUTE('07-12-2019 12:45 PM')
To pass a date/time value as an argument, you must enclose them within single quotes. Supported date and time format is mm-dd-yyyy and hh:mm.
Returns the month of the given date as a number from 1 through 12.
Example: MONTH('07-23-2019')
To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.
Returns the current date and time.
Example: NOW()
Returns today’s date and time as 12.00 am.
Example: TODAY()
Calculates the number of days between the current date and another date.
Example: FROMNOW({START DATE})
If the current date is 07-20-2019 and Start date is 07-15-2019, the output will be 5 days. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.
Returns the current week value of the given date parameter.
Example: WEEKNUM({DUE DATE})
If the Due date is 07-15-2019, the output week number will be 29. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.
Returns the year of the given date parameter.
Example: YEAR({COMPLETED DATE})
If the Completed date is 07-15-2019, the output will be 2019. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.