Search results

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

The goal is to calculate the correct invoice status ("OK", "Paid", or "Overdue") using the following rules:

1. If there is an "x" in the "Paid" column, return "Paid".
2. If there is not an "x" in the "Paid" column, and if the age is less than 31 days, return "OK"
...Read more

In this example, the goal is to subtotal invoices by age, where age represents the number of days since the invoice was issued. This problem can be solved with the SUMIFS function and the COUNTIFS function, as explained below. For convenience, age (E5:E16) and amount...Read more

The goal is to do something if a cell equals a given value. The most common way to solve this problem is with the IF function.

IF function

The IF function runs a logical test and returns one value for a TRUE result, and another value for a...Read more

This formula uses the "greater than or equal to" operator with text, something you might not have tried before. When Excel compares text, it decides which value is "greater" than another based on rules that follow the ASCII specification.

Inside COUNTIF, the...Read more

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include".

In the right SUMPRODUCT, SEARCH looks for all strings in the named range "exclude".

In both...Read more

The goal is to "flag" records that are neither "Red" nor "Green". More specifically, we want to check the color in column B, and leave an "x" in rows where the color is NOT "Red" OR "Green". If the color is "Red" OR "Green", we want to display nothing.

IF function...Read more

The table contains basic order information, with columns for Date, Product, Name, and Amount. The Helper column is used to create a special lookup value, as explained below. The goal is to retrieve the nth matching record in a table for a specific product, which is entered in cell I4. For...Read more

The goal is to retrieve the nth matching record in a table when targeting a specific product. For example, if the value in cell H4 is "A", the formula in H7 should return the name "John", since this is the first name in the table associated with product "A". In the same way, the...Read more

The goal is to list the working days between a start date and an end date. In the simplest form, this means we want to list dates that are Monday, Tuesday, Wednesday, Thursday, or Friday, but exclude dates that are Saturday or Sunday. In addition, we need an option to exclude a list of...Read more

The core of this formula is based on another formula that calculates the "current row" in a range:

=ROW()-ROW(rng.firstcell)+1

In brief, we get the current row in the workbook, then subtract the...Read more

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 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

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

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

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

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 look up and retrieve employee information in a table that contains unique id values in the first column. The VLOOKUP function is straightforward to use with data in this format, but you can easily use the XLOOKUP function as well. See below for a detailed explanation of both...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

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

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

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