IF/AND/OR

  • Thread starter Thread starter ALLDREAMS
  • Start date Start date
A

ALLDREAMS

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!!
 
Hi

=IF(OR(A1>90%,A1<=10%),"PIVNUM1",IF(OR(A1>80%,A1<=20%),"PIVOT2",IF(OR(A1>70%
,A1<=30%),"PIVOT3",IF(OR(A1>60%,A1<=40%),"PIVOT4","PIVOT5"))))

assuming nothing is ever higher than 100% or less than 0% and your value
appears in A1 -
Cheers
JulieD
 
Hi AllDreams!


This isn't right:
<=70% and >60% OR <=40% AND >50% THEN "PIVOT4, IF

How can the test cell be <=40 and >50% ?

I'd use a VLOOKUP for this type of problem.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I think this does them all:-

With your value in A1

=LOOKUP(IF(A1<0.5,1.1-A1,A1),{0.5,0.6,0.7,0.8,0.9,1;"P5","P5","P4","P3","P2","P1
"})
 
-----Original Message-----
Hi AllDreams!


This isn't right:
<=70% and >60% OR <=40% AND >50% THEN "PIVOT4, IF

How can the test cell be <=40 and >50% ?

I'd use a VLOOKUP for this type of problem.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.



.
Click to expand...
 
Hi AllDreams!

Here's what you asked for:

=IF(OR(AND(A1<=100%,A1>90%),AND(A1<=10%,A1>0%)),"PIVNUM1",IF(OR(AND(A1
30%)),"PIVOT4",IF(OR(AND(A1<=60%,A1>=50%),AND(A1<=50%,A1>40%)),"PIVOT
Click to expand...
5")))))

I had to vary the last condition because you missed out <=50%, >40%

But the following might be a better approach:

=VLOOKUP(A1,$F$1:$G$10,2)

In F1:F10 I have
0.01%
10.01%
20.01%
30.01%
40.01%
50.01%
60.01%
70.01%
80.01%
90.01%

In G1:G10 I have
PIVNUM1
PIVOT2
PIVOT3
PIVOT4
PIVOT5
PIVOT5
PIVOT4
PIVOT3
PIVOT2
PIVNUM1


You could put these internally in a formula but I prefer to keep my
VLOOKUP tables in a separate range which I can edit more easily.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman

agree with you that a vlookup might be neater but
couldn't the IF statement be simplified similar to the example i provided in
my response?
or can you see a fault in my statement?

Cheers
JulieD
 
Hi

How about
=LOOKUP((A1<0.5)+A1,{0.5,0.6,0.7,0.8,0.9,1;"P5","P5","P4","P3","P2","P1"})
 
Hi JulieD!

Agreed that your approach is *much* better if going the IF function
route. I was making the mistake of giving OP what he asked for, rather
than what he needed <vbg>. I got distracted by errors in the
information.

But if he changed PIVNUM1 to PIVOT1 you could use:

="PIVOT"&IF(A1<=50%,INT((A1-1%)*10)+1,INT((1-A1)*10)+1)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
now that's a "neat" solution ... :)
very impressed given the time of day (or should i say night)
 
Hi Arvi - Don't think it will work on the ones below 0.5 will it, as it gives
the following for the lookup value:-

A1 Lookup Value
0.9 (0.0+0.9) = 0.9
0.8 (0.0+0.8) = 0.8
0.7 (0.0+0.7) = 0.7
0.6 (0.0+0.6) = 0.6
0.5 (0.0+0.5) = 0.5
0.4 (1.0+0.4) = 1.4 <<
0.3 (1.0+0.3) = 1.3 <<
0.2 (1.0+0.2) = 1.2 <<
0.1 (1.0+0.1) = 1.1 <<
 
Hi

You are right - I didn't think it over throughly.

=LOOKUP((A1<0.5)*(1-A1)+A1,{0.5,0.6,0.7,0.8,0.9,1;"P5","P5","P4","P3","P2","
P1"})
will work of course, but I don't see any gainings compared with IF()
anymore.
 
Hi JulieD!

Not quite so neat as it only works for exact percentages although you
could use:

="PIVOT"&IF(A91<=50%,INT((A91-0.0000000001%)*10)+1,INT((1-A91)*10)+1)

And re: "given the time of day (or should i say night)"

Always work better late at night or early morning.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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 ( O
(AND ( A1 <=90, A1 >80),AND ( A1 <=20, A1 >10)),"PIVOT2", IF ( OR (AN
( A1 said:
=50),"PIVOT5","ERROR")
Click to expand...

I think that the >0% should be >=0 ??

Hope this runs OK ... as usual no time to test . I agree with othe
users that its by far easier to use a lookup as its much less prone t
input errror
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top