How to Use the Data Analysis Toolpak in Excel

Anova (Analysis of Variance): Single Factor

This tool determines if there is a relationship between two datasets by performing a simple analysis of variance.

Anova (Analysis of Variance): Two Factor with Replication

This tool determines if there is a relationship between two datasets by performing an analysis of variance when each data set has more than one observable data point.

Anova (Analysis of Variance): Two-Factor without Replication

This tool determines if there is a relationship between two data sets by performing an analysis of variance. There is only a single observable data point for each pair.

Correlation

Tells you how strongly two variables are related to each other. 

Covariance

The Covariance analysis tool calculates the average of the product of deviations of values from the means of each data set.

Descriptive Statistics

Generates a report of univariate statistics for the selected data. Statistics generated include: Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, Count, Largest, Smallest and Confidence Level.

Exponential Smoothing

Smooths out irregularities (peaks and valleys) in data, to easily recognize trends . More recent data is weighted more heavily.

F-Test Two Sample for Variances

This analysis tool compares the variances between two groups of data. 

Fourier Analysis

This tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data. The Fourier Analysis tool also supports inverse transformations, where the inverse of transformed data returns the original data.

Histogram

The Histogram analysis tool counts occurrences in each of several data bins. It calculates individual and cumulative frequencies for a cell range of data and data bins. The output is a table and column chart by the frequency of occurrences.

Moving Average

Calculates a moving average to allow you to smooth out a data series that contains peaks and outliers. Used for forecasting trends in sales, inventory, call volume, etc.

Random Number Generation

Creates a number of several types of random numbers including Uniform, Normal, Bernoulli, Poisson, Patterned and Discrete. More flexible than the RAND and RANDBETWEEN functions.

Rank and Percentile

Creates a table that ranks numbers from highest to lowest and provides a percentile value of each number relative to the other numbers within the data set.

Regression

Uses the function LINEST to analyze how a single dependent variable is affected by the values of one or more independent variables. Creates a table of statistics that result from least-squares regression.

Sampling

Samples a population randomly or periodically, as desired.

t-Test: Paired Two Sample for Means

Paired two-sample student's T-Test. Each Two-Sample t-Test analysis tool tests for equality of the population means that underlie each sample. The paired two-sample form of the t-Test is used when there is a natural pairing of observations in the samples — for example, when a sample group is tested twice, before and after an experiment. There is no assumption that the variances of both populations are equal.

T-Test: Two Sample assuming equal Variances

This analysis tool performs a two-sample student's t-Test. This t-Test form is based on the assumption that the two paired data sets came from distributions with the same variances. It is also known as a “homoscedastic t-Test”. This t-Test can be used to determine if the two samples are likely to have come from distributions with equal population means.

T-Test: Two Sample assuming unequal Variances

This t-Test form assumes that the two datasets are from distributions where the variances are unequal. This is called a “heteroscedastic t-Test”.

Z-Test: Two Sample for Means

The Two Sample for Means analysis tool performs a two-sample z-Test for means with known variances. This analysis tool is used to test the null hypothesis that there is no difference between two population means against either one-sided or two-sided alternative hypotheses. If mean variances are not known, use the Z.TEST function instead.