| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array functions
=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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array functions
=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) -- Cheers, Shane Devenshire "anand" wrote: =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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array functions
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. -- Cheers, Shane Devenshire "anand" wrote: =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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array functions
For the second part, try...
=MEDIAN(IF(ISNUMBER(MATCH(EJ$2:EJ$5709,{1,3,7,"dec ub","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! In article , anand wrote: =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:G M$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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array functions
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" wrote: 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. -- Cheers, Shane Devenshire "anand" wrote: =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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array functions
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. -- Cheers, Shane Devenshire "Domenic" wrote: For the second part, try... =MEDIAN(IF(ISNUMBER(MATCH(EJ$2:EJ$5709,{1,3,7,"dec ub","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! In article , anand wrote: =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:G M$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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array functions
The OR function cannot be used in an array this way, since it only
returns a single result... Cheers! In article , ShaneDevenshire wrote: 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. -- Cheers, Shane Devenshire |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indirect ref in array formulas | Excel Worksheet Functions | |||
| SUMPRODUCT + CSE | Excel Worksheet Functions | |||
| Question to Bob Phillips (or whoever...) | Excel Worksheet Functions | |||
| Array | Excel Worksheet Functions | |||
| array functions and ISNUMBER() | Excel Worksheet Functions | |||
Linear Mode
