I was asked to find the average sales between two date ranges. Well in earlier posts I described how to use SUMIFS and COUNTIFS to get the sum of values between two dates and to count the number of rows between two dates.
Well another cousin of SUMIFS and COUNTIFS is AVERAGIFS.
AVERAGEIFS allows you to get an average of a range of data base on specific criteria(s) (hence the IFS in AVERAGETFS).
In the above example I have a few columns of Data. I want the average total in Column C that have a date range between 1/5/2011 AND 1/20/2011. The formula is in Cell E2 but I show it to you in Cell D2.
If I build the formula with the function Argument dialog box, it would look like the following…
Looking at the data sorted by date, I can see that my calculation is correct since the total of the cells highlighted in yellow is 18,000. When I divide that by 4, I get 4,500.
Some notes on the AVERAGEIFS function….
Average_range is one or more cells to average, including numbers or names, arrays, or references that contain numbers.
Some samples of the AVERAGEIFS criteria are as follows….
45 – no quotes are required for numbers.
“Invoice” – needs quotes around it since it is text.
“>50” – needs quotes around it since it includes the > sign.
D7 – no quotes requires as it references a cell.
If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
This formula is not available in Excel 2003 or older.
How have you used AVERAGEIFS to resolve a problem?