{=MEDIAN(IF($A$1:$A$7="a",$B$1:$B$7))}
*does* return 2 for the sample you provide. So would:
{=MEDIAN(IF($A$1:$A$7="a",$B$1:$B$7,""))}
but not
{=MEDIAN(IF($A$1:$A$7="a",$B$1:$B$7,))}
as you had it...
Henrik wrote:
Aladin,
Thanks for your help. What would the correct solution be? My sample dataset
looks like this (columns A and B)
a 1
a 2
a 3
b 4
b 5
b 6
b 7
I inteded on having the function {=MEDIAN(IF($A$1:$A$7="a",$B$1:$B$7))}
return "2" (i.e. just finding the medians on observations with "a" in column
A).
Hope this makes sense. I appreciate your help as I have been struggling with
this for while for several of the built-in statistical functions in Excel.
Also, please note that I made up this overly simplified dataset for the sake
of this question. I acutally intend on using the method for something far
more complex.
Thanks,
Henrik
"Aladin Akyurek" wrote:
{=MEDIAN(IF($A$1:$A$7="a",$B$1:$B$7))}
Yours creates 0 values for non-a's, due to the trailing comma, the above
FALSE values, which MEDIAN ignore.
Henrik wrote:
Hi,
Is it possible to submit the MEDIAN() function as an array (ctrl + shift +
enter)?
I.e., I would like to submit a function similar to this SUM() function:
{=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
However, when I submit
{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
it does not give me the intended result.
Your help is much appreciated.
Henrik
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
|