Functions
The following functions and (aliases) are included in PopTools. Some of these are Excel array formulas. The PopTools menu can be also used to enter some of the array formulas in worksheet cells, but the rest must be entered using Ctrl-Shift-Enter.
ACF
An array formula that returns the autocorrelation function of a vector.
ACF_lag
Returns the autocorrelation of a vector at the given lag.
AgeDist
An array formula that returns a column corresponding to the stable age distribution of a square population projection matrix (ie, a right eigenvector of the dominant eigenvalue).
ANOVA
Returns an ANOVA table of the data matrix which must be arranged with data for each group in a column. Handles unbalanced designs (unlike Excel data analysis tools) and is dynamic.
ArcTanForm
Reverses TanForm. Returns the transformation: Min + (ArcTan(Value) + PI/2) * (Max - Min) / PI.
BetaDev
Returns a random variable from the Beta distribution with the given mean and standard deviation.
BetaDevA
Returns a column vector with the given length of random variables from the Beta distribution with the given mean and standard deviation.
BFGS
DO NOT ENTER THIS FUNCTION USING THE FUNCTION WIZARD An array formula that estimates parameters by maximum likelihood. See the help file.
BinomialDev
Returns a random variable from the binomial distribution (slow because of call to VBA - use dBinomialDev instead).
BinomialDevA
Returns a column vector with the given length of random variables from the binomial distribution.
BootMean
Array formula that returns confidence limits for the mean of a column vector using simple percentiles, bias-corrected and accelerated bias-corrected methods.
BootSD
Array formula that returns confidence limits for the standard deviation of a column vector using simple percentiles, bias-corrected and accelerated bias-corrected methods.
CA
An array formula that returns a correspondence analysis of a data matrix.
Cat
Takes each cell in “InputRange” and splices “Characters” (optional) between the text of the cells. If “Characters” is missing then the text values of “InputRange” are simply spliced together.
Cholesky
An array formula (alias for dCholesky) that returns the Cholesky decomposition of a positive definite matrix.
CM
An array formula that returns correlation matrix given the variance-covariance matrix.
ColCent
An Array formula that returns a column centred matrix (subtracts the column mean from each element).
ColCentStand
An Array formula that returns a column centred and standardised matrix (subtracts the column mean and divides each element by the standard deviation of the column).
ColStand
An Array formula that returns a column standardised matrix (divides each element by the standard deviation of the column).
ColSum
Returns the sum of each column of a matrix as a row vector.
ColumnPlot
An array formula that returns an array representing a column plot of X-Y data. Enter as an array formula (Ctrl-Shift-Enter) and plot as an XY graph.
CorrelatedDev
Returns a correlated random variable - see demo worksheet (slow because of call to VBA - use dCorrelatedDev instead).
CorrelatedDevA
Returns a column vector with the given length of correlated Normal random variables - see demo worksheet.
dACF
An array formula that returns the autocorrelation function of a vector.
dCA
An array formula that returns a correspondence analysis of a data matrix.
dChiTest
Performs a Chi-squared test on a data matrix (enter using the PopTools menus system for guidance).
dChiTestP
Returns the P-Value of a Chi-squared test on a data matrix (enter using the PopTools menus system for guidance).
dCholesky
A DLL array formula that returns the Cholesky decomposition of a positive definite matrix.
dDomEig
A DLL function that returns the eigenvalue of a square matrix with the largest real part.
dEigVal
A DLL array formula that returns the real and imaginary parts of the eigenvalues of a square matrix.
dEigVect
A DLL array formula that returns the eigenvectors and eigenvalues (last two columns of output) of a square matrix.
DeleteElement
An array formula that returns takes an input vector and returns a vector from which the element with the specified index has been deleted.
Determ
An Array formula that returns the determinant of a square matrix.
dExact
Array formula that returns Fishers Exact test for a 2x2 matrix.
dGammaDist
Returns the distribution function of the gamma distribution with parameters alpha and beta (same as Excel GAMMADIST with cumulative = FALSE).
dGammaDist
Returns the distribution function of the gamma distribution with parameters alpha and beta (same as Excel GAMMADIST with cumulative = FALSE).
dGammaLike
Array formula that returns the neg ln likelihood of the observed value assuming a Gamma distribution of the errors parameterised by its mean (expected) and shape parameter alpha.
dGTest
Performs a G test on a data matrix (enter using the PopTools menus system for guidance).
dGTestP
Returns the P-Value of a G-test on a data matrix (enter using the PopTools menus system for guidance).
Diag
Takes a vector of values and returns a matrix containing the values along the main diagonal and zeroes elsewhere.
DiagInvRoot
An Array formula that takes a vector argument and returns a diagonal matrix in which each diagonal element is the reciprocal of the square root of each element of the vector.
Diagonal
Return the diagonal elements of a matrix (off-diagonal elements set to zero).
DiagProd
Return the product of the diagonal elements of a matrix.
DiscreteDev
Returns a random variable from the Discrete distribution with the given Values and Frequencies (only relative frequencies are required, ie they need not sum to one).
Difference
An array formula that “differences” a data matrix. That is, it subtracts the mean of the matrix from each element.
DiffMatrix
Returns an array representing a lower triangular matrix of differences between a column of values. Upper triangle is zeroes.
DiffMatrix2
Returns an array representing a lower triangular matrix of squared differences between a column of values. Upper triangle is zeroes.
Distance
An array formula that returns a distance matrix based on a data matrix with the observations in the rows. See demo file for help with the arguments.
DistanceMetric
Return a distance metric for a matrix using an expression (see demonstration sheet).
dLogNormalLike
Array formula that returns the neg ln likelihood of the observed value assuming a log normal distribution of the errors parameterised by its mean (expected) and standard deviation.
dLUDecomp
Array formula that returns the lower/upper decomposition of a matrix.
dMatInv
An array formula that returns the inverse of a square matrix.
dMultinomialLike
Returns the negative of the log likelihood of the given number of events given a model for the cell probabilities.
dMultinomialProb
A DLL formula that returns the probability of the given number of events given a model for the cell probabilities.
dNegBinomial
The probability of N events given mean mu and clumping parameter K.
dNegBinomialLike
Array formula that returns the neg ln likelihood of the observed value assuming a neg binomial distribution of the errors parameterised by its mean (expected) and cluster parameter (K).
dNormalLike
Returns the negative of the log likelihood for a Normally distributed variable.
dNormDist
Replacement for Excel’s NORMDIST function with same syntax and arguments. Returns the probability of X given a mean and standard deviation (returns pdf if last argument is FALSE; cdf if true)..
DomEig
A formula (alias for dDomEig, but slower because of call to VBA) that returns the dominant eigenvalue of a square population projection matrix.
DominantEigenvalue
A formula (alias for dDomEig, but slower because of call to VBA) that returns the dominant eigenvalue of a square population projection matrix.
dPCA
A DLL array formula that performs a principal components analysis of a data matrix.
dPoisson
Replacement for Excel’s POISSON function with same syntax and arguments. Returns the probability of X given a Poisson process with given mean (returns pdf if last argument is FALSE; cdf if true)..
dPoissonLike
Returns the negative of the log likelihood for a Poisson variable.
dQR
A DLL array formula that returns the QR decomposition of a matrix with rows >= columns. QR factors the n * m matrix A as a product Q * R where Q is a (n x m) column-orthogonal matrix, and R is a (m x m) upper triangular matrix.
dQRSolve
Array formula that solves a system of equations using QR decomposition (see the SOLVE demo).
dResample
A DLL array formula that returns a random selection (with replacement) of values from “Vector”. “Size” (required in DLL version but in alias) is the number of samples to take. Can also take a matrix as an argument..
dResampleCols
An array formula that returns a random selection (with replacement) of columns from a matrix.
dResampleRows
An array formula that returns a random selection (with replacement) of rows from a matrix.
dRowMatrixMult
An array formula that pre-multiplies a state vector by a row representation of a square projection matrix (see Projection demo).
dShuffle
A DLL array formula that randomly shuffles Vector (can also take a matrix as an argument if arranged as shown in the demo sheet)..
dShuffleCols
An array formula that randomly shuffles the columns of a matrix.
dShuffleDistance
An array formula that randomly shuffles the rows and columns of a lower triangular distance matrix (assumes headers are in first row and column of input).
dShuffleRows
An array formula that randomly shuffles the rows of a matrix.
dSVD
A DLL array formula that returns the singular value decomposition of a matrix.
dSVDSolve
Array formula that solves a system of equations using singular value decomposition (see the SOLVE demo).
dVariance
Returns the sample variance of a data range using a more accurate formula than Excel’s native function VAR.
dVarianceP
Returns the population variance of a data range using a more accurate formula than Excel’s native function VARP.
dVC
A DLL array formula that returns the variance-covariance matrix of a data matrix (with species in columns, locations in rows)..
Eigenvector
An array formula (alias for dEigVect, but slower because of call to VBA) that returns the eigenvectors and eigenvalues (last two columns of output) of a square matrix.
Eigenvectors
An array formula (alias for dEigVect, but slower because of call to VBA) that returns the eigenvectors and eigenvalues (last two columns of output) of a square matrix.
Eigs
An array formula (alias for dEigVect, but slower because of call to VBA) that returns the eigenvectors and eigenvalues (last two columns of output) of a square matrix.
EigVal
An array formula that returns the real and imaginary parts of the eigenvalues of a square matrix.
Elasticity
Array formula that returns the elasticity (sensivity of the dominant eigenvalue to proportional changes) of each of the elements of a population projection matrix, X.
Equal
Checks that values of two ranges are contain equal values and are of the same size. For floating point numbers the precision of the comparison can be specified - default is 6 decimal points.
Euler
TAKE CARE - CALCULATION TIME CAN BE VERY LONG. Array formula for discrete-time simulation. See the help file for explanations of the arguments.
ExpDev
Returns a random variable from an exponential distribution (slow because of call to VBA - use dExpDev instead).
ExpDevA
Returns a column vector with the given length of random variables from an exponential distribution.
EYE
Array formula that reurns an identity matrix of the specified size.
F
Returns the value of “Result” if it evaluates to a number otherwise returns an empty string. Useful to hide the results of worksheet functions that can return errors.
FormulaText
Returns the formula in cell X as text.
GAdj
A function that returns adjusted G-statistic (log likelihood ratio test statistic for contingency tables).
GadjP
A function that returns adjusted parametric G-statistic (log likelihood ratio test statistic) for “Observed” versus “Expected”.
GammaDev
Returns a random variable from the gamma distribution (slow because of call to VBA - use dGammaDev instead).
GammaDevA
Returns a column vector with the given length of random variables from the gamma distribution.
GCrit
A function that returns the critical value of the G-statistic for range X - assumes (Rows-1) x (Cols-1) degrees of freedom and Type I error rate of alpha.
GeomDev
Returns a random variable from the geometric distribution (slow because of call to VBA - use dGeomDev instead).
GeomDevA
Returns a column vector with the given length of random variables from the geometric distribution.
GetCol
An Array formula that returns the nominated column from a matrix. (The result is actually a two-dimensional array with dimensions 1 To Rows and 1 To 1, where “Rows” is the number of rows in the input matrix).
GetDiag
Extracts the diagonal elements of a matrix into a column vector.
GetRow
An Array formula that returns the nominated row from a matrix. (The result is actually a two-dimensional array with dimensions 1 To 1 and 1 To Cols, where Cols is the number of columns in the input matrix).
GetRow
An Array formula that returns the nominated row from a matrix. (The result is actually a two-dimensional array with dimensions 1 To 1 and 1 To Cols, where Cols is the number of columns in the input matrix).
GTestPPValue
A function that returns adjusted P-value of parametric G-statistic for use with GAdjP.
GTestPValue
A function that returns adjusted P-value of G-statistic for use with Gadj.
Identity
Array formula that returns an identity matrix of the given size.
Incrementer1
This function increments every time the worksheet is calculated. If “init” is TRUE the function stays at the “StartValue”.
Incrementer2
This function increments every time the worksheet is calculated. If “init” is TRUE the function stays at the “StartValue”.
Interpolate
Array formula that uses linear interpolation to estimate the value of Y at each value of “ValueX” given vectors of X values and Y values.
Jacobi
An array formula that the returns the eigenvalues (in the first column) and associated eigenvectors of a symmetric matrix.
Jolly
Array formula to return Jolly-Seber abundance. “Animals” is a range of quadrat counts “Areas” is a range of quadrat areas. “TotalArea” is the total area. “Alpha” is the Type I error rate. “Replace” = sampling with replacement (TRUE/FALSE).
Life
Return analysis of projection matrix (enter via the menu system only - arguments are conditional).
Life
Array formula to return properties of a population projection matrix. If second argument=TRUE the first element is treated as a Pi, otherwise it is a Fi. If both arguments are matrices they are treated as fecundity (1st) and transition (2nd) matrices..
LifeTableStats
Calulates lambda, rate of increase, Ro, and generation time using the lx and mx values of a life table.
LifeTableToMatrix
Creates pre- or post-breeding projection matrix using the lx and mx values of a life table.
LogisticFn
Returns logistic transformation of X. “Inflection” is the point at which the function value is 0.5. “Slope” is the slope of the function. “Increasing” (TRUE/FALSE optional) determines whether the function increases with X (default) or decreases.
LogNormalDev
Returns a random variable from a log Normal distribution (slow because of call to VBA - use dLogNormalDev instead).
LogNormalDevA
Returns a column vector with the given length of random variables from a log Normal distribution parameterised by its mean and standard deviation.
LUDecomp
Array formula that returns the lower/upper decomposition of a matrix.
Mantel
If ReturnData is false, returns the number of times the correlation coefficient of randomised distance matrices exceeds that of original; otherwise returns all coefficients as an array (original at top) CARE - CALC TIME CAN BE VERY LONG.
Marq
DO NOT ENTER THIS FUNCTION USING THE FUNCTION WIZARD An array formula that estimates parameters by maximum likelihood. See the help file.
MatInv
An array formula that returns the inverse of a square matrix.
MatPow
Raises a matrix to an arbitray integer power.
MCorrel
Returns Pearson’s correlation coefficient of a symmetric matrix. Uses only the lower triangular part (ie, below diagonal).
MeanFreq
Returns the mean of a data vector of observations with the given frequencies.
Morisita
Computes Morisita’s index of dispersion.
MorisitaStand
Computes standardised Morisita index of dispersion. Ranges from -1 to +1 with 95% confidence limits at +0.5 and -0.5. Random patterns = 0, clumped patterns > 0 and uniform patterns < 0.
MStandardise
An array formula that “normalises” a lower triangular matrix so that the mean is zero and the standard deviation is one (Z-scores).
MSumProducts
Returns sum of products of the lower triangular part of a (generally symmetric) matrix.
mw2D
An array formula that returns a 64x64 element fractal surface using algorithm MW2D of Hastings and Sugihara’s book.
NegBinomial
The probability of N events given mean mu and clumping parameter K.
NegBinomialDev
Returns a random variable from the negative binomial distribution (slow because of call to VBA - use dNegBinomialDev instead).
NegBinomialDevA
Returns a column vector with the given length of random variables from the negative binomial distribution with cluster parameter K.
NegBinomialK
Returns estimate of clumping parameter K of negative binomial distribution, using observational data.
NormalDev
Returns a random variable from a Normal distribution (slow because of call to VBA - use dNormalDev instead).
NormalDevA
Returns a column vector with the given length of random variables from a Normal distribution.
NormalInt
Returns a random integer from a Normal distribution (slow because of call to VBA - use dNormalInt instead).
NormalIntA
Returns a column vector with the given length of random integers from a Normal distribution.
Normalise
An array formula that normalises “Matrix” by expressing each element as the proportion of the sum of all elements in the matrix.
ODEIntegrate
TAKE CARE - CALCULATION TIME CAN BE VERY LONG. Array formula that integrates a system of ODEs. See the help file for explanations of the arguments.
Pad
An array formula that returns the result of padding a range with spaces (default is pad left, set last argument to true for right padding).
PCA
An array formula that performs a principal components analysis of a data matrix.
Petersen
Returns Lincoln-Petersen abundance estimate for simple Capture-Mark-Recapture studies in closed populations.
PoissonDev
Returns a random variable from the Poisson distribution (slow because of call to VBA - use dPoissonDev instead).
PoissonDevA
Returns a column vector with the given length of random variables from the Poisson distribution.
Project
A DLL array formula that premultiplies a state vector by a projection matrix for the given number of iterates.
Protect
Return the value of the first argument if it does not return an error; otherwise return the second argument.
QR
An array formula (alias for dQR) that returns the QR decomposition of a matrix with rows >= columns. QR factors the n * m matrix A as a product Q * R where Q is a (n x m) column-orthogonal matrix, and R is a (m x m) upper triangular matrix.
QRSolve
Array formula that solves a system of equations using QR decomposition (see the SOLVE demo).
QSort
A function that sorts a data matrix (InputRange) arranged in columns. SortOrder (optional) can be ascending “A” or descending “D” (default = “A”). SortKey (optional) is the column on which sorting is based..
RandFix
If the argument is true, all PopTools random variables will return their mean value.
RandFix
Allows you to set PopTools functions that return random variables so that they return their expected values [=RandFix(TRUE)] or random values [=RandFix(FALSE)]. CARE: each call sets the PRNG for ALL workbooks.
RandInt
Returns a uniform random integer between the bounds specified (slow because of call to VBA - use dRandInt instead).
RandReal
Returns a uniform random variable between the bounds specified (slow because of call to VBA - use dRandReal instead).
RandSeed
Seeds the random number generator. Can be used to force all random variables to return the same results every time the workbook is calculated.
Reciprocal
Array formula that returns the element by element reciprocals of a matrix.
Regress
An array formula that eliminates non-numeric data before performing a regression using the inbuilt formula LINEST. Output is the same as LINEST (see Excel’s help for worksheet functions).
Regression
An array formula that returns the regression of Ydata on Xdata.
ReplaceElementStr
An array formula that returns takes an input vector and returns a vector in which the element with the specified index has been replaced by a string.
ReprodValue
An array formula that returns a column corresponding to the reproductive value of a square population projection matrix (ie, a left eigenvector of the dominant eigenvalue).
Resample
An array formula that returns a random selection (with replacement) of values from “Vector”. “Size” (optional) is the number of samples to take (default is number of values in “Vector”). Can also take a matrix as an argument (see demo).
ResampleCols
An array formula that returns a random selection (with replacement) of columns from a matrix.
ResampleRows
An array formula that returns a random selection (with replacement) of rows from a matrix.
Roots
Returns the real and complex parts of the roots of a polynomial. The coefficients must be arranged as a row or column in order of increasing degree in “CoeffVector”.
RowCent
An Array formula that returns a row centred matrix (subtracts the row mean from each element).
RowCentStand
An Array formula that returns a row centred and standardised matrix (subtracts the row mean and divides each element by the standard deviation of the row).
RowMatrixMult
An array formula that pre-multiplies a state vector by a row representation of a square projection matrix (see Projection demo).
RowStand
An Array formula that returns a row standardised matrix (divides each element by the standard deviation of the row).
RowSum
Returns the sum of each row of a matrix as a column vector.
Schnabel
Returns Schnabel estimate of abundance. “Caught” is a range containing the number trapped. “Recaptures’ is a matching range with the number recaptured. “Marked” is the number recaptured that were marked. “Alpha” is the required Type I error rate.
Sensitivity
Array formula that returns the sensitivity of the dominant eigenvalue to small changes in each of the elements of a population projection matrix, X.
SetRan
If the argument = 1 the Marsaglia random number generator will be used; otherwise default to the Mersenne Twister.
ShowProg
If the argument “ShowIt” is true (default), PopTools functions that take a long time to compute will display a progress metre. Set the argument to FALSE to stop display (which could slow processing).
Shuffle
An array formula that randomly shuffles Vector (can also take a matrix as an argument if arranged as shown in the demo sheet)..
ShuffleCols
An array formula that randomly shuffles the columns of a matrix.
ShuffleDistance
An array formula that randomly shuffles the rows and columns of a lower triangular distance matrix (assumes headers are in first row and column of input).
ShuffleRows
An array formula that randomly shuffles the rows of a matrix.
SimAnn
DO NOT ENTER THIS FUNCTION USING THE FUNCTION WIZARD An array formula that estimates parameters by maximum likelihood. See the help file.
Simplex
DO NOT ENTER THIS FUNCTION USING THE FUNCTION WIZARD An array formula that estimates parameters by maximum likelihood. See the help file.
Splice
Takes each cell in “InputRange” and splices “Characters” (optional) between the text of the cells. If “Characters” is missing then the text values of “InputRange” are simply spliced together (alias for Cat).
Spline
Array formula that fits a cubic polynomial to estimate the value of Y at “ValueX” given vectors of X values and Y values.
StageDist
Return the stable stage distribution of a projection matrix as a column vector.
Strip
Removes all occurrences of Str2 (optional) in Str1. If Str2 is missing, all occurrences of “$” are removed from Str1.
SVD
A DLL array formula that returns the singular value decomposition of a matrix.
SVDSolve
Array formula that solves a system of equations using singular value decomposition (see the SOLVE demo).
SymPow
Return an arbitrary power of a symetric matrix.
TanForm
Returns the transformation (see also ArcTanForm): Tan(PI * (Value - Min) / (Max - Min) - PI/2).
ToCol
An array formula that stacks the columns of a matrix on top of one another to return a column vector.
ToRow
An array formula that aligns the rows of a matrix in a single row vector (use with RowMatrixMult).
Trace
Returns the trace (product of diagonal elements) of a matrix.
TRand
Returns a random variable from the triangular distribution (slow because of call to VBA - use dTRand instead).
TRandA
Returns a column vector with the given length of random variables from the triangular distribution.
ULM
Return the values of a projection matrix as a string that can be pasted into the input file for program ULM.
UpTriang
An Array formula that returns the upper triangular form of a square matrix.
VarFreq
Returns the sample variance of a data vector of observations with the given frequencies.
VC
An array formula that returns the variance-covariance matrix of a data matrix (with species/characters in columns, locations/observations in rows
