Forum Discussion
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 | ||
Year | 20 - 35 | 36-55 |
1 | 2,50 | 2,98 |
2 | 2,50 | 3,22 |
3 | 3 | 3,22 |
4 | 4 | 2 |
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