Statistical Functions

Tips, Tricks, and Answers

The following articles are available for the 'Statistical Functions' topic. Click the article's title (shown in bold) to see the associated article.

Adding Up Tops and Bottoms
If you need to know the sum of the top X items in a list or the bottom X items, you can use the LARGE and SMALL functions. This tip shows how easy it is to use the functions in a formula.

Cell and Name References in COUNTIF
The second parameter of the COUNTIF function is used to specify the criteria to be used when determining what should be counted. If you want that criteria to include a cell reference, it can be confusing to make the reference work properly.

Counting Cells with Specific Characters
If you've got a long list of data and you want to count the number of cells in the range that begin with a specific character, putting together the proper formula can be a bit confusing. You can get just what you need by using the COUNTIF function, as illustrated in this tip.

Counting the Number of Blank Cells
If you need to count the number of blank cells in a range, the function to use is COUNTBLANK. This tip discusses the function, along with a "gottcha" that you should be aware of.

Counting with Formulas
When you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip explains, through an example, how you can use the function to do the desired counting.

Counting with Two Criteria
Want to count the records in a data table that match two criteria? It's not as easy or straightforward as you might hope. This tip explores several different ways you can get the desired result.

Counting within Criteria
You can use the COUNTIF function to determine how many cells in a range fit your criteria. One criterion is easy; using multiple criteria is harder. Here's some ideas on how to do it.

Finding the Lowest Numbers
Need to find the lowest numbers in a range of values? It's easy to do using the SMALL worksheet function, or you can use a macro for more demanding needs.

Phantom Counts
Perplexed by the ways in which the COUNT and COUNTA functions seem to count? This tip explains the differences and the "gottchas" you need to watch for.

Using GEOMEAN With a Large List
GEOMEAN allows you to calculate the geometric mean of a series of values. This is easy with a limited number of values in your series, but can be a challenge with larger series. This tip discusses how to handle those large lists.

Using the FORECAST Function
The FORECAST worksheet function can be used to do an extrapolation of future data points based on a linear regression of existing data. This tip explains how to use the function.