Note: The data being averaged is similar to the data in the previous several topics. I am not showing columns A:F in the above figure because it would be too small. See Figure 409 for the columns in the data set. The headings are in row 1 and the data is in rows 2 through 57.
The first argument is the range with the values that you want to average. This is F2:F57.
The next pairs of arguments specify that Excel should look through D2:D57 for ages that are greater than zero. Note the single dollar sign before the 1 in I$1. This lets you copy the formula. This argument will always point to the criteria in row 1, but the reference can change to column J, K, L, and M.
The next pair of arguments say to look through the ages in D2:D57 for ages less than 30. Again, the criteria is stored in I$2.
The final pair of arguments say to look through the departments in E2:E57 looking for records that match Accounting as stored in $H3.
Gotcha: The #DIV/0! in M6 is because the sales department has no employees above 59 years of age. (Probably because the sales reps have retired to a private island after earning those huge commissions for so long.) When you AVERAGE a range that contains no numeric cells, you end up dividing by zero.