Use advanced Formulas to make calculations using mathematical functions.
What you'll need
- 60 uses of Custom Fields, including Formula Fields, are available on the Free Forever Plan.
- Unlimited uses are available on all paid plans.
- Individual Custom Fields can be hidden from guests.
- Guests with edit or full permissions can edit existing Custom Fields but cannot create new ones.
- Formula Fields do not support using Custom Fields that contain text.
Variables surrounded by {}
in the following sections are optional variables and can be left out of a function without the function breaking. Ensure parentheses are acknowledged in your Formulas or you will receive an error message.
Syntax | Example | Expected Result |
AVERAGE(value1, {value2, ...}) | AVERAGE(2, 4, 8, 16) |
7.5 |
COS(angle[in radians]) | COS(1) |
0.5403023058681398 |
LOG(value, base) | LOG(8, 2) |
3 |
MAX(value1, {value2, ...}) | MAX(0.1, 0.2, 0.4, 0.8) |
0.8 |
MEDIAN(value1, {value2, ...}) | MEDIAN(1, 2, 3, 4, 5, 6) |
3.5 |
MIN(value1, {value2, ...}) | MIN(0.1, 0.2, 0.4, 0.8) |
0.1 |
MOD(dividend, divisor) | MOD(3, -2) |
-1 |
POWER(base, exponent) | POWER(5, 2) |
25 |
PRODUCT(factor1, {factor2, ...}) | PRODUCT(5, 15, 30) |
2250 If one of the fields used is blank, no value will be returned. |
QUOTIENT(dividend, divisor) | QUOTIENT(-10, 3) |
-3 |
ROUND(value, places) | ROUND(626.3, -3) |
1000 |
ROUNDDOWN(value, places) | ROUNDDOWN(-3.14159, 2) |
-3.14 |
ROUNDUP(value, places) | ROUNDUP(-3.14149, 2) |
-3.15 |
SIN(angle[in radians]) | SIN(1) |
0.8414709848078965 |
SQRT(value) | SQRT(16) |
4 |
SUM(value1, {value2, ...}) | SUM(-5, 15, 32) |
42 |
TAN(angle[in radians]) | TAN(1) |
1.5574077246549023 |
Complex mathematical functions
Below is a table of the more complex mathematical operators that ClickUp offers.
Syntax | Description | Example | Expected Result |
AVERAGEIF(criteria_range, criterion) | This function takes an array of numbers and a criterion then returns a number corresponding to the average of the array depending on the criterion. | AVERAGEIF([2, 4, 8, 16], ">=5") |
12 |
COUNT(value1, {value2, ...}) | This function takes as many numbers as you would like then returns a number corresponding to the amount of numbers input. | COUNT(1, 2, 3, 4) |
4 |
COUNTBLANK(variable1, {variable2, ...}) | This functions takes as many variables as you would like then returns a number corresponding to the amount of empty variables input. | COUNTBLANK(1, null, 3, 'a', '', 'c') |
2 |
COUNTIF(range, criterion) | This function takes as many variables as you would like, then returns a number corresponding to the amount of variables that match the criterion. |
Example 1: Example 2: |
Example 1: 2 Example 2: 4 |
COUNTUNIQUE(variable1, {variable2, ...}) | This function takes as many variables as you would like then returns a number corresponding to the amount of unique variables input. | COUNTUNIQUE('', '', 2, 5, 3, 3) |
4 |
LARGE({variable1, variable2, ...}, n) |
This function takes an array of numbers and a number then returns a number corresponding to the nth largest number from the numbers in the array, where n is user-defined. If n exceeds the amount of numbers input in the array, the function will break and return blank. |
LARGE([3,5,3,5,4,4,2,4,6,7], 3) |
5 |
RANDBETWEEN(low, high) | This function takes two numbers then returns an integer (whole number) corresponding to a random number between the two numbers input. This function includes the low and high numbers as possible outcomes. | RANDBETWEEN(-1, 1) |
Could return: [-1, 0, or 1] |
SMALL({variable1, variable2, ...}, n) |
This function takes an array of numbers and a number then returns a number corresponding to the nth smallest number from the numbers in the array, where n is user-defined. If n exceeds the amount of numbers input in the array, the function will break and return blank. |
SMALL([3,5,3,5,4,4,2,4,6,7], 3) |
3 |
SUMIF(range, criterion) | This functions takes an array of numbers and a criterion then returns a number corresponding to the sum of the array depending on the criterion. | SUMIF([2, 4, 8, 16], ">=5") |
24 |
TRUNC(value, {places}) |
This function takes two numbers then returns a number corresponding to the first number with a certain amount of significant digits specified by the second number. If places is left blank, the function will truncate to a whole number. This function does not round the number, it simply removes significant digits from the result. |
TRUNC(-8.96234, 1) |
-8.9 |