< Back to screenshot

Extra stats

This menu item adds some handy statistics to Excel.

Autocorrelation Enters an array formula that returns the autocorrelation function for a data vector. The first element of the result is the correlation at lag 0, the next is the correlation at lag 1, and so on (see also functions).

ANOVA Enters an array formula that returns a one-way ANOVA (Type II sums of squares) for balanced or unbalanced data. The output is a 4 row x 6 column ANOVA table. Unlike the Excel built in routines, the output is dynamic and unequal group sizes are allowed (see also functions).

Chi square Enters an array formula that gives the Chi squared value and associated P-value)  for a set of observed data.

Regression This procedure replaces Excel's procedure for linear regression (LINEST). It calculates the results in extended precision and also gives correct R2 values if the intercept is fixed (see also functions).

Goodness-of-fit. This routine computes the G-statistic (see Sokal, R. R. & Rohlf, F. J. ,1995, Biometry: the principles and practice of statistics in biological research. W. H. Freeman and Company, New York) for comparing the goodness-of-fit of a set of observed data to some specified model (or frequency distribution).

G-test. This routine computes the G-statistic to determine if the  frequency distributions of two or more groups of observed data differ.

Mantel test This routine does a computation of the Mantel test on large matrices. It is fast and handles large matrices (tested on matrices of random variables as large as 256 x 256 ). When invoked you will be asked to nominate ranges containing two distance matrices. These are copied to text files (this is slow for large matrices - so be patient) and then loaded by the PopTools DLL (RANDEVS.DLL). The DLL computes the test and you can then copy the results of the randomisation procedure back into the spreadsheet. The result of the test is a sorted vector of correlation coefficients for each randomisation, with the correlation coefficient of the original matrix as the first element in the vector. See also MANTEL function.

Principal components analysis. Enters an array formula for a dynamic PCA of a data matrix (see also functions).

Summary Stats. See this page.