Matt_Thompson
Board Regular
- Joined
- Apr 5, 2004
- Messages
- 101
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
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