" If " & " And " query

[avinashraste]

Dear All,

Is this multiple if and query possible :

If A2 is > 1000 and E2 > 5 then
D2
else C2

and

If A2 is <1000 and >100 and E2 > 2 then
D2
else C2

and

if A2 is < 100 and E2 > 0.5 then
D2
else C2

Can we make this formula for excel ?

Thanks & Regards

Avinash Raste

 

[AlphaFrog]

Try something like this...

=IF(OR(AND(A2>1000,E2>5), 
         AND(A2<1000,A2>100,E2>2), 
           AND(A2<100,E2>0.5)),C2,"")

 

[avinashraste]

Hi
Thanks for the reply friend,

I have tried and pasted the formula but it gave no result. The cell remains blank.

Avinash Raste

 

[avinashraste]

hi,

I'm sorry, my mistake. It does work but only thing is that it does not give result if the E2 is < 5 or less than 2 or less than 0.5, i mean less than condition is not proper...

please help

Avinash Raste

 

[AlphaFrog]

I'm sorry, my mistake. It does work but only thing is that it does not give result if the E2 is < 5 or less than 2 or less than 0.5, i mean less than condition is not proper...

I don't follow what criteria you now want. Originally you had asked for E2 > 5 now you want E2 < 5 ?

If you look at the structure if the formula, you should be able to modify it yourself.

=IF(OR(AND(A2>1000,E2>5),
AND(A2<1000,A2>100,E2>2),
AND(A2<100,E2>0.5)),C2,"")

The OR test if any one of the AND statements is true then the result is C2

The ANDs test if the all the criteria within () is true...
AND(A2>1000, E2>5) 'Both have to be true
OR
AND(A2<1000, A2>100, E2>2) 'All three have to be true
OR
AND(A2<100, E2>0.5) 'Both have to be true

 

[avinashraste]

Hello,

Thanks for the reply. I'm trying the way you have suggested. I will come back soon to reply. Thanks again friend.

Avinash Raste