- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2003 October
- Re: multiple AND statements

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

>

> ,

None

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