Help finishing array formula!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat Flynn - 06 Nov 2006 16:05 GMT

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
4444>Don't show
45656>Show value
123>Don't Show
45689>Show value
Thanks for the help.

Reply to this Message

Dave F - 06 Nov 2006 16:08 GMT

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

Signature

Brevity is the soul of wit.

> 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
[quoted text clipped - 6 lines]
> 45689>Show value
> Thanks for the help.

Reply to this Message

Pat Flynn - 06 Nov 2006 17:02 GMT

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.

> To test for the length of a text string, use the function LEN: =LEN(A1)
> returns the number of characters in A1.
[quoted text clipped - 17 lines]
> > 45689>Show value
> > Thanks for the help.

Reply to this Message

Bob Phillips - 06 Nov 2006 18:51 GMT

=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)))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> 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
[quoted text clipped - 16 lines]
> > > 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
> > > 4444>Don't show
> > > 45656>Show value
> > > 123>Don't Show
> > > 45689>Show value
> > > Thanks for the help.

Reply to this Message

Pat Flynn - 06 Nov 2006 19:12 GMT

I didn't think of this-works great-much thanks.

> =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)),"",
[quoted text clipped - 36 lines]
> > > > 45689>Show value
> > > > Thanks for the help.

Reply to this Message