In this example, the goal is to output the time between a start date and an end date as a text string that lists years, months, and days separately. For example, given a start date of 1-Jan-2018 and an end date of 1-Jul-2018, the result should be a string...Read more
In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated, except for the code appended at the end to represent version ("CA", "CB", "CC", "CD", etc.).
For a given file, we want to locate the...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
In this example the named range "range1" refers to cells B5:B8, and the named range "range2" refers to D5:D7. We are using named ranges for convenience and readability only; the formula works fine with regular cell references as well.
The core of this...Read more
The WORKDAY.INTL function is based on the WORKDAY function, which is designed to add work days to a date. WORKDAY automatically excludes Saturday and Sunday, and optionally can exclude a list of custom...Read more
In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.
The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not...Read more
The table in B3:D11 is a log that shows courses completed by various people. If a course has been completed by a person, there will be an entry in the table with name, course, and date. For the purpose of this example, if we find and entry for a given name/course, we can assume that course is...Read more
In this example, the goal is to count the people shown in the table by both Department (Dept) and Group as shown in the worksheet. A simple way to do this is with the COUNTIFS function.
The COUNTIFS function is...Read more
The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like "?" and "*". This means we can't use IF by itself...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
