Statistical Analysis with Excel For Dummies

Excel offers a wide range of statistical analysis tools and functions you can use to add a single value or an array of values to your Excel worksheets. You can download even more tools with the Excel Analysis Toolpak.

>

>

Excel Statistical Analysis Functions

Excel shines at statistical analysis. It has a range of functions you can use to determine tendency and variability, comparatives, and correlations among other things. The following tables show which function to use to calculate a desired value and insert it into a call in an Excel worksheet.

Excel Central Tendency and Variability Functions

Function	What It Calculates	Function	What It Calculates
AVERAGE	Mean of a set of numbers	MEDIAN	Median of a set of numbers
AVERAGEIF	Mean of a set of numbers that meet a condition	VARP	Variance of a set of numbers considered to be a population
AVERAGEIFS	Mean of a set of numbers that meet one or more conditions	VAR	Variance of a set of numbers considered to be a sample
HARMEAN	Harmonic mean of a set of positive numbers	STDEVP	Standard deviation of a set of numbers considered to be a population
GEOMEAN	Geometric mean of a set of positive numbers	STDEV	Standard deviation of a set of numbers considered to be a sample
MODE	Mode of a set of numbers	STANDARDIZE	A standard score based on a given mean and standard deviation

Excel Relative Standing Functions

Function	What It Calculates
RANK	Rank of a number in a set of numbers
PERCENTRANK	Rank of a number expressed as a percent
PERCENTILE	The indicated percentile in a set of numbers
QUARTILE	The 1st, 2nd, 3rd, or 4th quartile of a set of numbers

Excel Correlation and Regression Functions

Function	What it Calculates
CORREL	Correlation coefficient between two sets of numbers
PEARSON	Same as CORREL. (Go figure!)
RSQ	Coefficient of determination between two sets of numbers (square of the correlation coefficient)
SLOPE	Slope of a regression line through two sets of numbers
INTERCEPT	Intercept of a regression line through two sets of numbers
STEYX	Standard error of estimate for a regression line through two sets of numbers

>

>

>

Excel Array Functions

Excel offers statistical analysis functions which you can use to find and insert an array of values into a selected array of cells in an Excel worksheet. The following table tells you which function calculates the data array you want:

Function	Calculates An Array Of …
FREQUENCY	Frequencies of values in a set of values
LINEST	Regression statistics based on linear regression through two or more sets of numbers
TREND	Numbers in a linear trend, based on known data points
LOGEST	Regression statistics based on curvilinear regression through two or more sets of numbers

>

>

>

Excel Data Analysis Toolpak Functions

You can load in the tools in Excel's Analysis ToolPak and add a whole range of statistical analysis tools. You can do F-tests, find covariance coefficients, as well as a lot of other tools. The following table shows the available tools and what each does:

Tool	What it Does	Tool	What it Does
Anova: Single Factor	Analysis of variance for two or more samples	Histogram	Tabulates individual and cumulative frequencies for values in the selected range of cells
Anova: Two Factor with Replication	Analysis of variance with two independent variables, and multiple observations in each combination of the levels of the variables.	Moving Average	In a sequence of values, calculates a prediction which is the average of a specified number of preceding values
Anova: Two Factor without Replication	Analysis of variance with two independent variables, and one observation in each combination of the levels of the variables.	Random Number Generation	Provides a specified amount of random numbers generated from one of seven possible distributions
Correlation	With more than two measurements on a sample of individuals, calculates a matrix of correlation coefficients for all possible pairs of the measurements	Rank and Percentile	Creates a table that shows the ordinal rank and the percentage rank of each value in a set of values
Covariance	With more than two measurements on a sample of individuals, calculates a matrix of covariances for all possible pairs of the measurements	Regression	Creates a report of the regression statistics based on linear regression through a set of data containing one dependent variable and one or more independent variables
Descriptive Statistics	Generates a report of central tendency, variability, and other characteristics of values in the selected range of cells	Sampling	Creates a sample from the values in a specified range of cells
Exponential Smoothing	In a sequence of values, calculates a prediction based on a preceding set of values, and on a prior prediction for those values	t-Test: Two Sample	Three t-test tools test the difference between two means. One assumes equal variances in the two samples. Another assumes unequal variances in the two samples. The third assumes matched samples.
F-Test Two Sample for Variances	Performs an F-test to compare two variances	z-Test: Two Sample for Means	Performs a two-sample z-test to compare two means when the variances are known

>

>
dummies

Source:http://www.dummies.com/how-to/content/statistical-analysis-with-excel-for-dummies-cheat-.html