Array functions

[anand]

=MEDIAN(IF(EJ$2:EJ$5709="dulcer",IF(GJ$2:GJ$5709=0,IF(GN$2:GN$5709="",GM$2:GM$5709))))
Entered as an array, the above formula works in giving me the median value
that I'm looking for. Also works for aveerage and standard deviation.

However, I need quartile values as well. When I substitute "quartile" for
"median" and add a "comma,1} following the GM$2:GM$5709 to read:

=quartile(IF(EJ$2:EJ$5709="dulcer",IF(GJ$2:GJ$5709=0,IF(GN$2:GN$5709="",GM$2:GM$5709,1))))

the formula fails with the message that I've entered too few arguments for
this function.

Can anyone tell me what the problem is?

Also 2nd question.

If I want to enter multiple text terms or #'s the the column EJ2:EJ5709 i.e.

both "dulcer" and "decub" or several #'s like 1, 3 and 7 i.e. if either of
those text terms or any of those numbers are present, then include them in
the array analysis of median values, how do I do that? I know I drop out the
quotation symbol for #'s but not sure how to combine several

Thanks

anand

 

[ShaneDevenshire]

=quartile(IF(EJ$2:EJ$5709="dulcer",IF(GJ$2:GJ$5709=0,IF(GN$2:GN$5709="",GM$2:GM$5709,1))))

should be

=quartile(IF(EJ$2:EJ$5709="dulcer",IF(GJ$2:GJ$5709=0,IF(GN$2:GN$5709="",GM$2:GM$5709))),1)

 

[ShaneDevenshire]

Hi,

Second question:

=MEDIAN(IF(OR(EJ$2:EJ$5709="dulcer",EJ$2:EJ$5709="decub"),IF(GJ$2:GJ$5709=0,IF(GN$2:GN$5709="",GM$2:GM$5709))))

The OR can have up to 30 arguments.

 

[Domenic]

For the second part, try...

=MEDIAN(IF(ISNUMBER(MATCH(EJ$2:EJ$5709,{1,3,7,"decub","dulcer"},0)),IF(GJ
$2:GJ$5709=0,IF(GN$2:GN$5709="",GM$2:GM$5709))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[anand]

Thanks, that helps tremendously. Same approach for #'s or is there something
simpler along the line of what is the following formula (which seems differnt
than the way array formulas are set up.

=IF(MAX(COUNTIF($G2150:$AA2150,"="&{29,429,851})),1,"")

Can the & function be adapted into the array funtion?

anand

 

[ShaneDevenshire]

You may need to reconsider you formulas - if a cell is empty it is ="" but
ALSO a blank cell is =0. Which means that IF doesn't distinguish between 0
and empty.

Also, when you clear that question up I can suggest simplier formulas for
all your questions.

 

[Domenic]

The OR function cannot be used in an array this way, since it only
returns a single result...

Cheers!