Cheat Sheet

Excel Data Analysis For Dummies Cheat Sheet

From Excel Data Analysis For Dummies, 2nd Edition by Stephen L. Nelson, E. C. Nelson

A great deal of the power of Excel data analysis is in its functions, so here's a round-up of key Excel database functions. Here's also a quick guide on constructing Boolean expressions for comparing values. You'll also find an explanation of some quick statistic measures you can perform on a range of data in Excel.

Excel Database Functions for Statistical Calculations

Excel provides a valuable set of handy-to-use database functions for making statistical calculations using information from lists. These functions are incredibly helpful for analyzing your data. The following table includes many of the most useful database functions and a description of what they do. After the table is an explanation of the standard three-argument syntax used by all of these functions.

Function Description
DAVERAGE Calculates arithmetic mean
DCOUNT Counts the number of cells with values
DCOUNTA Counts the number of cells that aren’t empty
DGET Returns a value from a database list
DMAX Finds the largest value in a list
DMIN Finds the smallest value in a list
DPRODUCT Calculates the product of values matching criteria
DSTDEV Calculates the standard deviation of a sample
DSTDEVP Calculates the standard deviation of a population
DSUM Calculates the sum of values matching criteria
DVAR Calculates the variance of a sample
DVARP Calculates the variance of a population

All these database functions use a standard three-argument syntax. For example, the DAVERAGE function looks like this:

=DAVERAGE(database,field,criteria)

where database is a range reference to the Excel list that holds the value you want to examine, field tells Excel which column in the database to examine, and criteria is a range reference that identifies the fields and values used to define your selection criteria. The field argument can be a cell reference holding the field name, the field name enclosed in quotation marks, or a number that identifies the column (1 for the first column, 2 for the second column, and so on).

Using Boolean Expressions to Compare Values in Excel

To construct a Boolean expression, such as when you filter criteria, use a comparison operator and then a value used in the comparison: (>5, for example). These are Boolean comparisons that compare two values and evaluate whether the statement is true or false.

Comparison Operator What It Means
= Equals
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to

Quick Statistical Measures in Excel

To perform a statistical calculation of a selected range list, right-click the Excel status bar. When you do, Excel displays a pop-up menu that includes numerous statistical measures that you can make on the selected range. Several of the most useful are detailed in the following table.

Statistical Measures Option What It Does
Average Finds the mean of values in selected range.
Count Tallies the cells that hold labels, values, or formulas. Use this when you want to count the number of cells that aren’t empty.
Numerical Count Tallies the number of cells in a selected range that hold values or formulas.
Maximum Finds the largest value in the selected range.
Minimum Finds the smallest value in the selected range.
Sum Adds the values in the selected range.
blog comments powered by Disqus
Advertisement

Inside Dummies.com