In this example, we have a simple worksheet that uses the XLOOKUP function to lookup the name of a U.S. state when a valid two-letter code is provided as a lookup value. The goal is to remove the #N/A error that XLOOKUP returns when it can't find a result. Although you could use the IFNA or...Read more
In this example, the goal is to count the number of cells in a range that contain formulas. This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below.
The values in the range C13:C16 are forecasts created...Read more
In this example, the goal is to replace the comma-separated values in column B with the line break-separated values seen in column D. In a problem like this, the first step is to identify the delimiter, which is the character (or characters) that separate each value we want to...Read more
In this example, the goal is to filter data based on multiple criteria with the FILTER function. Specifically, we want to select data where (1) the group = "A" and (2) the Score is greater than 80. At first glance, it's not obvious how to do this with the FILTER function. Unlike older...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 calculate the required recovery rate (or gain) to complete offset a loss expressed as a negative percentage. This is not a difficult problem, but you must pay attention to the sign of the loss and be sure to format the result with the...Read more
In this example, the goal is to create a formula that calculates an average by group, using the group names in column C. The solution shown requires three general steps:
