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:
- The x value is less than 0
- The numerator or denominator is less than 1
#VALUE!
Occurs if:
- 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 |