Average of selected field

[Guest]

I'm trying to take an average of a set of numbers (C12:E21) that correspond
to a set of qualifiers. For example, there are values corresponding to
Corporal, General, Private, and Sargeant. I am trying to tell excel to take
an average in C12:E21 for ONLY the lines corresponding to Corporal. The
equation i'm using now is as follows:
=IF($B28=0,0,SUM(SUMIF($B$11:$B$21,$A28,C$11:C$21),SUMIF($B$11:$B$21,$A28,D$11$21),SUMIF($B$11:$B$21,$A28,E$11:E$21))/((COUNTIF($B$11:$B$21,!$A28))*3))
where A28 is the name "corporal" and B28 is the number of corporals in the
set. the *3 is because there are three columns of numbers. Is there a
better,more leveragable way to do this?

 

[Domenic]

Try the following...

=IF($B28=0,0,AVERAGE(IF($B$11:$B$21=$A28,$C$11:$E$21)))

To exclude empty cells or zero values...

=IF($B28=0,0,AVERAGE(IF(($B$11:$B$21=$A28)*($C$11:$E$21>0),$C$11:$E$21)))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!