The first formula is based on theAVERAGEIFS function, which is designed to calculate averages using multiple criteria.
The second formula is based on theFILTER functionand theAVERAGE function.
For convenience all data is in anExcel Tablenameddatain the range B5:E16.
Note: the values in G5:G8 are validExcel dates.
This makes it easier to use these values in the formula criteria.
you’ve got the option to use acustom number formatto display these dates any way you like.
Thestructured referencesbehave likeabsolute referencesand don’t change, while the reference to G5 isrelativeand changes at each new row.
The FILTER function uses this array to retrieve only values in January.
Unfortunately, this is not possible, because AVERAGEIFS won’t accept an array operation in a range argument.
This is a limitation of the *IFS functions in Excel, which you canread more about here.
For a side-by-side comparison of formulas vs. pivot tables, see this video:Why pivot tables.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
TEXT Function
The Excel TEXT function returns a number formatted as text.
in a text string with the number format of your choice.
