Re: IF/AND/OR

From: Norman Harker (njharker_at_optusnet.com.au)
Date: 03/01/04 

Date: Tue, 2 Mar 2004 01:20:23 +1100

Hi Dave!

Try:
=IF(OR(AND(A1<=100,A1>90),AND(A1<=10,A1>=0)),"PivNUM1",IF(OR(AND(A1<=9
0,A1>80),AND(A1<=20,A1>10)),"PIVOT2",IF(OR(AND(A1<=80,A1>70),AND(A1<=3
0,A1>20)),"PIVOT3",IF(OR(AND(A1<=70,A1>60),AND(A1<=40,A1>30)),"PIVOT4"
,IF(OR(AND(A1<=60,A1>=50),AND(A1<=50,A1>40)),"PIVOT5","ERROR")))))

You had a lot of spaces but I don't think that made a difference. You
also missed out covering A1<=50, A1>40. The trouble with A1>=0 is that
it will cover A1 is an empty cell. So I'd be inclined to make it:

=IF(A1="","",IF(OR(AND(A1<=100,A1>90),AND(A1<=10,A1>=0)),"PivNUM1",IF(
OR(AND(A1<=90,A1>80),AND(A1<=20,A1>10)),"PIVOT2",IF(OR(AND(A1<=80,A1>7
0),AND(A1<=30,A1>20)),"PIVOT3",IF(OR(AND(A1<=70,A1>60),AND(A1<=40,A1>3
0)),"PIVOT4",IF(OR(AND(A1<=60,A1>=50),AND(A1<=50,A1>40)),"PIVOT5","ERR
OR"))))))

The initial IF function returns a "" if the cell is empty or contains
a formula returning "". The formula still returns PivNUM1 if A1 is 0.
You may not want this so you can change A1>=0 to A1>0.

Testing isn't too difficult. Just set up a column A1:A103 and fill
with the series 0-102. Enter the formula in A1 and copy down. Then
blank out say A102 to establish what happens with a blank cell. And
chuck some text in A103. 

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Dave Wilkinson >" <<Dave.Wilkinson.12fyim@excelforum-nospam.com>
wrote in message news:Dave.Wilkinson.12fyim@excelforum-nospam.com...
> IF/AND/OR
> could you show me the correct syntax for this condition
> using if/and/or functions
>
> IF <=100% and >90% OR <=10% and >0% then "PIVNUM1", IF
> <=90% and >80% OR <=20% AND >10% THEN "PIVOT2", IF
> <=80% and >70% OR <=30% AND >20% THEN "PIVOT3", IF
> <=70% and >60% OR <=40% AND >50% THEN "PIVOT4, IF
> <=60% and >=50% THEN "PIVOT5"
>
> Your help is much appreciated!!
>
> Assume that criteria is located in A1
>
> If(OR(AND(A1 <=100 , A1>90), AND(A1 <= 10, A1 >0)) , "PivNUM1", IF
( OR
> (AND ( A1 <=90, A1 >80),AND ( A1 <=20, A1 >10)),"PIVOT2",  IF ( OR
(AND
> ( A1 <=80, A1 >70),AND ( A1 <=30, A1 >20)),"PIVOT3", IF ( OR (AND
( A1
> <=70, A1 >60),AND ( A1 <=40, A1 >50)),"PIVOT4", if( AND(A1 <=60, A1
> >=50),"PIVOT5","ERROR")
>
> I think that the >0% should be >=0 ??
>
> Hope this runs OK ... as usual no time to test . I agree with other
> users that its by far easier to use a lookup as its much less prone
to
> input errror.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>