Dave Bruns

Note: the core idea of this formula is adapted from an example in Mike Girvin's excellent book Control+Shift+Enter.

The example shown uses several...Read more

This formula first generates a rank value using an expression based on COUNTIF:

=COUNTIF(data,"<="...Read more

This formula uses the "greater than or equal to" operator with text, something you might not have tried before. When Excel compares text, it decides which value is "greater" than another based on rules that follow the ASCII specification.

Inside COUNTIF, the...Read more

This formula depends on two helper columns. The first helper column holds random values created with the RAND() function. The formula in C5, copied down is:

=RAND()

The RAND function generates a random...Read more

This formula relies on a helper column that already contains a sequential list of numbers to represent an established sort order. The numbers in the helper column are independent of the operation of this formula. As long as the sequence is continuous, it can...Read more

Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here.

The core of this formula is the RANK function, which is used to...Read more

The SMALL function is meant to extract the "nth" smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value, supply 2, and so on.

=SMALL(data,1) //...Read more

At the core, we are using the INDEX function to retrieve the value at a given row or column number like this:

=INDEX(C5:G9,row,column)

The range C5:C9 defines the matrix values. What's left is to figure out the correct row and column...Read more

The goal of this formula is to verify whether the values in column B follow the format xx-xxxx-xxx, where "x" represents any single character. The IF function doesn't support wildcards directly, so we can't use IF by itself. Instead, we can combine the IF function with the COUNTIF function...Read more