Excel has SumIf and CountIf, but no AverageIf. However, with a little array magic, you can get Excel to average values, conditionally. Simply insert the following form as an array: =AVERAGE(IF(conditionalrange = condition, averagerange))
(To enter an array function, press [Ctrl]+[Shift]+[Enter] instead of just [Enter]. )
You can test this function by entering a column of values and comparing the results of AVERAGE() to the array. The AVERAGE() function in A7 evaluates all the values in column A. In contrast, the array (cell C7) evaluates only those values that are less than 10.
The form is versatile enough to handle conditions from the worksheet. For instance, the array function below averages values in column A only when the value in column B is the letter A.
