In this example, the goal is to highlight all values in K4:M7 (values) that appear in the range B4:I15 (data). The range K4:M7 is named "values" for readability and convenience only. If you don't want to use a named range, use an ...Read more
In this example, the goal is to get a count of people that hold shares in whole numbers. For example, Bob holds 100 shares (even), so he should be included in the whole number count, while Cindy holds 50.5 shares, so she should not be included in the whole number count....Read more
In this example, the goal is to test if a numeric value is a whole number. There are several ways to go about this. One of the easiest ways is to use the MOD function with a divisor of 1. Any whole number divided by 1 will result in a remainder of zero:...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
The goal in this example is to display the last 3 valid entries from the table shown, where "valid" is defined as a temperature of less than 75 in the "Temp" column. At a high level, the FILTER function is used to filter entries based on a logical test, and the INDEX function is used to extract...Read more
With the introduction of Dynamic Arrays in Excel formulas, there is more emphasis on arrays. The #CALC! error occurs when a formula runs into a calculation error with an array. The #CALC! error is a "new" error...Read more
In this example, the goal is to calculate durations in "days" starting from the start date and time in cell G5 and ending at the dates and times shown in column B. The twist is that we want to classify the durations using the custom labels shown in column E, starting with "Day 0" for the first...Read more
In this example the goal is to sort the comma separated values in column B in alphabetical order. In the latest version of Excel, you can solve this problem with a formula based on TEXTSPLIT, SORT, and TEXTJOIN. In earlier versions of Excel the problem is more...Read more
The goal in this example is to calculate a cost of living (COLA) adjustment for each of the eleven years shown, starting on the second year. The actual adjustment percentage is entered in cell F6, which is the named range cola. Each year, the adjustment should be applied to the...Read more
