XLSTAT: Choosing a Statistical Test in Excel

 

Background

A statistical test is a way to evaluate the evidence the data provides against a hypothesis. This hypothesis is called the null hypothesis and is often referred to as H0. Under H0, data are generated by random processes. In other words, the controlled processes (the experimental manipulations for example) do not affect the data. Usually, H0 is a statement of equality (equality between averages or between variances or between a correlation coefficient and zero, for example).

Statistical Test Guide

XLSTAT offers a wide range of statistical tests. To help users choose the right test, a guide is provided based on your research question, variable types (categorical, binary, continuous), and data distribution. The guide includes:

• Null hypothesis formulations

• Practical examples

• Links to tutorials for both parametric and nonparametric tests

• Conditions for parametric test validity

• Nonparametric alternatives when applicable or necessary

For deeper theoretical insights, additional reference articles are available.

• What is a statistical test?

• What is a difference between paired and independent samples tests?

• What is the difference between a parametric and a nonparametric test?

• What is the difference between a two-tailed and a one-tailed test?

• How to interpret the output of a statistical test: the significance level alpha and the p-value

The displayed tests are the most commonly used tests in statistics. They are all available in XLSTAT. Please notice that the list is not exhaustive, and that many other situations / tests exist. Please scroll down to see the grid.

Test family	Question	Data	Null Hypothesis	Example	Parametric tests	Conditions of validity (parametric tests)	Non-parametric equivalents
Compare locations*	Compare an observed mean to a theoretical one	Measurements on one sample and 1 theoretical mean (1 number)	Observed mean = theoretical mean	Compare an observed pollution rate to a standard value	One-sample t-test	2	One sample Wilcoxon signed rank test
	Compare two observed means (independent samples)	Measurements on two samples	means* are identical	Compare hemoglobin concentration between two groups of patients	t-test on two independent samples	1 ; 3 ; 5	Mann-Whitney's test
	Test the equivalence between two samples	Measurements on two samples	means* are different	Check if the effect of medication A is the same as the effect of medication B on the concentration of a molecule in mice	Equivalence test (TOST)	1 ; 3 ; 5
	Compare several observed means (independent samples)	Measurements on several samples	means* are identical	Compare corn yields according to 4 different fertilizers	Analysis Of Variance ( ANOVA )	1 ; 3 ; 4 ; 6	Kruskal-Wallis test ; Mood's test
	Compare two observed means (dependent measurements)	Two series of quantitative measurements on the same units (before-after…)	means* are identical	Compare the mean hemoglobin concentration before and after a treatment has been applied on a group of patients	t-test on two paired samples	10	Wilcoxon's test
	Compare several observed means (dependent measurements)	Several series of quantitative measurements on the same units	means* are identical	Follow the concentration of a trace element in a group of plants across time	Repeated measures Analysis of Variance (ANOVA ) , mixed models	10 ; Sphericity	Friedman's test for complete block designs; Durbin, Skillings-Mack's test for incomplete block designs; Page test for cases where series scores are expected to increase or to decrease (across time for example)
Compare series of binary data	Compare series of binary data (dependent measurements)	Several series of binary measurements on the same units	Locations* are identical	A group of assessors (units) evaluate the presence/absence of an attribute in a group of products			McNemar's test (for 2 series); Cochran's Q test (for more than 2 series)
Compare variances	Compare 2 variances (could be used to test assumption 3)	Measurements on two samples	variance(1) = variance(2)	Compare the natural dispersion of size in 2 different varieties of a fruit	Fisher's test
	Compare several variances (could be used to test assumption 3)	Measurements on several samples	variance(1) = variance(2) = variance(n)	Compare the natural dispersion of size in several different varieties of a fruit	Levene's test
Compare proportions	Compare an observed proportion to a theoretical one	1 observed proportion with its associated sample size, one theoretical proportion	observed proportion = theoretical proportion	Compare the proportion of a female group to a proportion of 0.5 in a sample	Tests for one proportion (chi-square test)
	Compare observed proportions to each other	Sample size associated to every category	proportion(1) = proportion(2) = proportion(n)	Compare the proportions of different eye colors in a sample	Chi-square test
	Compare observed proportions to theoretical ones	Sample size and theoretical proportion associated to every category	observed proportions = theoretical proportions	Compare the proportions of observed F1xF1 cross-breeding frequencies to Mendelian frequencies (1/2, 1/4, 1/2)	Multinomial Goodness-Of-Fit test
Association tests	Test the association between two qualitative variables	Contingency table or two qualitative variables	variable 1 & variable 2 are independent	Is the presence of a trace element linked to the presence of another trace element?	Chi-square test on contengency table	1 ; 9	Exact Fisher test ;Monte Carlo method
	Test the association between two qualitative variables across several strata	Several contingency tables or two qualitative variables with a stratum identificator	variable 1 & variable 2 are independent	Is the presence of a trace element linked to the presence of another trace element? Assessed over several sites (strata)			Cochran-Mantel-Haenszel (CMH) test
	Test the association between two quantitative variables	Measurements of two quantitative variables on the same sample	variable 1 & variable 2 are independent	Does plant biomass change with soil Pb content?	Pearson's correlation test	7 ; 8	Spearman's correlation test
	Test the association between a binary variable and a quantitative one	Measurements on one binary variable and one quantitative variable	the two variables are independent	Is the concentration of a molecule in rats linked to the rats' sex (M/F)?	Biserial correlation test	Normality of the quantitative variable
	Test the association between a series of proportions and an ordinal variable	Contingency table or proportions and sample sizes	Proportions do not change according to the ordinal variable	Did birth rates change from year to year during the last decade?			Cochran-Armitage trend test
	Test the association between two tables of quantitative variables	Two tables of quantitative variables	Tables are independent	Does the evaluation of a series of products on a series of attributes change from a panel to another?	RV coefficient test
	Test the association between two proximity matrices	Two proximity matrices	Proximity matrices are independent	Is geographic distance between populations of bees correlated with genetic distance?			Mantel's test
Time series tests	Test the presence of a trend across time	One series of data sorted by date (time series)	There is no trend across time for the measured variable	Did stock value change across the last 10 years?			Mann-Kendall trend test
Tests on distributions	Compare an observed distribution to a theoretical one	Measurements of a quantitative variable on one sample; parameters of the theoretical distribution	The observed and the theoretical distributions are the same	Do the salaries of a company follow a normal distribution with mean 2500 and standard deviation 150?			Kolmogorov-Smirnov's test
	Compare two observed distributions	Measurements of a quantitative variable on two samples	The two samples follow the same distribution	Is the distribution of human weight the same in those two geographical regions?			Kolmogorov-Smirnov's test
	Test the normality of a series of measurements (could be used to test assumptions 2, 4, 7)	Measurements on one sample	The sample follows a normal distribution	Is the observed sample distribution significantly different from a normal distribution?	Normality tests
Tests for outliers	Test for outliers	Measurements on one sample	The sample does not contain an outlier (following the normal distribution)	Is this data point an outlier?	Dixon's test / Grubbs test		Boxplot (not a statistical test)

*Locations are means in parametric tests and mean ranks in nonparametric equivalents.

Conditions of Validity of Parametric Tests

Validity conditions we propose are rules of thumb. There are no precise rules in literature. We strongly advise to refer to your fields’ specific recommendations.

1. Measurements are independent

2. The population from which the sample was extracted follows a normal distribution (assumed or verified)

3. Samples have equal variances

4. The residuals are normally distributed (assumed or verified)

5. At least 20 individuals per sample, or normality of the population of every sample verified or assumed

6. At least 20 individuals in the whole experiment, or normality of residuals assumed or verified

7. Every variable is normally distributed

8. At least 20 individuals in the sample (recommended)

9. Theoretical frequencies should not be < 5 in all of the table cells

10. Differences between series should follow normal distributions

How to Run Statistical Tests in XLSTAT

In XLSTAT, you can access any of the above tests in any solution. Simply choose the appropriate test in the main ribbon (see below), select your data and get the results in a few clicks. If you do not have XLSTAT, download for free our 14-Day version to run your own test.

For each test, you may choose to display several outcomes such as descriptive statistics, detailed results, confidence intervals.

Furthermore, we offer a wide range of charts to help you visualize the results of statistical tests and quicker draw conclusions. See an example below for a two-sample t-test and z-test.

Additional Info:  Which Statistical Model Should You Choose