Dave Bruns

In this example, the goal is to count orders (rows)  where the state is Texas ("TX"), the amount is greater than $100, and the month is March. In this case, we can't use COUNTIFS, because COUNTIFS is a...Read more

In this example the goal is to count products (rows) where sales have increased and sales have decreased, where previous sales are in column C (Previous) and current sales are in column D (Current). In this case, we can't use...Read more

In this example, the goal is to return a TRUE or FALSE result for each value in column B, based on whether it appears in the range E5:E9, which has been named "things" for convenience.

Context

Imagine you have a list of values in the range B5:B16 and you want to check each...Read more

The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text.

If you need to perform more than one replacement at the same time, you'll need to nest multiple SUBSTITUTE...Read more

Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015.

Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the...Read more

In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the...Read more

This formula uses the NETWORKDAYS function calculate total working days between two dates, taking into account weekends and (optionally) holidays. Holidays, if provided, must be a range of valid Excel dates. Once total work days are known, they are simply multiplied by a fixed number of hours...Read more

An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price. The best way to solve this problem...Read more

In this formula, the goal is to return the numeric position of the most expensive property in the list. The formula in cell I5 is:

=MATCH(MAX(C3:C11),C3:C11,0)

The MAX function extracts the maximum value from the range C3:C11. In this case...Read more