Dave Bruns

Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (...Read more

Dynamic ranges are also known as expanding ranges because they automatically expand and contract to accommodate new or deleted data. You can see a video demo of this approach here. This formula uses the OFFSET function to...Read more

In this example, the goal is to count rows using OR logic based on the criteria shown in column F. For example, in cell G5 we want to count rows where Color is "Blue" OR Pet is "Dog". This can be done with Boolean logic and the...Read more

First, this formula determines a "start date". The "first of month" is calculated with the DAY function here:

=B5-DAY(B5)+1

Then n * 7 days is added the first of the month, to get a start date n weeks from the first of the month. In the...Read more

First, this formula determines the first day of the next month *after* a given date. It does this my using EOMONTH to get the last day of the month, then adding one day:

=EOMONTH(B5,0)+1

Next, the formula calculates the number of days...Read more

The CHOOSE function does most of the work in this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index.

In this case, we are providing...Read more

This formula uses the MIN function to make a decision that might otherwise be handled with the IF function. Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for...Read more

In this example, the goal is to convert negative numbers in column B to zero and leave positive numbers unchanged. Essentially, we want to force negative numbers to zero.

With the MAX function

The MAX function provides an elegant solution...Read more

Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date.

TEXT(C5-B5,"h"" hrs...Read more