MEDIAN() as array function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
{=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.
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
Click to expand...

--

[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.
 
Henrik wrote...
Is it possible to submit the MEDIAN() function as an array (ctrl + shift +
enter)? ....

Yes.

However, when I submit

{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}

it does not give me the intended result.
Click to expand...

Since these are fairly short arrays, what's in A1:B7, what result do
you get, and what result were you expecting?
 
Yeah, I wanted to keep my example simple although I intend on using this for
something much more sophisticated. My sample dataset looks like this (columns
A and B)

a 1
a 2
a 3
b 4
b 5
b 6
b 7

{=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
gives me "6" as intended

{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
gives me "0" although I want it to return "2" (i.e. I want it to find the
median of just observations which has "a" in COLUMN A).

Hope this makes sense. I appreciate your help as this is something that has
been bugging me for a while.

Thanks,
Henrik
 
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 said:
{=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.
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
Click to expand...

--

[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.
Click to expand...
 
{=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...
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


:

{=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.
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
Click to expand...

--

[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.
Click to expand...
Click to expand...

--

[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.
 
thanks this is extremely helpful!


Aladin Akyurek said:
{=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...
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


:

{=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.
Click to expand...
Click to expand...

--

[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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Median with Criteria 2
Median Array 4
Having a problem with calculating medians in access 1
Percentile 1
Array functions 6
Median array formula 1
median if 4
Can you calculate the Median without each value in separate cells? 5

Back
Top