Average function and two criteria
 

Hi!
I'd appreciate your help with a formula. I'm trying to get the avarage
ages of all the people on a list that meet two different criteria. The
Ages are in column F and the criteria are in in columns A and B.
Could you please tell me what's wrong with my formula? :confused: I'm
entering it as an Array formula.


{=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1: $B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}

Many thanks

Paula


--
Paula M
------------------------------------------------------------------------
Paula M's Profile: http://www.excelforum.com/member.php...o&userid=26465
View this thread: http://www.excelforum.com/showthread...hreadid=397346

Try...

=AVERAGE(IF((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA !$B$1:$B$995="H"),ARGENTINA!F1:F995))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the
ranges accordingly.

Hope this helps!

Paula M Wrote:
Hi!
I'd appreciate your help with a formula. I'm trying to get the avarage
ages of all the people on a list that meet two different criteria. The
Ages are in column F and the criteria are in in columns A and B.
Could you please tell me what's wrong with my formula? :confused: I'm
entering it as an Array formula.


{=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1: $B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}

Many thanks

Paula


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=397346

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA !$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
Hi!
I'd appreciate your help with a formula. I'm trying to get the avarage
ages of all the people on a list that meet two different criteria. The
Ages are in column F and the criteria are in in columns A and B.
Could you please tell me what's wrong with my formula? :confused: I'm
entering it as an Array formula.


{=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1: $B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}

Many thanks

Paula

:) THANK YOU BOTH!! :)
The two options worked just fine!


--
Paula M
------------------------------------------------------------------------
Paula M's Profile: http://www.excelforum.com/member.php...o&userid=26465
View this thread: http://www.excelforum.com/showthread...hreadid=397346

Hi,
I need your help again. Now, I need to know the avarage age of the
people belonging to the Sales deparment that are either category "C" or
"H".
Column A has the different departments (S, F, D, E, etc), column B, the
different categories (C, H, B, etc), and column F has the employee ages.

I thought of this:

=((AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTI NA!$B$1:$B$999="C"),ARGENTINA!$F$1:$F$999)))+(AVER AGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTINA!$B$1 :$B$999="H"),ARGENTINA!$F$1:$F$999))))/2
Entering it with Ctrl+shift+enter as an array formula.

It works fine when both averages are greater than 0, but it is no good
when one of them is 0. Any ideas to solve my problem? All suggestions
are welcomed!

Thanks for your help!


--
Paula M
------------------------------------------------------------------------
Paula M's Profile: http://www.excelforum.com/member.php...o&userid=26465
View this thread: http://www.excelforum.com/showthread...hreadid=397346

Try...

=AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ISNUMBER( MATCH(ARGENTINA!$B$1:$B$999,{"C","H"},0))),ARGENTI NA!$F$1:$F$999))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Paula M Wrote:
Hi,
I need your help again. Now, I need to know the avarage age of the
people belonging to the Sales deparment that are either category "C" or
"H".
Column A has the different departments (S, F, D, E, etc), column B, the
different categories (C, H, B, etc), and column F has the employee ages.

I thought of this:

=((AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTI NA!$B$1:$B$999="C"),ARGENTINA!$F$1:$F$999)))+(AVER AGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTINA!$B$1 :$B$999="H"),ARGENTINA!$F$1:$F$999))))/2
Entering it with Ctrl+shift+enter as an array formula.

It works fine when both averages are greater than 0, but it is no good
when one of them is 0. Any ideas to solve my problem? All suggestions
are welcomed!

Thanks for your help!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=397346

:) Thank you again, Domenic!


--
Paula M
------------------------------------------------------------------------
Paula M's Profile: http://www.excelforum.com/member.php...o&userid=26465
View this thread: http://www.excelforum.com/showthread...hreadid=397346