Explanation
In this example, the goal is to create a formula that calculates an average salary by department, where data is an Excel Table in the range B5:D16. The solution shown requires three general steps:
- Create an Excel Table called data
- List unique departments with the UNIQUE function
- 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. To create a table, place the cursor in the data and use the keyboard shortcut Control + T . Then name the table “data”.
Video: How to create an Excel table
The table will now automatically expand or contract when new data is added or removed.
List unique departments
The next step is to list unique department names starting in cell F5. For this we use the UNIQUE function like this:
=UNIQUE(data[Department]) // unique departments
The result from UNIQUE is an array with 5 values like this:
{"Marketing";"IT";"Finance";"HR";"Sales"}
This array lands as a spill range in cell F5 and lists all of the unique department names in the Department column of the table. Note the UNIQUE function will continue to return a current list of unique departments when data changes.
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 department. To do this, we only need one condition: the department name. A good way to do this is to use the AVERAGEIFS function, which can calculate averages based on one or more criteria. Because we already have unique departments in column F, we can reference the list directly. The formula in G5 is:
=AVERAGEIFS(data[Salary],data[Department],F5#)
The first argument in AVERAGEIFS is average_range . This is the range that contains numbers to average. In this example, this is the Salary column in the table:
=AVERAGEIFS(data[Salary],
The next two arguments specify the criteria. The criteria range is data[Department] , and the criteria is the spill range :
=AVERAGEIFS(data[Salary],data[Department],F5#)
Because the spill range F5# contains 5 values, the AVERAGEIFS function returns 5 averages in an array like this:
{53333.3333333333;68500;71000;43500;59000}
Each number in the array is the average calculated for a given department. These values spill into the range G5:G7.
When data changes
The key advantage to using a Excel table with the UNIQUE function is that the formula responds instantly to changes in the data. If new rows are added that refer to existing departments, the spill range returned by UNIQUE remains unchanged, and AVERAGEIFS calculates a new set of averages. If new rows are added that include new departments, or if existing departments values are changed, these changes are captured by the UNIQUE function, which expands the spill range in F5 as 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 departments, 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 departments, the departments can be entered manually. Then the formula in G5 becomes:
=AVERAGEIFS(data[Salary],data[Department],F5)
Note the criteria is no longer a spill range. This formula can then be copied down to return an average for each department.
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 an average of the top 3 quiz scores for each name listed in column B. For reference, column H has a formula that calculates an average of all 4 scores. This is a slightly tricky problem, because it’s not obvious how to limit the scores included in the average to only the top 3 scores. The classic solution is to use the AVERAGE function with the LARGE function as explained below.
LARGE function
The LARGE function is designed to retrieve the “top nth value” from a set of numbers. For a given range, LARGE(range,1) will return the largest value, LARGE(range,2) will return the 2nd largest value, and so on, as seen below:
LARGE(range,1) // 1st largest value
LARGE(range,2) // 2nd largest value
LARGE(range,3) // 2nd largest value
In this problem, we need to configure LARGE to retrieve the largest 3 values, and the easiest way to do that is to pass an array constant like {1,2,3} into LARGE as the second argument , k . Because we are asking for the top 3 values, LARGE will return an array that contains all 3 values:
LARGE(range,{1,2,3}) // largest 3 values
LARGE with AVERAGE
In the example shown, the formula in cell I5 is:
=AVERAGE(LARGE(C5:F5,{1,2,3}))
Working from the inside out, the LARGE function is configured to extract the top 3 scores from the range C5:F5 like this:
LARGE(C5:F5,{1,2,3}) // returns {10,10,10}
Because we have provided the array constant {1,2,3} for the second argument, k , LARGE returns the top 3 scores in C5:F5 in an array like this:
{10,10,10}
This array is returned directly to the AVERAGE function:
=AVERAGE({10,10,10}) // returns 10
The AVERAGE function then returns the average of these values as a final result. As the formula is copied down, it returns an average of the top 3 scores for each name in the list. Although this set of data contains only 4 scores, the formula will work correctly for any number of scores, as long as there are at least 3.
Variable n
To make this formula use a variable for n , where n represents the number of values to include in the average, you can add the SEQUENCE function like this:
=AVERAGE(LARGE(C5:F5,SEQUENCE(n)))
To supply a value for n , you can use a cell reference like A1 or simply hardcode a number into the formula. The SEQUENCE function dynamically generates a numeric array which is then returned to LARGE as the second argument, and the formula works the same thereafter.
Legacy Excel
In Legacy Excel , the SEQUENCE function does not exist. The classic solution for creating a numeric array in older versions of Excel is to use the ROW and INDIRECT functions. For example, to generate a numeric array from 1 to 10, you can use a formula like this:
=ROW(INDIRECT("1:10")) // returns {1;2;3;4;5;6;7;8;9;10}
The INDIRECT function converts the text string “1:10” to the range 1:10, which is returned to the ROW function. ROW then returns an array of row numbers like {1;2;3;4;5;6;7;8;9;10}. To make n variable, you can concatenate the string “1:” to a cell reference that provides a value for n :
=AVERAGE(LARGE(C5:F5,ROW(INDIRECT("1:"&n))))
Note that this formula is an array formula and must be entered with control + shift + enter in older versions of Excel.
