Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average with Criteria | Excel Worksheet Functions | |||
Average top third with criteria | Excel Worksheet Functions | |||
AVERAGE BASED ON 2 CRITERIA | Excel Worksheet Functions | |||
average with 2 criteria | Excel Worksheet Functions | |||
Average given criteria, HELP! | Excel Worksheet Functions |