Dynamic two-way average

To perform a dynamic two-way average with a formula, you can use an Excel Table, the UNIQUE function, and the AVERAGEIFS function connected to the spill ranges returned by UNIQUE. In the example shown, the formula in cell G5 is: =AVERAGEIFS(data[Rating],data[Age],F5#,data[Gender],G4#) Where data is an Excel Table based on the data in B5:D16. The result from AVERAGEIFS spills into the range G5:H7. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021.

April 14, 2025 · 1 min · 102 words · Sara Petersen

Table of Contents

For this we use theUNIQUE function.

This is what makes this solution fully dynamic.

Video:What is an array?

Excel formula: Dynamic two-way count

Calculate unique averages

We now have what we need to calculate the averages.

The formula in G5 is:

The firstargumentin AVERAGEIFS isaverage_range.

This is the range that contains numbers to average.

Excel formula: Dynamic two-way sum

In this example, this is the Rating column in the table:

The other arguments arerange/criteriapairs.

If rows are deleted from the table, spill ranges contract as needed.

However, one drawback is that pivot tables need to be refreshed to show the latest data.

Excel UNIQUE function

Formulas, on the other hand, update instantly when data changes.

Excel TRANSPOSE function

Excel AVERAGEIFS function