The goal is to search through a cell for one of several specified values and return the last match found when one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list) and a series of short sentences in the range B5:B16. The task is to...Read more
This formula depends on the CEILING function, which rounds numbers up to a given multiple. It works because of how dates work in Excel's default 1900 date system, where the first day is the number 1, which is Sunday, January 1, 1900.
In this...Read more
Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (...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
Named ranges make formulas easier to read, faster to develop, and more portable. They're also useful for data validation, hyperlinks, and dynamic ranges. This article shows you how you can use named ranges to build better spreadsheets, and better formulas.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
This example demonstrates how to calculate the nth occurrence of a specific day of the week within a month. For instance, you might need to find the 2nd Tuesday, the 4th Wednesday, or the 1st Friday of any given month. The worksheet is structured with the starting date in column B, the...Read more
First, this formula determines the first day of the next month *after* a given date. It does this my using EOMONTH to get the last day of the month, then adding one day:
=EOMONTH(B5,0)+1
Next, the formula calculates the number of days...Read more
The CHOOSE function does most of the work in this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index.
In this case, we are providing...Read more
This formula uses the MIN function to make a decision that might otherwise be handled with the IF function. Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for...Read more