Excel Data Analysis For Dummies Cheat Sheet
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.
|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:
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|
|>=||Greater than or equal to|
|<=||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.|