If AND Formula

[Matt_Thompson]

Hi,

I currently am struggling with a IF(AND(blah blah)) formula.

I have data in col A which is a number of 1 through to 5. This stands for :

1 = uncapped
2 = capped at 200
3 as above
4 as above
5 = 0

What I am trying to do is have a formula which will say:

If Col A = 1, return Col B
if Col A = 2 AND Col B is greater than 200, return 200 else return Col B
if Col A = 3 AND Col B is greater than 200, return 200 else return Col B
if Col A = 4 AND Col B is greater than 200, return 200 else return Col B
if Col A = 5 return 0

So far I can build the individual bits but when I try to put together =IF((AND(A7=4,B7>200),200,A7), if(AND(A7=4,B7>200),200,A7))

It does not like this but I'm not sure what I'm missing but I'm sure it's something stupid what with it being a Friday Afternoon!!

If anyone can point me in the right direction I'd be most grateful,

Cheers,

Matt

 

[sgremmo]

in italian in C1

=SE(A1=1;B1;SE(A1=5;0;SE(E(O(A1=2;A1=3;A1=4);B1>200);200;B1)))

in english probably

=IF(A1=1,B1,IF(A1=5,0,IF(E(O(A1=2,A1=3,A1=4),B1>200),200,B1)))

ciao

Simone

 

[NBVC]

Give this a try:

=IF(A1=5,0,IF(OR(A1=1,B1<200),B1,200))

What if A1 > 5 or = 0 or is blank? What happens?

 

[Aladin Akyurek]

=CHOOSE(MATCH(A7,{1,2,5},1),B7,MIN(200,B7),0)

 

[Matt_Thompson]

Thanks to the people who posted replies.

They worked great and have solved my problem.


thank you so much for your help,

Matt