Logical functions

[Anil_Indian]

Dear
i m using excel logical functions for some calculation,
eg
=IF(AND(C2="EBRK",K2=1),0.0001,IF(AND(C2="EBRK",K2>1),J2/K2,IF(AND(F2>1,E2>1),0.0001,IF(AND(J2<-2000),0.0001,IF(AND(E2>100000),1,IF(AND(K2=10000),3,IF(AND(F2>1),2,(J2/K2)*4)))))))
but now i want to add some more logic but excel not allowes me.

plz tell me how i can add or simplyfy my formula

 

[Heera]

you cannot use more then 8 if conditions in excel.

 

[Sandy Mann]

Hiw many more conditions do you want to add? If just one more condition you
can combine two of your consecutive conditions that return the same value
Vis:

IF(AND(F2>1,E2>1),0.0001,IF(AND(J2<-2000),0.0001

into:

OR(AND(F2>1,E2>1),J2<-2000),0.0001

to give:

=IF(AND(C2="EBRK",K2=1),0.0001,IF(AND(C2="EBRK",K2>1),J2/K2,IF(OR(AND(F2>1,E2>1),J2<-2000),0.0001,IF(E2>100000,1,IF(K2=10000,3,IF(F2>1,2,IF(A1=100,100,J2/K2*4)))))))

which allows you to add another condition at the end, [ie the IF(A1=100]
Note that you don't need all the AND() that you were using.

If the order is not critical then you can add both the conditions in the
OR() to the first condition to give:

=IF(OR(AND(C2="EBRK",K2=1),AND(F2>1,E2>1),J2<-2000),0.0001,IF(AND(C2="EBRK",K2>1),J2/K2,IF(E2>100000,1,IF(K2=10000,3,IF(F2>1,2,IF(A1=100,100,IF(A1=200,200,J2/K2*4)))))))

Which allows you to add yet another condition, the IF(A1=200

Failing that you may be able to use a combination of MAX() or MIN() with
CHOOSE().

From the variety of conditions it looks to me as if it would be very easy to
get unexpected results depending on how your spreadsheet is laid out.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk