< Contents

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. 

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