Friday, July 1, 2022
Home Formulas & Functions Array Formulas

# Array Formulas

Excel formulas that use arrays

### A Two-Part Excel Puzzle, with Arrays, Text, ASCII, and Concatenation

Recently, I needed to compare two long columns of text of various lengths to make sure that each row of text in column A...

### How to Report Top and Bottom Results Using Dynamic Arrays in Excel

In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer...

### How to Use Excel’s LET Function

In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...

### How to Define General Ledger Account Groups in Excel

In Part 1 of this series, How to Report GL Account Groups in Excel, we explored the strategy for creating financial reports that use...

### How to Report GL Account Groups in Excel

Believe it or not, this income statement is quite sophisticated. It's not nearly as simple-minded as it looks. In fact, this income statement illustrates a...

### How to Use SUMPRODUCT to Find the Last Item in an Excel List

Suppose you have a Sequential List of items, as in the second table in the figure below. And suppose you want to show the...

### How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas

A friend recently asked, "In this table, how can I return the date where the lowest value occurs?" More generally, however, he was asking how to...

### How to Summarize Spreadsheet Data with Excel’s Array Formulas

Subtotals can reveal very useful management information. For example, managers might be interested to learn that sales increased by 10% last month. But they would...

### Use Excel’s SUMPRODUCT to Summarize Worksheet Data

After I posted  The Most Powerful Ways to Summarize Excel Data for Reporting and Analysis, a reader asked why I hadn't discussed the SUMPRODUCT function. I...

### How to Smooth Data by Using the TREND Function

Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...

### Consumer Sentiment Suggests a 2022 Recession

In recent months, business websites have speculated about recessions and stagflation in 2022. These predictions could affect your Excel work significantly in the next few...

### Simple Transformations Can Reveal Hidden Patterns in Your Data

Excel users have at least two significant advantages over business professionals who rely on other analytical and reporting tools. First advantage: We Excel users can...