Dave Bruns

In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...Read more

Compound interest is a financial concept that describes how an initial investment grows over time, taking into account not only the interest earned on the initial amount but also the interest earned on the interest itself. Compound interest allows your money to grow exponentially, which...Read more

The general formula for simple interest is:

interest=principal*rate*term

So, using cell references, we have:

=C5*C7*C6
=1000*10*0.05
=500
Read more

This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.

Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100...Read more

The DATEDIF function can handle a variety of "date difference" calculations to calculate the difference between two dates in years, months, and days. DATEDIF takes 3 arguments: start date, end_date, and "unit", which controls which result is returned.

In this case, we want days...Read more

This formula relies on the AND function to test two conditions at the same time:

1. Count of words from named range inc is >0
2. Count of words from named range exc is =0

If both conditions are TRUE, the formula returns TRUE...Read more

Working from the inside-out, the WEEKDAY function takes a date and returns a number between 1 and 7. With default settings, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday.

The CHOOSE function simply maps numbers to values. The first argument is the number to...Read more

In this example, the goal is to sum the value of orders that have a status of "Complete" or "Pending". This is a slightly tricky problem in Excel because the SUMIFS function is designed for conditional sums based on multiple criteria, but all criteria must be TRUE in order for a value to be...Read more

In this example, the goal is to calculate a count for the longest winning streak in a set of data. In the worksheet shown, wins ("W") and losses ("L") are recorded in column C, so this means we want to count the longest consecutive series of W's. Although we are specifically counting the...Read more