Average of selected field
 

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:D$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?

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$210),$C$11:$E$21)))

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

Hope this helps!

In article ,
"GJR3599" wrote:

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
:D$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?