Forum Discussion

Akekew's avatar
Akekew
Copper Contributor
Aug 30, 2023

Need for more than 64 levels of nested - Simplify formula

Hai Guys,

 

i have not experience with that case, so i need your help with this, i have very long formula more than 64 levels of nested, and i need to simplify the formula, an di will copy and paste my formule here but although it might look very complicated, it is pretty much the same thing over and over again but with different values. I need to find a way to simplify it, so I don't get the error of 64 levels of nested if I add more.and my formula is from some of table that use vlookup and hlookup  Here is the example table:

 Age 
Year20 - 3536-55
12,502,98
22,503,22
333,22
442

 and here is my formula

 

=IF(AND(AD7>=20;AD7<=55;AM7=1);2,509;

IF(AND(AD7>=20;AD7<=55;AM7=2);5,000;

IF(AND(AD7>=20;AD7<=55;AM7=3);7,509;

IF(AND(AD7>=20;AD7<=55;AM7=4);10,018;

IF(AND(AD7>=20;AD7<=55;AM7=5);12,509;

IF(AND(AD7>=20;AD7<=55;AM7=6);15,018;

IF(AND(AD7>=20;AD7<=55;AM7=7);17,527;

IF(AND(AD7>=20;AD7<=55;AM7=8);20,017;

IF(AND(AD7>=20;AD7<=55;AM7=9);22,526;

IF(AND(AD7>=20;AD7<=55;AM7=10);25,053;

IF(AND(AD7>=20;AD7<=55;AM7=11);27,544;

IF(AND(AD7>=20;AD7<=55;AM7=12);30,053;

IF(AND(AD7>=20;AD7<=55;AM7=13);32,562;

IF(AND(AD7>=20;AD7<=55;AM7=14);35,053;

IF(AND(AD7>=20;AD7<=55;AM7=15);37,562;

IF(AND(AD7>=56;AD7<=64;AM7=1);4,819;

IF(AND(AD7>=56;AD7<=64;AM7=2);10,018;

IF(AND(AD7>=56;AD7<=64;AM7=3);15,018;

IF(AND(AD7>=56;AD7<=64;AM7=4);20,017;

IF(AND(AD7>=56;AD7<=64;AM7=5);25,035;

IF(AND(AD7>=56;AD7<=64;AM7=6);30,035;

IF(AND(AD7>=56;AD7<=64;AM7=7);35,035;

IF(AND(AD7>=56;AD7<=64;AM7=8);40,053;

IF(AND(AD7>=56;AD7<=64;AM7=9);45,053;

IF(AND(AD7>=56;AD7<=64;AM7=10);52,687;

IF(AND(AD7>=56;AD7<=64;AM7=11);53,390;

IF(AND(AD7>=56;AD7<=64;AM7=12);53,432;

IF(AND(AD7>=56;AD7<=64;AM7=13);55,495;

IF(AND(AD7>=56;AD7<=64;AM7=14);57,870;

IF(AND(AD7>=56;AD7<=64;AM7=15);60,032;

IF(AND(AD7>=65;AD7<=69;AM7=1);4,819;

IF(AND(AD7>=65;AD7<=69;AM7=2);10,018;

IF(AND(AD7>=65;AD7<=69;AM7=3);15,018;

IF(AND(AD7>=65;AD7<=69;AM7=4);20,017

;IF(AND(AD7>=65;AD7<=69;AM7=5);25,035;

IF(AND(AD7>=65;AD7<=69;AM7=6);30,035;

IF(AND(AD7>=65;AD7<=69;AM7=7);35,035;

IF(AND(AD7>=65;AD7<=69;AM7=8);40,053;

IF(AND(AD7>=65;AD7<=69;AM7=9);45,053;

IF(AND(AD7>=65;AD7<=69;AM7=10);52,687;

IF(AND(AD7=70;AM7=1);10,88;IF(AND(AD7=71;AM7=1);11,05;IF(AND(AD7=72;AM7=1);12,48;IF(AND(AD7=73;AM7=1);13,33;IF(AND(AD7=74;AM7=1);14,67;IF(AND(AD7>=75;AD7<=79;AM7=1);48,96;IF(AND(AD7=70;AM7=2);21,96;IF(AND(AD7=71;AM7=2);22,10;IF(AND(AD7=72;AM7=2);23,95;IF(AND(AD7=73;AM7=2);25,65;IF(AND(AD7=74;AM7=2);90,38;IF(AND(AD7>=75;AD7<=78;AM7=2);97,92;

IF(AND(AD7=70;AM7=3);32,64;IF(AND(AD7=71;AM7=3);34,15;IF(AND(AD7=72;AM7=3);36,43;IF(AND(AD7=73;AM7=3);135,49;IF(AND(AD7=74;AM7=3);139,23;IF(AND(AD7>=75;AD7<=77;AM7=3);143,39;

IF(AND(AD7=70;AM7=4);43,52;IF(AND(AD7=71;AM7=4);46,20;IF(AND(AD7=72;AM7=4);161,56;IF(AND(AD7=73;AM7=4);173,52;IF(AND(AD7=74;AM7=4);178,31;IF(AND(AD7>=75;AD7<=76;AM7=4);190,40;

IF(AND(AD7=70;AM7=5);57,40;IF(AND(AD7=71;AM7=5);191,79;IF(AND(AD7=72;AM7=5);199,17;IF(AND(AD7=73;AM7=5);213,93;IF(AND(AD7=74;AM7=5);219,83;IF(AND(AD7=75;AM7=5);238,00;

IF(AND(AD7=70;AM7=6);217,87;IF(AND(AD7=71;AM7=6);221,28;IF(AND(AD7=72;AM7=6);229,79;IF(AND(AD7=73;AM7=6);246,81;IF(AND(AD7=74;AM7=6);253,62;

IF(AND(AD7=70;AM7=7);243,40;IF(AND(AD7=71;AM7=7);247,20;IF(AND(AD7=72;AM7=7);256,71;IF(AND(AD7=73;AM7=7);275,72;

IF(AND(AD7=70;AM7=8);268,58;IF(AND(AD7=71;AM7=8);272,78;IF(AND(AD7=72;AM7=8);283,27;

IF(AND(AD7=70;AM7=9);291,66;IF(AND(AD7=71;AM7=9);296,22;

IF(AND(AD7=70;AM7=10);303,90;FALSE)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

I appreciate your help in advance!

 

Thank you,

 

NB: sorry if my english bad

 

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    I would suggest to use a lookup table then use XLOOKUP() or FILTER() function which will be too simplified.
    • Akekew's avatar
      Akekew
      Copper Contributor
      so i need to spreed the age?ok thank you for the advice i will try xloopup fir the formula

Resources