Search results

In the example shown, the formula in B11 is:

=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")

Working from the inside out, the work of setting up a variable range is done by the OFFSET function here:

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

Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:

$B3=$E$5

The second test is more complex:...Read more

In this example, the goal is to highlight rows in the data shown when the date is a specific day of week. The target day of week is a variable selected with a dropdown menu in cell F5, which contains abbreviated day names. This problem can be easily solved by applying conditional formatting...Read more

In this example, the goal is to count rows where the value in column one is "A" or "B" and the value in column two is "X", "Y", or "Z". In the worksheet shown, we are using array constants to hold the values of interest, but the article also shows how to...Read more

Note: the lookup_value of 2 is deliberately larger than any values in the lookup_vector, following the concept of bignum.

Working from the inside out, the expression:

(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy...Read more

The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to "fall back" to the previous value. This formula exploits this behavior by creating an array that contains only 1s and...Read more

The INDIRECT function tries to evaluate text as a worksheet reference. This makes it possible to build formulas that assemble a reference as text using concatenation, and use the resulting text as a valid reference.

In this example, we have Sheet...Read more

This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the COUNTA function as an array like this:

...Read more

In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened and stop counting on the date a ticket was closed. If there is no closed date, the ticket is still open. Because dates...Read more

Note: the values in E5:E8 are actual dates, formatted with the custom number format "mmm".

Working from the inside out, the expression:

MATCH(TRUE,TEXT(date,"...Read more

The SMALL function is meant to extract the "nth" smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value, supply 2, and so on.

=SMALL(data,1) //...Read more

When building advanced formulas that use dynamic ranges, it's often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. Note: we want the last *relative position* ...Read more

In this example, the goal is to sum or count a set of variances in different ways. Variances are listed in D5:D15, which is also the named range variance. The first formula in F5 simply sums all variances with the...Read more

In this example, the goal is to extract the 3 best race times for each name from the 5 race times that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the fastest time, the 2nd fastest time, and the 3rd fastest time. This problem can be solved with the SMALL...Read more

In this example, the goal is to repeat a sequence of numbers. This is a useful way to create repeating sequences of numbers by itself. In addition, this formula is a building block to the more general formula here, which can repeat ranges and...Read more

In this example the goal is to "unwrap" a column of values into separate fields. The values are spaced evenly apart, and the result should be all related values on one row, where each column corresponds to a field of information. The input data appears in column B. Each "...Read more

The goal is to demonstrate how other formulas and functions can be nested inside the IF function. The example is a simple quantity-based discount formula.

IF function

The...Read more

In this example, the goal is to extract common values from two text strings that contain comma-delimited values. In the worksheet shown the values for "List1" appear in column B and the values for "List2" appear in column C. The results in column D show the intersection of the two lists, that is...Read more

The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Zodiac signs are used in horoscopes,...Read more

In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...Read more

The goal is to take a specific action when a value begins with "x", "y", or "z". As is often the case in Excel, there are multiple ways to approach this problem. The simplest way is to use the OR function with the LEFT function to create the required logical test. Another option is to...Read more

This formula relies on the helper table visible in the range B4:D10. Column B contains the six numbers we want as a final result. Column C contains the probability weight assigned to each number, entered as a percentage. Column D contains the cumulative probability, created with this formula in...Read more