## Cheat Sheet

# 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.

## Some Excel Worksheet Functions

Excel can help you make all sorts of calculations. Here's a selection of Excel’s statistical worksheet functions. Each one returns a value into a selected cell.

Function | What it calculates |
---|---|

AVERAGE | Mean of a set of numbers |

AVERAGEIF | Mean of a set of numbers that meet a condition |

AVERAGEIFS | Mean of a set of numbers that meet one or more conditions |

HARMEAN | Harmonic mean of a set of positive numbers |

GEOMEAN | Geometric mean of a set of positive numbers |

MODE | Mode of a set of numbers |

MEDIAN | Median of a set of numbers |

VARP | Variance of a set of numbers considered to be a population |

VAR | Variance of a set of numbers considered to be a sample |

STDEVP | Standard deviation of a set of numbers considered to be a population |

STDEV | Standard deviation of a set of numbers considered to be a sample |

STANDARDIZE | A standard score based on a given mean and standard deviation |

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 1^{st}, 2^{nd}, 3^{rd}, or
4^{th} quartile of a set of numbers |

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

An array formula works with a series of data values rather than a one. Here are Excel's statistical array functions. Each one returns an array of values into a selected array of cells.

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 Tools

Excel's Analysis ToolPak gives you a whole range of new statistical analysis tools. These are the tools in Excel's Analysis ToolPak. You load them as add-ins.

Tool | What it Does |
---|---|

Anova: Single Factor | Analysis of variance for two or more samples |

Anova: Two Factor with Replication | Analysis of variance with two independent variables, and multiple observations in each combination of the levels of the variables. |

Anova: Two Factor without Replication | Analysis of variance with two independent variables, and one observation in each combination of the levels of the variables. |

Correlation | With more than two measurements on a sample of individuals, calculates a matrix of correlation coefficients for all possible pairs of the measurements |

Covariance | With more than two measurements on a sample of individuals, calculates a matrix of covariances for all possible pairs of the measurements |

Descriptive Statistics | Generates a report of central tendency, variability, and other characteristics of values in the selected 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 |

F-Test Two Sample for Variances | Performs an F-test to compare two variances |

Histogram | Tabulates individual and cumulative frequencies for values in the selected range of cells |

Moving Average | In a sequence of values, calculates a prediction which is the average of a specified number of preceding values |

Random Number Generation | Provides a specified amount of random numbers generated from one of seven possible distributions |

Rank and Percentile | Creates a table that shows the ordinal rank and the percentage rank of each value in a set of values |

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 |

Sampling | Creates a sample from the values in a specified range of cells |