Dave Bruns

In this example, the context is that a certain number of items are needed and the items are only available in bundles of a set size. The goal is to calculate the number of bundles needed based on the items in each bundle, and the number of items needed. For example:

• If you
...Read more

The DATE function builds dates from separate year, month, and day values. One of its tricks is the ability to roll forward to correct dates when given days and months that are "out of range". For example, DATE returns April 9, 2016, with the following arguments:

...Read more

The foundation of this formula is the MID function, which extracts a specific number of characters from text, starting at a specific location. To figure out where to start extracting text, we use this expression:

SEARCH("(",B5)+1

This...Read more

The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which...Read more

Working from the inside out, the expression C5:G5<>"" returns an array of true and false values:

{FALSE,TRUE,FALSE,FALSE,FALSE}

The number 1 is divided by this array, which creates a new array composed of either 1's or #DIV/0! errors...Read more

In this example, the goal is to sum amounts by weekday. In other words, we want to sum amounts by Monday, Tuesday, Wednesday, and so on. Column B contains valid Excel dates formatted with a custom number format explained below. For convenience, all source data...Read more

You might wonder why we aren't using COUNTIF or COUNTIFS. These functions seem like the obvious solution. However, without adding a helper column that contains a weekday value, there is no way to create criteria...Read more

Working from the inside out, the MATCH function matches the range against itself. That is, we give the MATCH function the same range for lookup value and lookup array (B5:F5).

Because the lookup value contains more than one value (an array), MATCH returns an array of results, where each...Read more