multiple AND statements

[Gale Gorman]

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





,

 

[Don Guillett]

try
=if(and(a1>5,a1<=10,1,2)

 

[Don Guillett]

forgot a )
=if(and(a1>5,a1<=10),1,2)

try
=if(and(a1>5,a1<=10,1,2)

 

[J.E. McGimpsey]

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.

 

[Gale Gorman]

Thanks. I was going to try this as soon as I finished with
my newsgroups.

I also am trying to write a VB function with case statements
to see which is most efficient.

Gale Gorman
Houston