Comparing Values with If Statement

[DBarker5]

This formula works,

=IF(AND(D2>=106%,D2<=110%,E2>=4),600,"NO")

but I want to add more conditions like

=IF(AND(D2>=101%,D2<=105%,E2>=3),400,"NO")

The problem is putting them together so that it works, I
have numerous other conditions, but when I figure out how
to put two together I should be good to go.

Basically am asking if the value matches these three
conditions give it a value if not move to the next three
conditions.

Any help would be appreciated.

 

[Dave R.]

You can put them together simply by adding the 2nd IF in where "NO" is in
the first statement;

=IF(AND(D2>=106%,D2<=110%,E2>=4),600,IF(AND(D2>=101%,D2<=105%,E2>=3),400,"NO
"))

This will work with 6 more IF statements-- there is a limit of 7 "nested" IF
statements, if that's not enough you can look at another way to solve it.

 

[Don Guillett]

try this but I'll bet you will run out of space and need a different
solution.
=IF(AND(D2>=106%,D2<=110%,E2>=4),600,IF(AND(D2>=101%,D2<=105%,E2>=3),400,"NO
"))
or this would be shorter. The trick is your are starting at the TOP and
working down with the 1st IF. Try adapting to your needs.
if(and(d2<106,e2>3),1,if(and(d2<105,ed>2),2,3))

 

[DBarker5]

Is there a limitation on how many statements you can put
in between, because it works up until a certain point and
then breaks?

 

[Dave R.]

I don't think there's a limit on those AND statements (maybe the limit would
be the limit on the contents of a cell), the other poster and I were
referring to the number of IFs you could stack together like

=IF(A1=B,IF(A2=C,IF(C4>20,IF(........

you could get away with
=IF(AND(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p...

if you wanted to.

 

[DBarker5]

I just looked this up and there is a limit of 7 functions
that you can nest. I am not understanding how your
formula fits my situation since I need 3 two values to
equal a criteria and this determines the value.

 

[Dave R.]

It may not apply to you. It was mentioned as a possibility if you were
headed towards that limit.

 

[DBarker5]

Any ideas?

 

[Dave R.]

Not at this point no, looks like Don had an idea though.