Math functions help you perform numerical calculations, statistical analysis, rounding operations, and financial computations in reports. These functions can be used to aggregate values, apply mathematical operations, generate random numbers, and evaluate statistical distributions.
In Plan, math functions help you create calculations for scenarios such as variance analysis, percentage calculations, forecasting, rounding, and financial modeling.
| Function | Description | SYNTAX | EXAMPLE | EXPLANATION |
|---|---|---|---|---|
| ABS | Returns the absolute value of a number without its sign. | ABS(value) | ABS(COLUMN1) | Returns the absolute value of COLUMN1 |
| AVERAGE | Returns the average of the specified values. | Average(value1,[value2]....) | AVERAGE(COLUMN1, COLUMN2) | Returns the average of COLUMN1, COLUMN1 |
| AVERAGEEXNEG | Returns the average of values excluding negative numbers. | AverageExNeg(value1,[value2]....) | ||
| AVERAGEEXZERO | Returns the average of values excluding zeros. | AverageExZero(value1,[value2]....) | ||
| AVERAGEEXZERONEG | Returns the average of values excluding zeros and negative numbers. | AverageExZeroNeg(value1,[value2]....) | ||
| CEILING | Rounds a number up to the nearest integer or specified multiple. | CEILING(value, significance) | CEILING(COLUMN1) | Returns the ceiling of COLUMN1 to the significance of two decimal |
| COUNT | Returns the count of values in a list. | COUNT (value1,value2....) | COUNT(Column1,Column2,Column3) | Returns the count of the number of items in a range |
| COUNTIF | Returns the count of values that satisfy a specified condition. | Count(list, condition) | COUNTIF([100,500,120],"<200") COUNTIF([[Quantity],[Units Sold]],BLANK) | Returns 2, since only two value in the given list matches the condition Returns 1,2 or 0 depending on how many blank values are in Quantity, Units Sold in each row. This value can be used to set conditional formatting or used with IF condition to fill value in another column |
| DIVIDE | Returns the result of dividing one value by another. | DIVIDE(Numerator, Denominator, Alternate) | DIVIDE(COLUMN1, COLUMN2, 0) | Returns COLUMN1/COLUMN2 and if any error, returns 0 |
| EVEN | Rounds a number up to the nearest even integer. | |||
| EXP | Returns e raised to the power of a specified number. | EXP(value) | EXP(COLUMN1) | Returns the exponential of COLUMN1 |
| FLOOR | Rounds a number down to the nearest specified multiple. | FLOOR(value, significance) | FLOOR(COLUMN1) | Returns the floor of COLUMN1 to the significance of two decimal |
| INDEXOF | Returns the index of the first occurrence of a value in a list. | |||
| LOG | Returns the logarithm of a number to a specified base. | LOG(value,base) | LOG(COLUMN1, 10) | Returns the Log to the base 10 of COLUMN1 |
| MAX | Returns the largest value in a list. | Max(value1,[value2]....) | MAX(COLUMN1,COLUMN2,COLUMN3) | Returns the maximum of COLUMN1, COLUMN2, COLUMN3 |
| MIN | Returns the smallest value in a list. | Min(value1,[value2]....) | MIN(COLUMN1,COLUMN2,COLUMN3) | Returns the minimum of COLUMN1, COLUMN2, COLUMN3 |
| MROUND | Rounds a number to the nearest multiple of a specified value. | |||
| NORMDIST | Returns the normal distribution for a specified value, mean, and standard deviation. | |||
| NORMSDIST | Returns the standard normal distribution for a specified value, with a mean of 0 and a standard deviation of 1. | |||
| NORMINV | Returns the inverse normal distribution for a specified value, mean, and standard deviation. | |||
| NORMSINV | Returns the inverse standard normal distribution for a specified value, with a mean of 0 and a standard deviation of 1. | |||
| ODD | Rounds a number up to the nearest odd integer. | |||
| POWER | Returns the result of a number raised to a specified power. | POW(value,power) | POW(COLUMN1, 2) | Returns the COLUMN1 to the power of 2 |
| RAND | Returns a random number between 0 and 1. | RAND() | RAND(0,1) | Returns a random number like 0.1,0.2,.. |
| RANDBETWEEN | Returns a random number between two specified values. | RANDBETWEEN(value1,value2) | RANDBETWEEN(0,100) | Returns a random number between 0 and 100 |
| ROUND | Rounds a number to the specified number of decimal places. | ROUND(value, significance) | ROUND(COLUMN1, 2) | Returns the round of COLUMN1 to the significance of two decimal |
| ROUNDDOWN | Rounds a number down toward zero. | |||
| ROUNDUP | Rounds a number up away from zero. | |||
| SQRT | Returns the square root of a number. | SQRT(value) | SQRT(COLUMN1) | Returns the exponential of COLUMN1 |
| SUM | Returns the sum of the specified values. | Sum(value1,[value2]....) | SUM(COLUMN1, COLUMN2) | Returns the sum of COLUMN1 and COLUMN2 |
| PCT | Returns the percentage value for a calculation. | PCT(value:number) | SALES + PCT(10) = SALES + 10% | Returns percentage value |
| PMT | Calculates the payment amount for a loan based on constant payments and interest rate. |
