Dave Bruns

The key to this formula is the array or TRUE and FALSE values constructed with this expression:

LEFT(code,1)<>"N"

Here, each value in the named range "code" is evaluated with the logical test "...Read more

For this example, we want to calculate the interest paid during each year in a 5-year loan of $30,000 with an interest rate of 5%. To do this, we set up CUMIPMT like this:

• rate - The interest rate per period. We divide 5% by 12 because 5% represents annual
...Read more

This formula depends on a TRUE or FALSE result from a logical test, where FALSE represents the value you are looking for. In the example, the logical test is data="red", entered as the lookup_array argument in the MATCH function:

=MATCH(FALSE,data="red",0...Read more

In this example the goal is to parse feet and inches out in the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The formula can be divided into two parts. In...Read more

In this example, the goal is to create a formula that converts a numeric value in inches to a format that displays inches and feet, as seen in the table below:

In this example, we want to apply three different colors, depending on how much the original date varies from the current date:

1. Green if the variance is less than 3 days
2. Yellow if the variance is between 3 and 10 days
3. Red if the variance is greater than 10
...Read more

The goal in this example is to count cells in a range that do not contain a given number of strings. The cells to evaluate are in the named range data (B5:B14) and the strings to exclude are listed in the named range exclude (...Read more

In the example shown, we have a list of amounts by month. The goal is to dynamically sum values through a given number of months using a variable n in cell E5. Since month names are just text, and months are listed in order, the key requirement is to sum amounts...Read more

At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with COUNTA like this:

...Read more