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.
Name |
Description |
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 |
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 |