average using two criteria
 

I am trying to average using 2 criteria. In column A I have gender as M or F.
In Column B I have ethnicity as A, W etc. In column C I have levels from -2
to 4. I want to average column C where column A = M and Column B = A. I don't
want to include in the average range a M if it does not have a corresponding
level in column C. I have used =AVERAGE(IF((gen="M")*(eth="A"),level)) as an
array (eg entering the above formula and then entering CTRL + SHIFT + Enter
rather than just enter). SO basically I wanted to know how to add the extra
condition to average only if it has M in column A, A in coumn B and a level
in Column C.

Hope this makes sense!

average using two criteria
 

Try array formula

=AVERAGE(IF((A2:A10="m")*(C2:C10="a")*(M2:M10<"") ,M2:M10))

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

I am trying to average using 2 criteria. In column A I have gender as M or F.
In Column B I have ethnicity as A, W etc. In column C I have levels from -2
to 4. I want to average column C where column A = M and Column B = A. I don't
want to include in the average range a M if it does not have a corresponding
level in column C. I have used =AVERAGE(IF((gen="M")*(eth="A"),level)) as an
array (eg entering the above formula and then entering CTRL + SHIFT + Enter
rather than just enter). SO basically I wanted to know how to add the extra
condition to average only if it has M in column A, A in coumn B and a level
in Column C.

Hope this makes sense!

average using two criteria
 

Thanks! That is FAB and v speedy!

:o)

"Jacob Skaria" wrote:

Try array formula

=AVERAGE(IF((A2:A10="m")*(C2:C10="a")*(M2:M10<"") ,M2:M10))

If this post helps click Yes
---------------
Jacob Skaria


"DJ" wrote:

I am trying to average using 2 criteria. In column A I have gender as M or F.
In Column B I have ethnicity as A, W etc. In column C I have levels from -2
to 4. I want to average column C where column A = M and Column B = A. I don't
want to include in the average range a M if it does not have a corresponding
level in column C. I have used =AVERAGE(IF((gen="M")*(eth="A"),level)) as an
array (eg entering the above formula and then entering CTRL + SHIFT + Enter
rather than just enter). SO basically I wanted to know how to add the extra
condition to average only if it has M in column A, A in coumn B and a level
in Column C.

Hope this makes sense!