Simple Statistics in Excel Data Analysis - dummies

Simple Statistics in Excel Data Analysis

By Stephen L. Nelson, E. C. Nelson

Excel provides several handy, easy-to-use tools for analyzing the information that you store in a table, including some quick-and-dirty statistical tools. Look at the following simple table.

A simple grocery list.
A simple grocery list.

One of the slickest and quickest tools that Excel provides is the ability to effortlessly calculate the sum, average, count, minimum, and maximum of values in a selected range. For example, if you select the range C2 to C10, Excel calculates an average, counts the values, and even sums the quantities, displaying this useful information in the status bar. Note the information on the status bar (the lower edge of the workbook):

Average: 1.555555556 Count: 9 Sum: 14

This indicates that the average order quantity is (roughly) 1.5, that you’re shopping for 9 different items, and that the grocery list includes 14 items: Two loaves of bread, one can of coffee, one tomato, one box of tea, and so on.

The big question here, of course, is whether, with 9 different products but a total count of 14 items, you’ll be able to go through the express checkout line. But that information is irrelevant to the discussion.

You aren’t limited, however, to simply calculating averages, counting entries, and summing values in your list. You can also calculate other statistical measures.

To perform some other statistical calculation of the selected range list, right-click the status bar. When you do, Excel displays a pop-up Status Bar Configuration menu. Near the bottom of that menu bar, Excel provides six statistical measures that you can add to or remove from the Status Bar: Average, Count, Numerical Count, Minimum, Maximum, and Sum.

In this table, each of these statistical measures is described briefly, but you can probably guess what they do. Note that if a statistical measure is displayed on the Status Bar, Excel places a check mark in front of the measure on the Status Bar Confirmation menu. To remove the statistical measure, select the measure.

Quick Statistical Measures Available on the Status Bar
Option What It Does
Average Calculates the average of the cells in a selected range that
hold values or formulas.
Count Tallies the cells that hold labels, values, or formulas. In
other words, use this statistical measure when you want to count
the number of cells that are not empty.
Numerical Count Tallies the number of cells in a selected range that hold
values or formulas.
Minimum Finds the smallest value in the selected range.
Maximum Finds the largest value in the selected range.
Sum Adds up the values in the selected range.

No kidding, these simple statistical measures are often all you need to gain wonderful insights into data that you collect and store in an Excel table. By using the example of a simple, artificial grocery list, the power of these quick statistical measures doesn’t seem all that earthshaking. But with real data, these measures often produce wonderful insights.