List unique departments
The next step is to list unique department names starting in cell F5.
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.
Because we already have unique departments in column F, we can reference the list directly.
The formula in G5 is:
The firstargumentin AVERAGEIFS isaverage_range.
This is the range that contains numbers to average.
These valuesspillinto the range G5:G7.
If rows are deleted from the table, the spill range contracts as needed.
Instead of using the UNIQUE function to automatically extract unique departments, the departments can be entered manually.
Then the formula in G5 becomes:
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 toextract unique values in older versions of Excel,but they are more complicated.
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.
Related videos
How to use the AVERAGEIFS function
The UNIQUE function
