Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help finishing array formula!
I need the following array formul to only select cell values longer than a
string of 4. The array works-but I cannot get it to discriminate and only select values of 5 characters and larger. =IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT("1:4")))) Ex. Col A 4444Don't show 45656Show value 123Don't Show 45689Show value Thanks for the help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help finishing array formula!
To test for the length of a text string, use the function LEN: =LEN(A1)
returns the number of characters in A1. So, if you're testing for text strings longer than 5 characters you would want something like =IF(LEN(A1)5,"VALID","INVALID") I'm not sure how that would fit into your formula below as I'm not sure what you're trying to accomplish with the INDIRECT and LARGE functions. Dave -- Brevity is the soul of wit. "Pat Flynn" wrote: I need the following array formul to only select cell values longer than a string of 4. The array works-but I cannot get it to discriminate and only select values of 5 characters and larger. =IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT("1:4")))) Ex. Col A 4444Don't show 45656Show value 123Don't Show 45689Show value Thanks for the help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help finishing array formula!
Thanks for the response Dave. I tried the len() function-but keep getting an
error message that I have added too many arguments. I solved it by using the len() with conditional formatting and [hiding] the values I do not want printed. So I cheated! Thanks again for the response. "Dave F" wrote: To test for the length of a text string, use the function LEN: =LEN(A1) returns the number of characters in A1. So, if you're testing for text strings longer than 5 characters you would want something like =IF(LEN(A1)5,"VALID","INVALID") I'm not sure how that would fit into your formula below as I'm not sure what you're trying to accomplish with the INDIRECT and LARGE functions. Dave -- Brevity is the soul of wit. "Pat Flynn" wrote: I need the following array formul to only select cell values longer than a string of 4. The array works-but I cannot get it to discriminate and only select values of 5 characters and larger. =IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT("1:4")))) Ex. Col A 4444Don't show 45656Show value 123Don't Show 45689Show value Thanks for the help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help finishing array formula!
=IF(ISERROR(SMALL(IF(LEN($D$6:D$14)4,ROW($D$6:$D$ 14),""),ROW($D$6:$D$14)-MI
N(ROW($D$6:$D$14))+1)),"", INDEX($D$1:$D$14,SMALL(IF(LEN($D$6:D$14)4,ROW($D$ 6:$D$14),""),ROW($D$6:$D$1 4)-MIN(ROW($D$6:$D$14))+1))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pat Flynn" wrote in message ... Thanks for the response Dave. I tried the len() function-but keep getting an error message that I have added too many arguments. I solved it by using the len() with conditional formatting and [hiding] the values I do not want printed. So I cheated! Thanks again for the response. "Dave F" wrote: To test for the length of a text string, use the function LEN: =LEN(A1) returns the number of characters in A1. So, if you're testing for text strings longer than 5 characters you would want something like =IF(LEN(A1)5,"VALID","INVALID") I'm not sure how that would fit into your formula below as I'm not sure what you're trying to accomplish with the INDIRECT and LARGE functions. Dave -- Brevity is the soul of wit. "Pat Flynn" wrote: I need the following array formul to only select cell values longer than a string of 4. The array works-but I cannot get it to discriminate and only select values of 5 characters and larger. =IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT ("1:4")))) Ex. Col A 4444Don't show 45656Show value 123Don't Show 45689Show value Thanks for the help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help finishing array formula!
I didn't think of this-works great-much thanks.
"Bob Phillips" wrote: =IF(ISERROR(SMALL(IF(LEN($D$6:D$14)4,ROW($D$6:$D$ 14),""),ROW($D$6:$D$14)-MI N(ROW($D$6:$D$14))+1)),"", INDEX($D$1:$D$14,SMALL(IF(LEN($D$6:D$14)4,ROW($D$ 6:$D$14),""),ROW($D$6:$D$1 4)-MIN(ROW($D$6:$D$14))+1))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pat Flynn" wrote in message ... Thanks for the response Dave. I tried the len() function-but keep getting an error message that I have added too many arguments. I solved it by using the len() with conditional formatting and [hiding] the values I do not want printed. So I cheated! Thanks again for the response. "Dave F" wrote: To test for the length of a text string, use the function LEN: =LEN(A1) returns the number of characters in A1. So, if you're testing for text strings longer than 5 characters you would want something like =IF(LEN(A1)5,"VALID","INVALID") I'm not sure how that would fit into your formula below as I'm not sure what you're trying to accomplish with the INDIRECT and LARGE functions. Dave -- Brevity is the soul of wit. "Pat Flynn" wrote: I need the following array formul to only select cell values longer than a string of 4. The array works-but I cannot get it to discriminate and only select values of 5 characters and larger. =IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT ("1:4")))) Ex. Col A 4444Don't show 45656Show value 123Don't Show 45689Show value Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |