Search results

In this example, the goal is to highlight dates that occur on weekends. In other words, we want to highlight dates that land on either Saturday or Sunday. This problem can be easily solved by applying conditional formatting with a formula based on the WEEKDAY function together with the OR...Read more

In this example, the goal is to calculate a weighted average of scores for each name in the table using the weights that appear in the named range weights (I5:K5) and the scores in columns C through E. A weighted average (also called a weighted mean)...Read more

In this example, the goal is to create a formula that will return "Pass" or "Fail" depending on whether a student has a passing score in at least 4 out of 6 subjects. This problem can be easily solved with a formula based on the COUNTIF function...Read more

In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:

=IF(ISBLANK(C5),"",COUNTA($C$5:C5))

The IF function first checks if cell C5...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

The goal is to calculate a tax amount with both fixed and variable components according to the following logic:

1. If the amount is less than $1000, only the base tax applies.
2. If the amount is $1000 or greater, the result is the base tax + 15% * the amount over $
...Read more

Ever needed to calculate someone's retirement date? Or figure out when a 30-year mortgage will end? Or maybe you're setting a contract expiration date? In each case, you need a way to add years to a date.

Here's something that might surprise...Read more

This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data:

FILTER(B5:B16,B5:B16<>"")

The <> symbol is a...Read more

This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove limit data to values associated with group A only:

FILTER(B5:B16,C5:C16=E4)

Notice we are...Read more

When conditional formatting is applied with a formula, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the active cell when the rule is created is assumed to be cell E5, with the range E5:E14 selected.

As the formula is...Read more

FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars. The LEFT function then extracts characters starting at the left side of the text, up to (position - 1...Read more

The goal of this example is to count rows in the data where the date joined falls between start and end dates (inclusive) and the age also falls into the age ranges seen in column G. The formula is complicated somewhat by the fact that the age range labels are actually text, so we need to...Read more

The goal is to calculate a tax of 6% on amounts up to 20,000 and a tax of 10% on amounts of 20,000 or greater. This problem illustrates how to use the IF function to return different calculations. At the core, this formula uses a single IF function. The...Read more

This example uses the UNIQUE function together with the FILTER function. The FILTER function removes data that does not meet required criteria, and the UNIQUE function further limits results to unique values only.

Working from the inside out, the FILTER function is used to collect source...Read more

In this example, we need to construct logic that filters data to include:

account begins with "x" AND region is "east", and month is NOT April.

The filtering logic of this formula (the include argument) is created by chaining together three expressions that use...Read more

In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (n) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:

...Read more

A common situation in Excel is to use the SORT function to sort results returned by the FILTER function. However, a formula based on the FILTER and SORT may return an error when no data is returned. In this...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

In this example, the goal is to list the next n upcoming birthdays from a larger set of 25 birthdays based on the current date. The set of birthdays are in an Excel Table named data in the range B5:C29. In the example shown, we are using 7 for...Read more

In this example, the goal is to repeat a range of values. This can be done in various ways in Excel, but I think the CHOOSEROWS/ CHOOSECOLS functions are the easiest way to retrieve values from the range for now. Both functions work natively with two-dimensional ranges and can accept a...Read more

The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get rid of the extra space.

In this snippet, the delimiter (delim...Read more

In this example, criteria are entered in the range F5:H6. The logic of the formula is:

item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)

The filtering logic of this formula (the include argument) is applied with the...Read more