How can I use the AVERAGE function like the SUMIF function?

[galatius]

I want to calculate the mean average of values that have been assigned to specific, recurring variables. I have organised my data like this:

Variable Column Value Column

Variable A(1) Value (A1)
Variable B(1) Value (B1)
Variable C(1) Value (C1)

Variable A(2) Value (A2)
Variable B(2) Value (B2)
Variable C(2) Value (C2)

Variable A(3) Value (A3)
Variable B(3) Value (B3)
Variable C(3) Value (C3)

The mean averages that I want to calculate are for the values of all variables A (i.e. A1 - An), B (B1 - Bn), etc.

Obviously I could simply do this using a regular AVERAGE function by manually inputing the cell references for all of the relevant values. However, this would be too time-consuming and I am sure there must be a quicker way. If there was an AVERAGEIF function then I could do this very easily by filtering the values that are averaged according to the corresponding variable in the same way that the SUMIF function filters values that are then summed.

 

[JRCBoston]

You should be able to use an array to do this.

 

[Richard Schollar]

=AVERAGE(IF(A1:A100="A",B1:B100))

Confirmed with Ctrl+Shift+Enter (not just with Enter).

 

[galatius]

Thanks RichardSchollar - that works perfectly!