Dave Bruns

The formula in this example converts amounts in USD to other currencies using currency codes and a simple lookup table. The available currencies and exact conversion rates can be adjusted by editing the values in the table on the right. The core of this formula is the VLOOKUP function,...Read more

In Excel, you can't easily create formulas that skip columns following a certain pattern, because the references in the formula will automatically change to maintain the relationship between the original source cell and the new target cell. However, with a little work, it's possible to construct...Read more

The goal is to mark rows where the color is "Red" AND the size is "Small" or "Medium". To perform this task, you can use the IF function in combination with the AND function and the...Read more

In this example, the goal is to calculate total amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. There are three basic approaches to solving this problem: (1) a traditional approach based on the SUMIFS...Read more

In this example, the goal is to sum the smallest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three smallest values, so n is equal to 3. At a high level, this problem breaks down into three...Read more

We want to count how often items in columns B, C, and D appear together. For example, how often A appears with C, B appears with F, G appears with D, and so on. This would seem like a perfect use of COUNTIFS, but if we try to add criteria looking for 2 items across 3 columns, it isn't going to...Read more

Data validation rules are triggered when a user adds or changes a cell value. This formula takes advantage of this behavior to provide a clever way for the user to switch between a short list of cities and a longer list of cities. In the worksheet shown, the data validation applied to C4 looks...Read more