nested functions in an "IF" statement

[samsebastian]

I am trying to get around this long formula by using defined names and defined formulas but I am not writing the formula correctly. I tried the lookup function and ran into problems with that. How exactly do you use lookup in this situation (or defined formulas if that is better):
=IF(A1>=4,IF(B1>=9,IF(C1>=8,IF(D1>=7,IF(E1>=1,IF(F1>=16,IF(G1>=9,IF(H1>=67,IF(I1>=8,IF(J1>=98,.2,0),0),0),0),0),0),0),0),0),0)
I want the value .2 returned if all arguments are met. The values for A1,B1, will change depending on the situation. thanks.

[Richard Buttrey]

Hi,

I thought there was a limit of 7 IF() functions in Excel so I'm surprised this is working. Are you using XL 2007 and has this limitation been removed perhaps. The formula you have would perhaps be better written as

Please Login or Register  to view this content.

Lookups are used where you have a single value that you want to find in a table, and return a corresponding value from another column (VLOOKUP), or row (HLOOKUP) in the same table. I don't think this applies in your case.

HTH

[VBA Noob]

Think you can do 8 if's that way as walkenbach demonstrates
http://www.j-walk.com/ss/excel/usertips/tip080.htm

VBA Noob

[samsebastian]

=if(and(b1>=6,c1>=9,d1>=8,e1>=1,f1>=5,g1>=22,h1>=22,i1>=24,j1>=12,k1>=4),0.2,0) Works! Thanks