Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MEDIAN() as array function?
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 |
#2
|
|||
|
|||
{=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. |
#3
|
|||
|
|||
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. Since these are fairly short arrays, what's in A1:B7, what result do you get, and what result were you expecting? |
#4
|
|||
|
|||
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 "Harlan Grove" wrote: 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. Since these are fairly short arrays, what's in A1:B7, what result do you get, and what result were you expecting? |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
{=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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using AND function within an array formula | Excel Worksheet Functions | |||
Array Function | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |