multiple AND statements

  • Thread starter Thread starter Gale Gorman
  • Start date Start date
G

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





,
 
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.
 
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
 
Back
Top