Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.
The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is...Read more
This formula counts how many values are not in range of a fixed tolerance. The variation of each value is calculated with this:
ABS(data-target)
Because the named range "data" contains 10 values,...Read more
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 moreThis 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 moreAt 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