Dave Bruns

This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10).

The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function:

COUNTIF(range1,B5) // count in range1
COUNTIF(...Read more

In this example, the goal is to calculate the average call time (duration in minutes) for each month listed in column G using the dates in column B and the durations in column E. The article below explains two approaches. The first formula is based on the...Read more

In this example, the goal is to build a "cash denomination calculator." A cash denomination calculator is a tool for counting and verifying cash amounts. It can calculate the denominations needed to achieve a certain cash value. It can also perform the reverse calculation and determine the...Read more

If you have an annual interest rate, and a starting balance you can calculate interest with:

=balance * rate

and the ending balance with:

=balance+(balance*rate)

So, for each period...Read more

In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function, which is designed to calculate...Read more

Working from the inside out, the MIN function is used to find the lowest bid in the range C5:C9:

MIN(C5:C9) // returns 99500

The result, 99500, is fed into the MATCH function as the lookup value:

MATCH(...Read more

At the core, this formula looks for a line delimiter ("delim") and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.

Note: In older versions of Excel on a Mac, use CHAR(13) instead of CHAR(10). The CHAR function...Read more

In this example, the goal is to strip (i.e., remove) non-numeric characters from a text string with a formula. Until 2024, this was a tricky problem in Excel, partly because Excel did not support regex (Regular Expressions), and partly because there...Read more