Skip to content

Statistical Functions#

Statistical functions in Spectrum allow you to do statistical calculations on your data such as finding the average, the maximum, or the standard deviation of arguments.

Common Errors

Common Errors#

#NUM!

Occurs if:

  1. The x value is less than 0
  2. The numerator or denominator is less than 1

#VALUE!

Occurs if:

  1. the argument type isn't numeric

Functions#

These are the statistical functions in Spectrum:

AVERAGE#

Syntax#

AVERAGE(<number>;<number>,...)

Description#

Averages the arguments. Supports more than 255 arguments.

Examples

AVERAGE(Column1;Column2;Column3)

Column1 Column2 Column3 AVERAGE returns
1 9 10 6.6666
23 33 2 19.3333
43 34 70 49
22 11 3 12
6 23 52 27
32 234 25 97
24 342 3 123

CHISQDIST#

Syntax#

CHISQDIST(<number>,<number>;<boolean>)

Description#

Returns a chi square distribution at a given value x for a certain number of degrees of freedom, or returns the chi square density function or the cumulative distribution function for the chi square distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Examples

CHISQDIST(#sheet!VALUE; #sheet!DF; cumulative value)

VALUE DF Cumulative value CHISQDIST returns
3 2 false 0.1115
3 2 true 0.7768

EXPONDIST#

Syntax#

EXPONDIST(<number>, <number>; <boolean>)

Description#

Returns the exponential distribution for a certain value with given lambda.

or

Returns the exponential density function or the cumulative distribution function for the exponential distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Function Arguments
  • x: The value for which the distribution should be computed (NUMERIC)
  • Lambda: The lambda parameter of the exponential distribution (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If true then the cumulative distribution function is used. If false then the probability mass function is used (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True Uses the cumulative distribution function
False Uses the probability density function
Example

EXPONDIST(#input!VALUE; #input!LAMBDA; cumulative value)

VALUE LAMBDA Cumulativevalues EXPONDIST returns
0.5 1 false 0.60653066
0.5 1 true 0.39346934

FDIST#

Syntax#

FDIST(<number>,<number>,<number>;<boolean>)

Description#

Returns the F probability distribution for a certain value with given degrees of freedom for the numerator and degrees of freedom for the denominator.

or

Returns the Probability Density Function or the Cumulative Distribution Function for the F distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Function Arguments
  • x: The value for which the distribution should be computed (NUMERIC)
  • df1: Numerator degrees of freedom (NUMERIC)
  • df2: Denominator degrees of freedom (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If true then the cumulative distribution function is used. If false then the probability mass function is used. (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True uses the cumulative distribution function
False uses the probability density function
Examples

FDIST(#input!VALUE; #input!DF1; #input!DF2; cumulative value)

VALUE DF1 DF2 Cumulative value FDIST returns
1 2 5 false 0.308
1 2 5 true 0.5688

GAMMADIST#

Syntax#

GAMMADIST(<number>,<number>,<number>;<boolean>)

Description#

Returns the gamma distribution for a certain value with given parameters alpha and beta.

or

Returns the gamma density function or the cumulative distribution function for the gamma distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Function Arguments
  • x: The value for which the distribution should be computed (NUMERIC)
  • Alpha: The alpha parameter (shape) of the exponential distribution (NUMERIC)
  • Beta: The alpha parameter (scale) of the exponential distribution (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If true then the cumulative distribution function is used. If false then the probability mass function is used. (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True uses the cumulative distribution function
False uses the probability density function
Examples

GAMMADIST(#input!VALUE; #input!ALPHA; #input!BETA; false)

VALUE ALPHA BETA Cumulative value GAMMADIST returns
6 3 2 false 0.112
6 3 2 true 0.5768

LOGNORMDIST#

Syntax#

LOGNORMDIST(<number>,<number>,<number>;<boolean>)

Description#

Returns the log-normal distribution for a certain value with given mean and standard deviation.

or

Returns the log-normal density function or the cumulative distribution function for the log-normal distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Function Arguments
  • x: The value for which the distribution should be computed (NUMERIC)
  • Mean: The mean of the distribution (NUMERIC)
  • Standard deviation: The standard deviation of the distribution (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If true then the cumulative distribution function is used. If false then the probability mass function is used. (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True uses the cumulative distribution function
False uses the probability density function
Examples

LOGNORMDIST(#input!VALUE; #input!Mean; #input!Standard_deviation; false)

Value Mean Standard_deviation Cumulative value LOGNORMDIST returns
0.75 10 5 false 0.01281
0.75 10 5 true 0.01987

MAX#

Syntax#

MAX(<any>,<any>,...)

Description#

Returns the maximum of arguments. Arguments must be of the same data type. Supports more than 255 arguments.

Examples
Data type Column1 Column2 MAX returns
number 1 2 2
string a b b
boolean true false true
date Jun 22, 2012 06:22:34 Jun 22, 2012 07:02:16 Jun 22, 2012 07:02:16
Data type Column1 MAX returns
list [44,55,22,88,11,55,77] 88

MIN#

Syntax#

MIN(<any>,<any>,...)

Description#

Returns the minimum of arguments. Arguments must be of the same data type. Supports more than 255 arguments.

Examples
Data type Column1 Column2 MIN returns
number 1 2 1
string a b a
boolean true false false
date Jun 22, 2012 06:22:34 Jun 22, 2012 07:02:16 Jun 22, 2012 06:22:34
Data type Column1 MIN returns
list [44,55,22,88,11,55,77] 11

NORMDIST#

Syntax#

NORMDIST(<number>,<number>,<number>;<boolean>)

Description#

Returns the normal distribution for a certain value with given mean and standard deviation.

or

Returns the normal density function or the cumulative distribution function for the normal distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Function Arguments
  • x: The value for which the distribution should be computed (NUMERIC)
  • Mean: The mean of the distribution (NUMERIC)
  • Standard deviation: The standard deviation of the distribution (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If "true" then the cumulative distribution function is used. If "false" then the probability density function is used. (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True Uses the cumulative distribution function
False Uses the probability density function
Examples

NORMDIST(#input!Value;#input!Mean;#input!Standard_deviation;cumulative value)

Value Mean Standard_d Cumulative value NORMDIST returns
42 40 1.5 false 0.10934005
42 40 1.5 true 0.908789

STDEVP#

Syntax#

STDEVP(<number>;<number>;...)

Description#

Estimates standard deviation on the entire population. If they represent only a sample of the entire population, use STDEVS instead. STDEVP supports more than 255 arguments.

Examples

STDEVP(#sheet!Column1;#sheet!Column2)

Column1 Column2 STDEVP returns
1 9 4
23 33 5
43 34 4.5
22 11 5.5
6 23 8.5
32 234 101
24 342 159

STDEVS#

Syntax#

STDEVS(<number>;<number>;...)

Description#

Estimates standard deviation of its arguments. It assumes that the arguments are a sample of the population. If they represent the entire population, use STDEVP instead. STDEVS supports more than 255 arguments.

Examples

STDEVS(#sheet!Column1;#sheet!Column2)

Column1 Column2 STDEVP returns
1 9 5.656854349492381
23 33 [7.0710678118654755]
43 34 [6.363961030678928]
22 11 [7.7781745930520225]
6 23 [12.020815280171307]
32 234 [142.8355697996826]
24 342 [224.8599564173221 ]

TDIST#

Syntax#

TDIST(<number>,<number>;<boolean>)

Description#

Returns student t-distribution at a given value x for a certain number of degrees of freedom.

or

Returns the student t density function or the cumulative distribution function for the t distribution. This function is frequently used used to measure the degree of diversity between two data sets.

See also Common Errors above.

Function Arguments
  • x: The value at which to evaluate the distribution (NUMERIC)
  • df: The number of degrees of freedom (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If TRUE then the cumulative distribution function is used. If FALSE then the probability mass function is used. (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True Uses the cumulative distribution function
False Uses the probability density function
Examples

TDIST(#input!Value;#input!DF;cumulative value)

Value DF Cumulative value TDIST returns
1 10 false 0.2303
1 10 false 0.8295

WEIBULLDIST#

Syntax#

WEIBULLDIST( <number>;<number>;<number>;<boolean>)

Description#

Returns the weibull distribution for a certain value with given alpha and beta.

or

Returns the weibull density function or the cumulative distribution function for the weibull distribution. This function is frequently used used to measure the degree of diversity between two data sets.

Function Arguments
  • x: The value for which the distribution should be computed (NUMERIC)
  • Alpha: The alpha parameter of the distribution (NUMERIC)
  • Beta: The beta parameter of the distribution (NUMERIC)
  • Cumulative: Constant indicating the form of the function. If "true" then the cumulative distribution function is used. If "false" then the probability mass function is used. (BOOLEAN)
Cumulative/Boolean Logical argument which defines the type of distribution to be calculated
True Uses the cumulative distribution function
False Uses the probability density function
Example

WEIBULLDIST(#input!Value;#input!Alpha;#input!Beta;cumulative value)`

Value Alpha Beta Cumulative value WEIBULLDIST returns
105 20 100 false 0.035589
105 20 100 false 0.929581