Excel Function - limit to number of logical tests? 2

[GLENEE]

Hi
Is there a limit to the number of logical tests that can be applied to a function in an excel cell, for example the following stops after the 6th logical test i.e IF(AND(C14="L",E14="VL"),2

IF(AND(C14="VL",E14="VL"),1,IF(AND(C14="VL",E14="L"),4,IF(AND(C14="VL",E14="M"),10,IF(AND(C14="VL",E14="H"),12,IF(AND(C14="VL",E14="VH"),19,IF(AND(C14="L",E14="VL"),2, (it stops here) IF(AND(C14="L",E14="L"),7,IF(AND(C14="L",E14="M"),11,IF(AND(C14="L",E14="H"),17,IF(AND(C14="L",E14="VH"),20,IF(AND(C14="M",E14="VL"),3,IF(AND(C14="M",E14="L"),8,IF(AND(C14="M",E14="M"),14,IF(AND(C14="M",E14="H"),18,IF(AND(C14="M",E14="VH"),23,IF(AND(C14="H",E14="VL"),5,IF(AND(C14="H",E14="L"),9,IF(AND(C14="H",E14="M"),15,IF(AND(C14="H",E14="H"),21,IF(AND(C14="H",E14="VH"),24,IF(AND(C14="VH",E14="VL"),6,IF(AND(C14="VH",E14="L"),13,IF(AND(C14="VH",E14="M"),16,IF(AND(C14="VH",E14="H"),22,IF(AND(C14="VH",E14="VH"),25))))))))

I appreciate that it is not the most elegant way of doing this but i dont want the spreadsheet to contain VBA/Macros due to security and portability constraints. If any one knows a better way i would be very grateful. Thanks

 

[eti3nne]

I think the simple limit is 7 nested IFs.

You can see a possible solution to this though at this link

http://www.cpearson.com/Excel/nested.htm

Hope this helps.

 

[GlennUK]

Why don't you put the required values in a matrix, 5 by 5, for VL, L, M, H, VH vs same, and lookup the required row and column by using MATCHs, and then INDEX into the MATRIX for the number. The formula would look something like:

=INDEX(data_body,MATCH(C14,vertical_titles,0),MATCH(E14,horizontal_titles,0))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

You pick an item in 5x5 matrix depending on entry row and column. The other approach:

define arrays as names:
'_x':
={1,4,10,12,19;2,7,11,17,20;3,8,14,18,23;5,9,15,21,24;6,13,16,22,25}

'_r':
={"VL","L","M","H","VH"}

'_c':
={"VL";"L";"M";"H";"VH"}

and the final formula in cell:
=INDEX(_x,MATCH(C14,_r,0),MATCH(E14,_c,0))

combo

 

[GLENEE]

Thanks for the suggestions. I opted for the 5 x 5 matrix using the INDEX and MATCH, which worked a treat!