Dave Bruns

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:

1. Create an Excel Table called data
2. List
...Read more

In the example shown, we have a list of amounts in column C. The goal is to dynamically sum the last n amounts using the number that appears in cell E5 for n. Since the list may grow over time, the key requirement is to sum amounts by position...Read more

In this example, the goal is to create a formula that will extract unique values from a range of data in a case-sensitive way. Normally, we would use the UNIQUE function to extract unique values. However, UNIQUE is not case-sensitive so it won't work in this situation. One way to solve this...Read more

A common situation in Excel is to use the SORT function to sort results returned by the FILTER function. However, a formula based on the FILTER and SORT may return an error when no data is returned. In this...Read more

In this example, the goal is to sum the quantities in column C by the codes in column E in a case-sensitive manner. The SUMIF function and the SUMIFS function are both good options for counting text values, and...Read more

In this example, the goal is to calculate an average for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. For convenience only, data (C5:E16) and group (B5:B16) are named ranges. In...Read more

In this example, the goal is to use a formula to split a text string into an array of characters. For example, if the text string is "Apple", the resulting array should be {"A","p","p","l","e"}. For a long time, this was quite a difficult problem that...Read more

In this example, the goal is to count numbers that appear in column B. The COUNT function is designed to only count numeric values, but because all values in the range B5:B15 are text, COUNT will return zero. One approach...Read more

In this example, the goal is to sum every nth value in a range of data, as seen in the worksheet above. For example, if n=2, we want to sum every second value (every other value), if n=3, we want to sum every third value, and so on. All data is in the range B5:...Read more