Search results

This formula relies on a specific behavior of INDEX — although it seems that INDEX returns the value at a particular location, it actually returns a reference to the location. In most formulas, you wouldn't notice the difference – Excel simply evaluates the reference and...Read more

In this example, the goal is to get the last value in column B, even when data may contain empty cells. A secondary goal is to get the corresponding value in column C. This is useful for analyzing datasets where the most recent or last entry is significant. In the current version of...Read more

In this example, the goal is to get the maximum value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you...Read more

In this example, we want to apply three different colors, depending on how much the original date varies from the current date:

1. Green if the variance is less than 3 days
2. Yellow if the variance is between 3 and 10 days
3. Red if the variance is greater than 10
...Read more

In the example shown, we have a set of order data that includes Date, Product, Name, and Amount. The data is sorted by date in ascending order. The goal is to look up the latest order for a given person by Name. In other words, we want the last match by name. The challenge is that Excel...Read more

Although FILTER is more commonly used to filter rows, you can also filter columns, the trick is to supply an array with the same number of columns as the source data. In this example, we construct the array we need with boolean logic, also called Boolean...Read more

At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range data C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of all values in the range:

...Read more

In this example, the goal is to calculate an average for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. For convenience only, data (C5:E16) and group (B5:B16) are named ranges. In...Read more

In this example, the goal is to extract a set of records that match a partial text string. To keep things simple, we are only matching one field in the data, the last name ("Last"). The core operation of this formula comes from the...Read more

In this example, the goal is to count ages in column C according to the brackets defined in columns E and F. All data is in an Excel Table named data defined in the range B5:C16. A simple way to solve this problem is with the COUNTIFS...Read more

In the example shown, we want to mark or "flag" records where the color is red OR green. In other words, we want to check the color in column B, and then leave a marker (x) if we find the word "red" or "green". In D6, the formula is:

=IF(OR(B6="red",B6="...Read more

In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function, which is designed to calculate...Read more

In this example, the goal is to calculate a sum for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. In other words, we want to perform a "sum if" with a data range that contains three columns. For convenience only, data (C5:E16) and...Read more

The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas that return the #NUM error. In general, the fixing the #NUM! error is a matter...Read more

In this example, the goal is to create a list of pay dates that follow a biweekly schedule. A biweekly pay schedule means employees are paid every two weeks on a given day of the week. Each pay period is 14 days, and there are usually 26 pay dates per year, though occasionally 27 depending...Read more

Note: this formula depends on values in the Time column (C), and values in the Start and End columns (F, G) being valid Excel times.

The data is in an Excel table called table. By...Read more

The core of this formula is a basic lookup with INDEX:

=INDEX(list,row)

In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list.

The hard work is figuring out the ROW number 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

The LARGE function is an easy way to get the nth largest value in a range:

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

In this example, we can use the LARGE function to get a highest score...Read more

The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there is no row number information in the data.

Working from the inside...Read more

Dates in Excel are serial numbers that start on 1/1/1900, which is represented by the number 1. In the example shown, the formula in cell D6 simply subtracts the numeric value of 1/1/1999 (36161) from the numeric value of 1/1/2000 (36526) to get a result...Read more