This formula uses the named range "list" which is the range B5:B11.
The core of this formula is the SMALL function, which simply returns the nth smallest value in a list of values that correspond to row numbers. The...

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,...

The SMALL function is fully automatic — you just need to supply a range and an integer for"nth" to specify the ranked value you want. The official names for these arguments are "array" and "k".
Note: you can also use...

At the core, this formula uses the COUNTIF function and an expanding range to count occurrences of values. The expanding reference is used so that COUNTIFS returns a running count of occurrences, instead of a total...

In its simplest form, SMALL will return the "nth smallest" value in a range. For example:
=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
However, if you supply an array...

The small function can retrieve the smallest values from data based on rank. For example:
=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
In this case, the rank simply...

In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2.
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and...

Note: this formula uses two named ranges: "names" refers to C4:C11, and "groups" refers to B4:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...

In its simplest form, SMALL returns the "Nth smallest" value in a range with this construction:
=SMALL (range,N)
So, for example:
=SMALL (C5:C12,2)
will return the 2nd smallest value in the range C5:C12, which is...

Note: this formula uses two named ranges: "names" refers to C5:C11, and "groups" refers to B5:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...

Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:
$C3<=SMALL...

At the core, this formula is simply an INDEX formula that retrieves the value in an array at a given position. The value for n is supplied in column H, and all the "heavy" work that the formula does is to figure out...

The SMALL function is fully automatic — you just need to supply a range and an integer for"nth" to specify the ranked value you want.
The problem in this case is that we don't want SMALL to operate on every value in...