Explanation

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 unique groups with the UNIQUE function
  3. Calculate averages with the AVERAGEIFS function

Create the Excel Table

One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed. In this case, all we need to do is create a new table named data with the data shown in B5:D16. You can use the keyboard shortcut Control + T .

Video: How to create an Excel table

The table will now automatically expand or contract as needed when new data is added or removed.

List unique groups

The next step is to list the unique group values in column C starting in cell F5. For this we use the UNIQUE function . The formula in F5 is:

=UNIQUE(data[Group]) // unique groups

The result from UNIQUE is an array with 3 values like this:

{"A";"B";"C"}

This array lands as a spill range in cell F5 and lists all of the unique groups in the Group column of the table. The UNIQUE function will continue to output a list of unique groups, even if new groups are added to the data.

Video: Intro to the UNIQUE function

Video: What is an array ?

Calculate average

We now have what we need to calculate the average for each group. Our task is to calculate an average based on one criteria: the group name. A good way to do this is to use the AVERAGEIFS function, which is designed to calculate averages based on one or more criteria. Because we have unique groups in a spill range , we can point to this list directly. The formula in G5 is:

=AVERAGEIFS(data[Score],data[Group],F5#)

The first argument in AVERAGEIFS is average_range . This is the range that contains numbers to average. In this example, this is the Score column in the table:

=AVERAGEIFS(data[Score],

The next two arguments specify the criteria. The criteria range is data[Group], and the criteria is the spill range:

=AVERAGEIFS(data[Score],data[Group],F5#)

Because the spill range F5# contains 3 values, the AVERAGEIFS function returns 3 averages in an array like this:

{77.75;73.25;87.25}

Each number in the array is the average calculated for a group. These values spill into the range G5:G7.

When data changes

The key advantage to this approach is that it responds instantly to changes in the data. If new rows are added that refer to existing groups, the spill range returned by UNIQUE remains unchanged, and AVERAGEIFS calculates an updated set of averages. If new rows are added that include new groups, or if existing group values are changed, these changes are captured by the UNIQUE function, which expands the spill range in F5 if needed. If rows are deleted from the table, the spill range contracts as needed. In all cases, the spill range represents the current list of unique groups, and the AVERAGEIFS function uses this list to calculate averages.

Legacy Excel

In older versions of Excel without the UNIQUE function, the approach needs to be modified slightly. Instead of using the UNIQUE function to automatically extract unique groups, the groups can be entered manually. Then the formula in G5 becomes:

=AVERAGEIFS(data[Score],data[Group],F5)

Note the criteria is no longer a spill range. This formula can then be copied down to return an average for each group.

Note: there are ways to extract unique values in older versions of Excel, but they are more complicated.

Pivot Table option

A pivot table would also be a good way to solve this problem , and would provide additional features. However, one drawback is that pivot tables need to be refreshed to show the latest data. Formulas, on the other hand, update instantly when data changes.

Explanation

In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function , which is designed to calculate averages using multiple criteria. The second approach is based on the FILTER function and the AVERAGE function . For convenience only, both solutions use the named ranges amount (C5:C16) and date (B5:B16).

Note: the values in E5:E10 are valid Excel dates , formatted to display the month name only with the number format “mmm”. See below for more information.

AVERAGEIFS function

The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria . The generic syntax for AVERAGEIFS looks like this:

=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2,...)

In this problem, we need to configure AVERAGEIFS to average amounts by month using two criteria: (1) dates greater than or equal to the first day of the month, (2) dates less than the first day of the next month . We start off with the average range , which contains the values to average in data (C5:C16):

=AVERAGEIFS(amount,

Next, we need to enter the criteria needed to target the appropriate dates for each month. To make this step easier, the values in E5:E10 are all first of month dates formatted to show an abbreviated month name. To enter a criteria for the start date, we use the named range date (B5:B16) followed by a greater than or equal to operator (>=) concatenated to cell E5:

=AVERAGEIFS(amount,date,">="&E5,

To enter criteria for the end date, we use the EDATE function to advance one full month to the first day of the next month :

=EDATE(E5,1) // first of next month

We can then use the less than operator (<) to select the correct dates. The final formula in F5, copied down, is:

=AVERAGEIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1))

Roughly translated, the meaning of this formula is “Average the amounts in B6:B16 when the date in C5:C16 is greater than or equal to the date in E5 and less than the first day of the next month”. Notice we need to concatenate the dates to logical operators , as required by the AVERAGEIFS function. As the formula is copied down, it returns a total for each month in column E. The named ranges behave like absolute references and don’t change, while the reference to E5 is relative and changes at each new row.

Note: we could use the EOMONTH function to get the last day of the current month, then use “<=” as the second logical operator. However, because EOMONTH returns a date that is technically midnight, t here is a danger of excluding dates with times that occur on the last of the month. Using EDATE is a simpler and more robust solution.

FILTER with AVERAGE

Another nice way to average by month is to use the FILTER function with the AVERAGE function like this:

=AVERAGE(FILTER(amount,TEXT(date,"mmyy")=TEXT(E5,"mmyy")))

At a high level, the FILTER function extracts the amounts for a given month, and returns these amounts to the AVERAGE function, which calculates an average. The FILTER function is configured like this:

FILTER(amount,TEXT(date,"mmyy")=TEXT(E5,"mmyy"))

The first argument, array , is set to amount (C5:C16). The second argument, include, is where most of the work gets done:

TEXT(date,"mmyy")=TEXT(E5,"mmyy")

Here, we use the TEXT function to convert the dates to text strings in the format “mmyy”. Because there are 12 dates in the list, the result is an array with 12 values like this:

{"0122";"0222";"0222";"0322";"0322";"0322";"0422";"0422";"0422";"0522";"0522";"0522"}

Next, the TEXT function is used in the same way to extract the month and year from the date in E5:

TEXT(E5,"mmyy") // returns "0122"

The two results above are then compared to each other. The result is an array of TRUE and FALSE values like this:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

In this array, a TRUE value indicates dates in B5:B16 that are in the same month and year as the date in E5. As you can see, only the first date occurs in January 2022. The FILTER function uses this array to select only values in data that meet criteria. The result is delivered directly to the AVERAGE function like this:

=AVERAGE({100})

AVERAGE then returns a result of 100. As the formula is copied down, FILTER delivers amounts for each month to the AVERAGE function, which returns a final result.

One nice feature of this formula is that it automatically ignores time values that may be attached to dates, so there is no need to worry about excluding dates on the last day of the month that include time values, as with AVERAGEIFS above. This is because the logic created with the TEXT function only compares month and year values. It would be nice to use the TEXT function inside the AVERAGEIFS formula as well, but the AVERAGEIFS function won’t accept an array operation in a range argument .

Display dates as names

To display the dates in E5:E10 as names only, you can apply a custom number format . Select the dates, then use Control + 1 to bring up the Format Cells Dialog box and apply the “mmm” date format as shown below:

Custom number format to display month only - 1

This allows you to use valid Excel dates in column E (required for the formula) but display them as month names only.

Pivot Table solution

A pivot table is another excellent solution when you need to summarize data by year, month, quarter, and so on, because it can do this kind of grouping for you without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables .