Formula
Updated
Overview
Formula allows you to perform calculations within your Task Sheet. You can use Task fields as input values and apply mathematical, statistical, and text operations on them to produce precise results. Formula greatly improves the use value of Task Sheets and allows you to use them for budget and resource planning, sales analysis, capacity planning, supply planning, and much more.
The table shows the various formula functions available within Task Sheets and the actions they perform.
Formulas in Task Sheet | ||
Mathematical Functions | ||
Name | Description | Format |
SUM | Returns the sum of all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs | SUM(Field1, Field2, Field3) SUM(Field1, Number, Field2) |
MULTIPLY | Returns the product of all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs | MULTIPLY(Field1, Field2, Field3) MULTIPLY(Field1, Number, Field2) |
PRODUCT | Returns the product of all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs | PRODUCT(Field1, Field2, Field3) PRODUCT(Field1, Number, Field2) |
QUOTIENT | Returns the integer portion of the division of first input by second input. Number, currency, percentage type fields and numbers are acceptable inputs. | QUOTIENT(Field1, Field2) QUOTIENT(Field1, Number) |
Statistical Functions | ||
Name | Description | Format |
MAX | Returns the largest numerical value among all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs | MAX(Field1, Field2, Field3, Field4) MAX(Field1, Number, Field2, Field3) |
MIN | Returns the smallest numerical value among all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs | MIN(Field1, Field2, Field3, Field4) MIN(Field1, Number, Field2, Field3) |
MEAN | Returns the arithmetic mean of all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs. Empty cells are considered to have 0 value. | MEAN(Field1, Field2, Field3, Field4) MEAN(Field1, Number, Field2, Field3) |
AVERAGE | Returns the arithmetic mean of all the inputs. Number, currency, percentage type fields and numbers are acceptable inputs. Empty cells are considered to have 0 value. | AVERAGE(Field1, Field2, Field3, Field4) AVERAGE(Field1, Number, Field2, Field3) |
Logical Functions | ||
Name | Description | Format |
IF | Returns one value if a logical expression provided as input is `TRUE` and another value if it is `FALSE'. Logical expression can include fields and numbers while output can be a field value, number or string. | IF(Logical Expression, Value if true, Value if false) |
IFS | Evaluates multiple logical expressions and returns a value that corresponds to the first true logical expression. Logical expressions can include fields and numbers while output can be a field value, number or string. | IFS(Logical Expression 1, Value if true, Logical Expression 2, Value if true, ..... , Default value) |
Text Functions | ||
Name | Description | Format |
SUBSTITUTE | Replaces text in a string with another text. Single line text, multi line text, email and URL are acceptable input fields. | SUBSTITUE(Text field, Text to substitute, Text to substitute with) |
EXACT | Returns "TRUE" if two strings are exactly same and "FALSE" otherwise | EXACT(Field1, Field2) |
CONCATENATE | Joins several text strings into one text string using blankspace as a connector | CONCATENATE(Column_1, Column_2, ....) |
Date Functions | ||
Name | Description | Format |
DATE_ADD | Adds a "count" to Date. Number type field or a integer can be used for count | DATE.ADD(Date_Column, Number_Column) |
DATE_SUBTRACT | Subtract a "count" from the Date. Number type field or a positive integer can be used for count | DATE.SUBTRACT(Date_Column, Number_Column) |