About Us

thanks this is extremely helpful!

"Aladin Akyurek" wrote:

{=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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using AND function within an array formula	Andrew L via OfficeKB.com	Excel Worksheet Functions	3	August 1st 05 06:49 PM
Array Function	N Harkawat	Excel Worksheet Functions	9	July 18th 05 11:14 PM
Array Function with VLOOKUP	CoRrRan	Excel Worksheet Functions	15	April 8th 05 05:54 PM
Need a ISWorkday Function -- Any Ideas	Mark	Excel Worksheet Functions	5	March 29th 05 01:58 AM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE.	Robert AS	Excel Worksheet Functions	4	December 2nd 04 10:49 AM

Menu

About Us