﻿ Mathematical Functions
Nevron Vision for SharePoint Documentation
Pivot Engine / Functions / Mathematical Functions
In This Topic
Mathematical Functions
In This Topic
Syntax Description Example
ABS(fNumber) Returns the absolute value of number.

ABS(-2.5)
Returns: 2.5

AVG(f1[,f2,...fN]) Returns the average of a set of numbers. The arguments can be number variants or lists of number variants. Empty values are ignored from both the internal sum and count calculations.

AVG(10,15,20)
Returns: 15

AVG(ARRAY(10,15,20))
Returns: 15

AVG(ARRAY(10,15,EMPTY()),20,EMPTY())
Returns: 15

CEILING(fNumber, fMultiple) Rounds number away from 0 (zero) to the next instance of multiple. If multiple is not specified, the number rounds away from 0 to the next integer.

CEILING(1.7)
Returns: 2

CEILING(1.7, 0.25)
Returns: 1.75

FLOOR(fNumber, fMultiple) Rounds a number toward 0 (zero), to the next integer, or to the next instance of multiple. If multiple is not specified, the number rounds toward 0 to the next integer.

FLOOR(1.7)
Returns: 1

FLOOR(1.7, 0.25)
Returns: 1.5

INT(fNumber) Rounds a number down to the next integer.

INT(1.2)
Returns: 1

INT(-1.2)
Returns: -2

INTUP(fNumber) Rounds a number up to the next integer.

INTUP(1.2)
Returns: 2

INTUP(-1.2)
Returns: -1

LN(fNumber) Returns the natural logarithm of a number. The number must be positive.

LN(10)
Returns: 2.3026

LOG10(fNumber) Returns the base 10 logarithm of a number. The number must be positive

LOG10(10)
Returns: 1

MAGNITUDE(fConstA, fA, fConstB, fB) Returns the magnitude of the vector whose rise is A and whose run is B, multiplied by the respective constants constA and constB. MAGNITUDE is calculated according to the following formula: SQRT((constA * A) ^ 2 + (constB * B) ^ 2)

MAGNITUDE(1, 3, 1, 4)

Returns: 5

MAX(f1[, f2, ..., fN]) Returns the largest number from a list. Largest means closest to positive infinity. Empty values are skipped from the calculation. If only empty values are provided the function returns empty.

MAX(1, 3, 2)
Returns: 3

MAX(ARRAY(1,3,2,EMPTY()))
Returns: 3

MAX(ARRAY(EMPTY()))
Returns: empty

MAXVAR(var1[, var2, ..., varN]) Returns the largest variant from a list. Works with all variant types for which a comparison is defined. Empty values are skipped from the calculation. If only empty values are provided the function returns empty.

MINVAR(1, 3, 2)
Returns: 3

MINVAR(ARRAY(1,3,2,EMPTY()))
Returns: 3

=MINVAR(DATETIME("2008-09-15T09:30:41.770"),DATETIME("2009-09-15T09:30:41.770"))

Returns: DATETIME("2009-09-15T09:30:41.770")

MEDIAN(f1[, f2, ..., fN]) Returns a value which splits the set in half. Half of the values are smaller than the median value and half are larger. Empty values are skipped from the calculation.

MEDIAN(1,3,2)
Returns: 2

MEDIAN(1,3,5,6)
Returns: 4

MIN(f1[, f2, ..., fN]) Returns the smallest number from a list. Smallest means closest to negative infinity. Empty values are skipped from the calculation. If only empty values are provided the function returns empty. MIN(1, 3, 2)
Returns: 1

MIN(ARRAY(1,3,2,EMPTY()))
Returns: 1

MIN(ARRAY(EMPTY()))
Returns: empty
MINVAR(var1[, var2, ..., varN]) Returns the smallest variant from a list. Works with all variant types for which a comparison is defined. Empty values are skipped from the calculation. If only empty values are provided the function returns empty.

MINVAR(1, 3, 2)
Returns: 1

MINVAR(ARRAY(1,3,2,EMPTY()))
Returns: 1

=MINVAR(DATETIME("2008-09-15T09:30:41.770"),DATETIME("2009-09-15T09:30:41.770"))

Returns: DATETIME("2008-09-15T09:30:41.770")

MODULUS(fNumber, fDivisor) Returns the remainder (modulus) resulting when a number is divided by a divisor. The result has the same sign as the divisor.

MODULUS(5, 1.4)
Returns: 0.8.

MODULUS(5, -1.4)
Returns -0.6.

PERCENTILE(arr, fPercent) Returns a value which splits the set at arbitrary percent. {Percent count} of the values are smaller than this value and the other ones are larger. Percentile50 is equivalent to the Median formula. You can use the percentile to compute quartiles and deciles. 1 quartile is equivalent to Percentile25. 1 decile is equal to Percentile10.

PERCENTILE(ARRAY(25,10,20,30), 25)
Returns: 15

PERCENTILE(ARRAY(25,10,20,30), 75)
Returns: 27.5

POW(fNumber, fExponent) Returns a number raised to the power of an exponent. POW(10, 2)
Returns: 100
ROUND(fNumber, nDigits).

Rounds a number to the precision represented by digits.
If digits is greater than 0, number is rounded by digits to the right of the decimal.
If digits is 0, number is rounded to an integer.
If digits is less than 0, number is rounded by digits to the left of the decimal.

ROUND(123.654,2)
Returns: 123.65

ROUND(123.654,0)
Returns: 124

ROUND(123.654,-1)
Returns: 120

SIGN(fNumber[,fFuzz]) Returns a value that represents the sign of a number. Returns 1 if number is positive, 0 if number is zero, or -1 if number is negative. Fuzz (optional) helps avoid floating-point roundoff errors when a calculation is almost zero.

SIGN(-10)
Returns: -1.

SIGN(0)
Returns: 0

SQRT(fNumber) Returns the square root of a number.

SQRT(4)
Returns: 2

STDDEV(arr, bEntirePopulation) Returns the standard deviation of the values in the set. It is computed as the positive square root of the variance.

STDDEV(ARRAY(4,7,13,16),true)
Returns: 4.74

STDDEV(ARRAY(4,7,13,16),false)
Returns: 5.48

SUM(f1[, f2, ..., fN]) Returns the sum of a list of numbers. Empty values are skipped from the calculation. If only empty values are provided the function returns empty. SUM(1,2,3)
Returns: 6

SUM(ARRAY(1,2,3,EMPTY()))
Returns: 6

SUM(ARRAY(EMPTY()))
Returns: empty
TRUNC(fNumber, fDigits) Returns a number truncated to number of digits.
If digits is greater than 0, number is truncated to number of digits to the right of the decimal.
If digits is 0, number is truncated to an integer.
If digits is less than 0, number is truncated to number of digits to the left of the decimal.

TRUNC(123.654,2)
Returns: 123.65.

TRUNC(123.654,0)
Returns: 123

TRUNC(123.654,-1)
Returns: 120

VARIANCE(arr, bEntirePopulation)

The variance is used to measure the tendency of the values in the set to deviate from the average.

If Entire Population is True, it is computed by this formula:

If Entire Population is False (sample variance), it is computed by this formula:

VARIANCE(ARRAY(4,7,13,16),true)
Returns: 22.5

VARIANCE(ARRAY(4,7,13,16),false)
Returns: 30