This formula uses two expressions in a single array inside the SUMPRODUCT function.
The first expression tests every holiday date to see if it's greater than or equal to the start date in F5:
(B4:B12>=F5)
This returns an array of...Read more
In this example, the goal is to count non-blank dates in column D by group. All data is an Excel Table named data in the range B5:D16. This problem can be solved with the COUNTIFS function, as...Read more
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 moreIn 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 moreAt 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