Re: multiple AND statements

Giganews Newsgroups
Subject: Re: multiple AND statements
Posted by:  J.E. McGimpsey (jemcgimps…@mvps.org)
Date: Sun, 26 Oct 2003

It's my experince that any time I get more than four or five
conditions, there are better ways of handling it than nested IFs or
multiple ANDs. You don't give a lot of detail, but if, say your two
condition formula was something like:

    =IF(A1<5, 1, IF(A1<10, 2, ""))

then for your twenty condition formula (which you can't do with
nested IF()s anyway, since the nesting is limited to seven levels),
you might instead use:

    =INT(A1/5)+1  or  =IF(A1<=100, INT(A1/5),"")

if your desired conditions or results are less regular, a lookup
table works well:

      J            K
1    Cond.        Result
2      0            Red
3      5            Green
4      10          Blue
5      25          Purple
....
21      1243      Chartreuse

You could then use

    =VLOOKUP(A1,$J$2:$K$21,2,TRUE)

etc.

In article <BHXmb.10024$xv4.13…@twister.austin.rr.com>,
"Gale Gorman" <ggorm…@houston.rr.com> wrote:

> I need to test a cell with about 20 different conditions
> like so:
>
> IF (A1 < 5)(A1 >= 5 and < 10) ....etc.
>
> What is the proper syntax for this?
>
> Would I be better off writing a function?  I read that a VBA
> function is MUCH slower but that is not really a problem.
>
> Gale Gorman
> Houston
>
> ,

Replies

None

In response to

multiple AND statements posted by Gale Gorman on Sun, 26 Oct 2003