Search results

In this example, the goal is to get the maximum quiz score (i.e. the best quiz score) for each person listed in column B from the five quiz scores that appear in columns C through G. This is a job for the MAX function or the LARGE function, as explained below.

MAX function

The...Read more

The coefficient of variation measures the relative variability of data with respect to the mean. It represents a ratio of the standard deviation to the mean and can be a useful way to compare data series when means are different. It is sometimes called relative standard deviation (RSD).

...Read more

In this example, the goal is to get the minimum quiz score (i.e. the lowest quiz score) for each person listed in column B from the five quiz scores that appear in columns C through G. This is a job for the MIN function or the SMALL function, as explained below.

MIN function

...Read more

This formula is more complicated than a similar formula that uses FREQUENCY to count unique numeric values because FREQUENCY doesn't normally work with non-numeric values. As a result, a large part of the formula simply transforms...Read more

In this example the goal is to sum the numbers in the range F5:F16 when cells in the range C5:C15 contain "Red". To solve this problem, you can use either the SUMIFS function or the SUMIF function. The SUMIF...Read more

In this example, the goal is to sum the Quantity in C5:C16 when the Item in B5:B16 ends with "small". To solve this problem, you can use either the SUMIFS function or the SUMIF function with the asterisk (*)...Read more

In this example, the goal is to sum the Price in column C when the Product in column B begins with "sha". To solve this problem, you can use either the SUMIF function or the SUMIFS function with the asterisk (*) wildcard, as explained below.

Wildcards

Certain Excel functions...Read more

In this example, the goal is to sum amounts by weekday. In other words, we want to sum amounts by Monday, Tuesday, Wednesday, and so on. Column B contains valid Excel dates formatted with a custom number format explained below. For convenience, all source data...Read more

There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function, but SUBTOTAL only works with vertical ranges. As a result, the approach described in this example is a workaround based on a helper...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

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 find the longest text string in the range C5:C16 that belongs to the group entered in cell F5. The group is a variable that may be changed at any time. At the core, this is a lookup problem, and the challenge is that the value we need to look up (the string...Read more

In this example, the goal is to sum the amounts in the table using the "Start" and "End" values in columns F and G. For convenience, all data is in an Excel Table called data, which means we can use the...Read more

In the example shown, we have a list of amounts by month. The goal is to dynamically sum values through a given number of months using a variable n in cell E5. Since month names are just text, and months are listed in order, the key requirement is to sum amounts...Read more

In this example, the goal is to sum amounts C5:C16 when the date in B5:B16 is greater than the date provided in cell E5. A good way to solve this problem is with the SUMIFS function.

Note: for SUMIFS to work correctly...Read more

In this example, the goal is to sum the quantities in column C by the codes in column E in a case-sensitive manner. The SUMIF function and the SUMIFS function are both good options for counting text values, and...Read more

In this example, the goal is to create a formula that will sum values in a range that may contain errors. A common problem in Excel is that errors in data show up in the results of other formulas. For example, in the worksheet shown, the SUM function is used to sum the ...Read more

In this example, the goal is to sum values in the range D5:D16 when they are greater than the value entered in cell F5. This problem can be easily solved with the SUMIF function or the SUMIFS function. The main challenge in this problem is the syntax needed for criteria that uses the...Read more

In this example, the goal is to sum values in the range D5:D16 when they are less than the value entered in cell F5. This problem can be easily solved with the SUMIF function or the SUMIFS function. The main challenge in this problem is the syntax needed for cell F5 in the criteria,...Read more

In this example, the goal is to sum amounts in column C when the date in column B is between two given dates. The start date is provided in cell E5, and the end date is provided in cell F5. The date range should be inclusive - both the start date and end date should be included in the final...Read more

In this example, the goal is to average the last n columns in a set of data, where n is a variable entered in cell K5 that can be changed at any time. Since more data may be added, a key requirement is to average amounts by position....Read more

In this example, the goal is to sum Total when the corresponding Color is either "Red" or "Blue". For convenience, all data is in an Excel Table named data. This is a tricky problem, because the solution is not obvious. The go-to function for...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 calculate an average of the top 3 quiz scores for each name listed in column B. For reference, column H has a formula that calculates an average of all 4 scores. This is a slightly tricky problem, because it's not obvious how to limit the scores included in the...Read more

This formula uses two named ranges: things, and results. If you are porting this formula directly, be sure to use named ranges with the same names (defined based on your data). If you don't want to use named ranges, use...Read more