# 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

## 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