For this we use theUNIQUE function.
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.
Because we have unique groups in aspill range, we can point to this 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 groups, the groups 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 group.
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
