how i can use more than 64 levels of nesting

[nskatoch]

=if(and(p1=1,b37=hpe!c5),hpe!c6,if(and(p1=1,b37=hpe!d5),hpe!d6,if(and(p1=1,b37=hpe!e5),cosa!e6,if(and(p1=1,b37=hpe!f5),h pe!f6,if(and(p1=1,b37=hpe!g5),hpe!g6,if(and(p1=2,b37=hpe!c5),hpe!c7,if(and(p1=2,b37=hpe!d5),hpe!d7,if(and(p1=2,b37=hpe!e 5),cosa!e7,if(and(p1=2,b37=hpe!f5),hpe!f7,if(and(p1=2,b37=hpe!g5),hpe!g7,if(and(p1=3,b37=hpe!c5),hpe!c8,if(and(p1=3,b37= hpe!d5),hpe!d8,if(and(p1=3,b37=hpe!e5),cosa!e8,if(and(p1=3,b37=hpe!f5),hpe!f8,if(and(p1=3,b37=hpe!g5),hpe!g8,if(and(p1=4 ,b37=hpe!c5),hpe!c9,if(and(p1=4,b37=hpe!d5),hpe!d9,if(and(p1=4,b37=hpe!e5),cosa!e9,if(and(p1=4,b37=hpe!f5),hpe!f9,if(and (p1=4,b37=hpe!g5),hpe!g9,if(and(p1=5,b37=hpe!c5),hpe!c10,if(and(p1=5,b37=hpe!d5),hpe!d10,if(and(p1=5,b37=hpe!e5),cosa!e1 0,if(and(p1=5,b37=hpe!f5),hpe!f10,if(and(p1=5,b37=hpe!g5),hpe!g10,if(and(p1=6,b37=hpe!c5),hpe!c11,if(and(p1=6,b37=hpe!d5 ),hpe!d11,if(and(p1=6,b37=hpe!e5),cosa!e11,if(and(p1=6,b37=hpe!f5),hpe!f11,if(and(p1=6,b37=hpe!g5),hpe!g11,if(and(p1=7,b 37=hpe!c5),hpe!c12,if(and(p1=7,b37=hpe!d5),hpe!d12,if(and(p1=7,b37=hpe!e5),cosa!e12,if(and(p1=7,b37=hpe!f5),hpe!f12,if(a nd(p1=7,b37=hpe!g5),hpe!g12,if(and(p1=8,b37=hpe!c5),hpe!c13,if(and(p1=8,b37=hpe!d5),hpe!d13,if(and(p1=8,b37=hpe!e5),cosa !e13,if(and(p1=8,b37=hpe!f5),hpe!f13,if(and(p1=8,b37=hpe!g5),hpe!g13,if(and(p1=9,b37=hpe!c5),hpe!c14,if(and(p1=9,b37=hpe !d5),hpe!d14,if(and(p1=9,b37=hpe!e5),cosa!e14,if(and(p1=9,b37=hpe!f5),hpe!f14,if(and(p1=9,b37=hpe!g5),hpe!g14,if(and(p1= 10,b37=hpe!c5),hpe!c15,if(and(p1=10,b37=hpe!d5),hpe!d15,if(and(p1=10,b37=hpe!e5),cosa!e15,if(and(p1=10,b37=hpe!f5),hpe!f 15,if(and(p1=10,b37=hpe!g5),hpe!g15,if(and(p1=11,b37=hpe!c5),hpe!c16,if(and(p1=11,b37=hpe!d5),hpe!d16,if(and(p1=11,b37=h pe!e5),cosa!e16,if(and(p1=11,b37=hpe!f5),hpe!f16,if(and(p1=11,b37=hpe!g5),hpe!g16,if(and(p1=12,b37=hpe!c5),hpe!c17,if(an d(p1=12,b37=hpe!d5),hpe!d17,if(and(p1=12,b37=hpe!e5),cosa!e17,if(and(p1=12,b37=hpe!f5),hpe!f17,if(and(p1=12,b37=hpe!g5), hpe!g17,if(and(p1=13,b37=hpe!c5),hpe!c18,if(and(p1=13,b37=hpe!d5),hpe!d18,if(and(p1=13,b37=hpe!e5),cosa!e18,if(and(p1=13 ,b37=hpe!f5),hpe!f18,-------------," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

[dispelthemyth]

I would immediately stop using the IF statement and rephrase your question so potentially a VBA or alternative formula can be devised

[shyy]

Going out on a limb and never tried it like this but try using name ranges.

[CROY1985]

This looks extremely inefficient. Looking at the formula you have entered so far:

you are looking to see if B37 appears in HPE!C5:G5
When P1 = 1 you want to return 1 row below, P2 = 2 rows below, P3=3 rows below etc

In which case you can use:

=INDEX(HPE!C:G,P1+1,MATCH(B37,HPE!C$5:G$5))


If that doesn't work a you want, as the above poster says, please rephrase your question: tell us the layout of your data and what you want to achieve.

[CROY1985]

Revised formula:


=INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!C5:G5,0))

Let us know how you get on. There is no need for such a complex if statement, when you are only testing 2 conditions (roll number and Category (sport, 1st aid, gardening, etc)

[ukmikeb]

Hi

Perhaps :-

=IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))

Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

hth

[nskatoch]

Revised formula:=INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!C5:G5,0))Let us know how you get on. There is no need for such a complex if statement, when you are only testing 2 conditions (roll number and Category (sport, 1st aid, gardening, etc)

Thank you very much sir. Your revised formula runs successfully

[syban]

Hi

Perhaps :-

=IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))

Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

hth

Hello, I would like to make a function that generates a result depending on type of measurement but I am limited by the 64 limitation of nested ifs. The H2 of my excel includes types of measurements : Box of 5 kilos so I have to multiply the I2 cell which has the quantity by 5 to get kilos, box of 30 kilos*30 and so on. I have many unit types (112) so how could I make this??? I really need this for my work. I am including a photo to see how I do it (I put the word function so u can see the function that I am using). I tries concatenate but it generates multiple values and I only need one.
EXAMPLE_zpsqrhvgkpy.jpg Photo by Konstantinos_Psilakis | Photobucket
Sorry in advance for my inexperience

[syban]

Hi

Perhaps :-

=IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))

Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

hth

Hello, I would like to make a function that generates a result depending on type of measurement but I am limited by the 64 limitation of nested ifs. The H2 of my excel includes types of measurements : Box of 5 kilos so I have to multiply the I2 cell which has the quantity by 5 to get kilos, box of 30 kilos*30 and so on. I have many unit types (112) so how could I make this??? I really need this for my work. I am including a photo to see how I do it (I put the word function so u can see the function that I am using). I tries concatenate but it generates multiple values and I only need one.
EXAMPLE_zpsqrhvgkpy.jpg Photo by Konstantinos_Psilakis | Photobucket
Sorry in advance for my inexperience

[shg]

Just make a table with all of the descriptions in one column and the relevant measure in the next, and use a lookup formula.

http://www.contextures.com/xlFunctions02.html