PHPExcel classes
Back to top  

PHPExcel_Calculation_Statistical

category PHPExcel
package PHPExcel_Calculation
copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)

 Methods

AVEDEV

AVEDEV() : float
Static

Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.

Excel Function: AVEDEV(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

AVERAGE

AVERAGE() : float
Static

Returns the average (arithmetic mean) of the arguments

Excel Function: AVERAGE(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

AVERAGEA

AVERAGEA() : float
Static

Returns the average of its arguments, including numbers, text, and logical values

Excel Function: AVERAGEA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

AVERAGEIF

AVERAGEIF($aArgs, string $condition, mixed[] $averageArgs) : float
Static

Returns the average value from a range of cells that contain numbers within the list of arguments

Excel Function: AVERAGEIF(value1[,value2[, ...]],condition)

access public
category Mathematical and Trigonometric Functions

Parameters

$aArgs

$condition

string

The criteria that defines which cells will be checked.

$averageArgs

mixed[]

Data values

Returns

float

BETADIST

BETADIST(float $value, float $alpha, float $beta, $rMin, $rMax) : float
Static

Returns the beta distribution.

Parameters

$value

float

Value at which you want to evaluate the distribution

$alpha

float

Parameter to the distribution

$beta

float

Parameter to the distribution

$rMin

$rMax

Returns

float

BETAINV

BETAINV(float $probability, float $alpha, float $beta, float $rMin, float $rMax) : float
Static

Returns the inverse of the beta distribution.

Parameters

$probability

float

Probability at which you want to evaluate the distribution

$alpha

float

Parameter to the distribution

$beta

float

Parameter to the distribution

$rMin

float

Minimum value

$rMax

float

Maximum value

Returns

float

BINOMDIST

BINOMDIST(float $value, float $trials, float $probability, boolean $cumulative) : float
Static

Returns the individual term binomial distribution probability. Use BINOMDIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOMDIST can calculate the probability that two of the next three babies born are male.

todo Cumulative distribution function

Parameters

$value

float

Number of successes in trials

$trials

float

Number of trials

$probability

float

Probability of success on each trial

$cumulative

boolean

Returns

float

CHIDIST

CHIDIST(float $value, float $degrees) : float
Static

Returns the one-tailed probability of the chi-squared distribution.

Parameters

$value

float

Value for the function

$degrees

float

degrees of freedom

Returns

float

CHIINV

CHIINV(float $probability, float $degrees) : float
Static

Returns the one-tailed probability of the chi-squared distribution.

Parameters

$probability

float

Probability for the function

$degrees

float

degrees of freedom

Returns

float

CONFIDENCE

CONFIDENCE(float $alpha, float $stdDev, float $size) : float
Static

Returns the confidence interval for a population mean

Parameters

$alpha

float

$stdDev

float

Standard Deviation

$size

float

Returns

float

CORREL

CORREL(array $yValues, array $xValues) : float
Static

Returns covariance, the average of the products of deviations for each data point pair.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

COUNT

COUNT() : int
Static

Counts the number of cells that contain numbers within the list of arguments

Excel Function: COUNT(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

int

COUNTA

COUNTA() : int
Static

Counts the number of cells that are not empty within the list of arguments

Excel Function: COUNTA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

int

COUNTBLANK

COUNTBLANK() : int
Static

Counts the number of empty cells within the list of arguments

Excel Function: COUNTBLANK(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

int

COUNTIF

COUNTIF($aArgs, string $condition) : int
Static

Counts the number of cells that contain numbers within the list of arguments

Excel Function: COUNTIF(value1[,value2[, ...]],condition)

access public
category Statistical Functions

Parameters

$aArgs

$condition

string

The criteria that defines which cells will be counted.

Returns

int

COVAR

COVAR(array $yValues, array $xValues) : float
Static

Returns covariance, the average of the products of deviations for each data point pair.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

CRITBINOM

CRITBINOM(float $trials, float $probability, float $alpha) : int
Static

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value

See http://support.microsoft.com/kb/828117/ for details of the algorithm used

todo Warning. This implementation differs from the algorithm detailed on the MS web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess This eliminates a potential endless loop error, but may have an adverse affect on the accuracy of the function (although all my tests have so far returned correct results).

Parameters

$trials

float

number of Bernoulli trials

$probability

float

probability of a success on each trial

$alpha

float

criterion value

Returns

int

DEVSQ

DEVSQ() : float
Static

Returns the sum of squares of deviations of data points from their sample mean.

Excel Function: DEVSQ(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

EXPONDIST

EXPONDIST(float $value, float $lambda, boolean $cumulative) : float
Static

Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPONDIST to determine the probability that the process takes at most 1 minute.

Parameters

$value

float

Value of the function

$lambda

float

The parameter value

$cumulative

boolean

Returns

float

FISHER

FISHER(float $value) : float
Static

Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed. Use this function to perform hypothesis testing on the correlation coefficient.

Parameters

$value

float

Returns

float

FISHERINV

FISHERINV(float $value) : float
Static

Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x.

Parameters

$value

float

Returns

float

FORECAST

FORECAST(float $xValue, array $yValues, array $xValues) : float
Static

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value.

Parameters

$xValue

float

Value of X for which we want to find Y

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

GAMMADIST

GAMMADIST(float $value, float $a, float $b, boolean $cumulative) : float
Static

Returns the gamma distribution.

Parameters

$value

float

Value at which you want to evaluate the distribution

$a

float

Parameter to the distribution

$b

float

Parameter to the distribution

$cumulative

boolean

Returns

float

GAMMAINV

GAMMAINV(float $probability, float $alpha, float $beta) : float
Static

Returns the inverse of the beta distribution.

Parameters

$probability

float

Probability at which you want to evaluate the distribution

$alpha

float

Parameter to the distribution

$beta

float

Parameter to the distribution

Returns

float

GAMMALN

GAMMALN(float $value) : float
Static

Returns the natural logarithm of the gamma function.

Parameters

$value

float

Returns

float

GEOMEAN

GEOMEAN() : float
Static

Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.

Excel Function: GEOMEAN(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

GROWTH

GROWTH(array $yValues, array $xValues, array $newValues, boolean $const) : array
Static

Returns values along a predicted emponential trend

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

$newValues

array

of mixed Values of X for which we want to find Y

$const

boolean

A logical value specifying whether to force the intersect to equal 0.

Returns

arrayof float

HARMEAN

HARMEAN() : float
Static

Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.

Excel Function: HARMEAN(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

HYPGEOMDIST

HYPGEOMDIST(float $sampleSuccesses, float $sampleNumber, float $populationSuccesses, float $populationNumber) : float
Static

Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size.

Parameters

$sampleSuccesses

float

Number of successes in the sample

$sampleNumber

float

Size of the sample

$populationSuccesses

float

Number of successes in the population

$populationNumber

float

Population size

Returns

float

INTERCEPT

INTERCEPT(array $yValues, array $xValues) : float
Static

Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

KURT

KURT() : float
Static

Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.

Returns

float

LARGE

LARGE() : float
Static

Returns the nth largest value in a data set. You can use this function to select a value based on its relative standing.

Excel Function: LARGE(value1[,value2[, ...]],entry)

access public
category Statistical Functions

Returns

float

LINEST

LINEST(array $yValues, array $xValues, boolean $const, boolean $stats) : array
Static

Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that describes the line.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

$const

boolean

A logical value specifying whether to force the intersect to equal 0.

$stats

boolean

A logical value specifying whether to return additional regression statistics.

Returns

array

LOGEST

LOGEST(array $yValues, array $xValues, boolean $const, boolean $stats) : array
Static

Calculates an exponential curve that best fits the X and Y data series, and then returns an array that describes the line.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

$const

boolean

A logical value specifying whether to force the intersect to equal 0.

$stats

boolean

A logical value specifying whether to return additional regression statistics.

Returns

array

LOGINV

LOGINV(float $probability, float $mean, float $stdDev) : float
Static

Returns the inverse of the normal cumulative distribution

todo Try implementing P J Acklam's refinement algorithm for greater accuracy if I can get my head round the mathematics (as described at) http://home.online.no/~pjacklam/notes/invnorm/

Parameters

$probability

float

$mean

float

$stdDev

float

Returns

float

LOGNORMDIST

LOGNORMDIST(float $value, float $mean, float $stdDev) : float
Static

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev.

Parameters

$value

float

$mean

float

$stdDev

float

Returns

float

MAX

MAX() : float
Static

MAX returns the value of the element of the values passed that has the highest value, with negative numbers considered smaller than positive numbers.

Excel Function: MAX(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

MAXA

MAXA() : float
Static

Returns the greatest value in a list of arguments, including numbers, text, and logical values

Excel Function: MAXA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

MAXIF

MAXIF($aArgs, string $condition, $sumArgs) : float
Static

Counts the maximum value within a range of cells that contain numbers within the list of arguments

Excel Function: MAXIF(value1[,value2[, ...]],condition)

access public
category Mathematical and Trigonometric Functions

Parameters

$aArgs

$condition

string

The criteria that defines which cells will be checked.

$sumArgs

Returns

float

MEDIAN

MEDIAN() : float
Static

Returns the median of the given numbers. The median is the number in the middle of a set of numbers.

Excel Function: MEDIAN(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

MIN

MIN() : float
Static

MIN returns the value of the element of the values passed that has the smallest value, with negative numbers considered smaller than positive numbers.

Excel Function: MIN(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

MINA

MINA() : float
Static

Returns the smallest value in a list of arguments, including numbers, text, and logical values

Excel Function: MINA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

MINIF

MINIF($aArgs, string $condition, $sumArgs) : float
Static

Returns the minimum value within a range of cells that contain numbers within the list of arguments

Excel Function: MINIF(value1[,value2[, ...]],condition)

access public
category Mathematical and Trigonometric Functions

Parameters

$aArgs

$condition

string

The criteria that defines which cells will be checked.

$sumArgs

Returns

float

MODE

MODE() : float
Static

Returns the most frequently occurring, or repetitive, value in an array or range of data

Excel Function: MODE(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

NEGBINOMDIST

NEGBINOMDIST(float $failures, float $successes, float $probability) : float
Static

Returns the negative binomial distribution. NEGBINOMDIST returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent.

Parameters

$failures

float

Number of Failures

$successes

float

Threshold number of Successes

$probability

float

Probability of success on each trial

Returns

float

NORMDIST

NORMDIST(float $value, float $mean, float $stdDev, boolean $cumulative) : float
Static

Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.

Parameters

$value

float

$mean

float

Mean Value

$stdDev

float

Standard Deviation

$cumulative

boolean

Returns

float

NORMINV

NORMINV($probability, float $mean, float $stdDev) : float
Static

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Parameters

$probability

$mean

float

Mean Value

$stdDev

float

Standard Deviation

Returns

float

NORMSDIST

NORMSDIST(float $value) : float
Static

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.

Parameters

$value

float

Returns

float

NORMSINV

NORMSINV(float $value) : float
Static

Returns the inverse of the standard normal cumulative distribution

Parameters

$value

float

Returns

float

PERCENTILE

PERCENTILE() : float
Static

Returns the nth percentile of values in a range..

Excel Function: PERCENTILE(value1[,value2[, ...]],entry)

access public
category Statistical Functions

Returns

float

PERCENTRANK

PERCENTRANK(array $valueSet, \number $value, \number $significance) : float
Static

Returns the rank of a value in a data set as a percentage of the data set.

Parameters

$valueSet

array

of number An array of, or a reference to, a list of numbers.

$value

\number

The number whose rank you want to find.

$significance

\number

The number of significant digits for the returned percentage value.

Returns

float

PERMUT

PERMUT(int $numObjs, int $numInSet) : int
Static

Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant. Use this function for lottery-style probability calculations.

Parameters

$numObjs

int

Number of different objects

$numInSet

int

Number of objects in each permutation

Returns

intNumber of permutations

POISSON

POISSON(float $value, float $mean, boolean $cumulative) : float
Static

Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.

Parameters

$value

float

$mean

float

Mean Value

$cumulative

boolean

Returns

float

QUARTILE

QUARTILE() : float
Static

Returns the quartile of a data set.

Excel Function: QUARTILE(value1[,value2[, ...]],entry)

access public
category Statistical Functions

Returns

float

RANK

RANK(\number $value, array $valueSet, mixed $order) : float
Static

Returns the rank of a number in a list of numbers.

Parameters

$value

\number

The number whose rank you want to find.

$valueSet

array

of number An array of, or a reference to, a list of numbers.

$order

mixed

Order to sort the values in the value set

Returns

float

RSQ

RSQ(array $yValues, array $xValues) : float
Static

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

SKEW

SKEW() : float
Static

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

Returns

float

SLOPE

SLOPE(array $yValues, array $xValues) : float
Static

Returns the slope of the linear regression line through data points in known_y's and known_x's.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

SMALL

SMALL() : float
Static

Returns the nth smallest value in a data set. You can use this function to select a value based on its relative standing.

Excel Function: SMALL(value1[,value2[, ...]],entry)

access public
category Statistical Functions

Returns

float

STANDARDIZE

STANDARDIZE(float $value, float $mean, float $stdDev) : float
Static

Returns a normalized value from a distribution characterized by mean and standard_dev.

Parameters

$value

float

Value to normalize

$mean

float

Mean Value

$stdDev

float

Standard Deviation

Returns

floatStandardized value

STDEV

STDEV() : float
Static

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Excel Function: STDEV(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

STDEVA

STDEVA() : float
Static

Estimates standard deviation based on a sample, including numbers, text, and logical values

Excel Function: STDEVA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

STDEVP

STDEVP() : float
Static

Calculates standard deviation based on the entire population

Excel Function: STDEVP(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

STDEVPA

STDEVPA() : float
Static

Calculates standard deviation based on the entire population, including numbers, text, and logical values

Excel Function: STDEVPA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

STEYX

STEYX(array $yValues, array $xValues) : float
Static

Returns the standard error of the predicted y-value for each x in the regression.

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

Returns

float

TDIST

TDIST(float $value, float $degrees, float $tails) : float
Static

Returns the probability of Student's T distribution.

Parameters

$value

float

Value for the function

$degrees

float

degrees of freedom

$tails

float

number of tails (1 or 2)

Returns

float

TINV

TINV(float $probability, float $degrees) : float
Static

Returns the one-tailed probability of the chi-squared distribution.

Parameters

$probability

float

Probability for the function

$degrees

float

degrees of freedom

Returns

float

TREND

TREND(array $yValues, array $xValues, array $newValues, boolean $const) : array
Static

Returns values along a linear trend

Parameters

$yValues

array

of mixed Data Series Y

$xValues

array

of mixed Data Series X

$newValues

array

of mixed Values of X for which we want to find Y

$const

boolean

A logical value specifying whether to force the intersect to equal 0.

Returns

arrayof float

TRIMMEAN

TRIMMEAN() : float
Static

Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set.

Excel Function: TRIMEAN(value1[,value2[, ...]],$discard)

access public
category Statistical Functions

Returns

float

VARA

VARA() : float
Static

Estimates variance based on a sample, including numbers, text, and logical values

Excel Function: VARA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

VARFunc

VARFunc() : float
Static

Estimates variance based on a sample.

Excel Function: VAR(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

VARP

VARP() : float
Static

Calculates variance based on the entire population

Excel Function: VARP(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

VARPA

VARPA() : float
Static

Calculates variance based on the entire population, including numbers, text, and logical values

Excel Function: VARPA(value1[,value2[, ...]])

access public
category Statistical Functions

Returns

float

WEIBULL

WEIBULL(float $value, float $alpha, float $beta, boolean $cumulative) : float
Static

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.

Parameters

$value

float

$alpha

float

Alpha Parameter

$beta

float

Beta Parameter

$cumulative

boolean

Returns

float

ZTEST

ZTEST(float $dataSet, float $m0, float $sigma) : float
Static

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.

Parameters

$dataSet

float

$m0

float

Alpha Parameter

$sigma

float

Beta Parameter

Returns

float

Beta function.

_beta(\p $p, \q $q) : 
Static
author Jaco van Kooten

Parameters

$p

\p

require p>0

$q

\q

require q>0

Returns

if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow

Evaluates of continued fraction part of incomplete beta function.

_betaFraction($x, $p, $q) 
Static

Based on an idea from Numerical Recipes (W.H. Press et al, 1992).

author Jaco van Kooten

Parameters

$x

$p

$q

_checkTrendArrays() 

_checkTrendArrays($array1, $array2) 
Static

Parameters

$array1

$array2

_gamma() 

_gamma($data) 
Static

Parameters

$data

Incomplete beta function

_incompleteBeta(\x $x, \p $p, \q $q) : 
Static
author Jaco van Kooten
author Paul Meagher The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992).

Parameters

$x

\x

require 0<=x<=1

$p

\p

require p>0

$q

\q

require q>0

Returns

if x<0, p<=0, q<=0 or p+q>2.55E305 and 1 if x>1 to avoid errors and over/underflow

_incompleteGamma() 

_incompleteGamma($a, $x) 
Static

Parameters

$a

$x

_inverse_ncdf() 

_inverse_ncdf($p) 
Static

Parameters

$p

_inverse_ncdf2() 

_inverse_ncdf2($prob) 
Static

Parameters

$prob

_inverse_ncdf3() 

_inverse_ncdf3($p) 
Static

Parameters

$p

The natural logarithm of the beta function.

_logBeta(\p $p, \q $q) : 
Static
author Jaco van Kooten

Parameters

$p

\p

require p>0

$q

\q

require q>0

Returns

if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow

_logGamma() 

_logGamma($x) 
Static

Parameters

$x

_modeCalc() 

_modeCalc($data) 
Static

Parameters

$data

 Properties

 

$_logBetaCache_p 
 

$_logBetaCache_q 
 

$_logBetaCache_result 
 

$_logGammaCache_result 
 

$_logGammaCache_x