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
When you are working with sequenced values in a list, you'll often want to take some action based on the top X or bottom Y values in the list. This tip takes a look at how you can sum just these selective values.

   An Average that Excludes Zero Values
Excel allows you to use functions and formulas to analyze your data. One way you can analyze your data is to use the AVERAGE function to find out the average of a range of values; this concept is easy to grasp. What may not be as easy is how you get an average that excludes zero values within the range.

   Calculating a Geometric Standard Deviation
One of the areas in which Excel provides worksheet functions is in the arena of statistical analysis. You may want to calculate some statistics for which Excel doesn't provide built-in functions. This tip discusses one such statistic: a geometric standard deviation.

   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
Excel is used by many people to hold all sorts of data, not just numbers. If you have cells that include meaningful leading characters, you may want to count the cells that contain a specific character. Here's how to do it.

   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 "gotcha" 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
When you use Excel as a simple database program to store individual records, you may have a need to count the records which meet two criteria. There are a number of ways you can determine the desired count, as described in this tip.

   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.

   Median of Selected Numbers
Need to find a median value in a series of values? It's easy with the MEDIAN function. What isn't as easy is to derive the median from only a subset of the values based upon some criterion. Here's an easy way to approach the task.

   Using GEOMEAN with a Large List
When performing a statistical analysis on a large dataset, you may want to use GEOMEAN to figure out the geometric mean of the data. Doing so, however, could possibly generate an error, as described in this tip.

   Using the FORECAST Function
Excel provides a handy worksheet function that allows you to forecast values based upon a set of known values. This function, appropriately enough, is known as the FORECAST function and is described in this tip.