Formula is getting stopped being colored and reference are being removed

[An Quala]

Hello,

I have written the below formula in the excel and it still continues but as soon as I enter the "2" at the end as you can see, it just stops showing all the colors in the formula that helps you understand it, it just becomes black and the colors from reference cells are also removed and as soon as I delete the this, it just becomes normal. Can you let me know what could possibly be happening?

Thank you

The formula is:

=IF($F2="";"";IF(AND('Control Panel'!$M$11="Turn On";$AU2>('Control Panel'!$P$11+$F2);$AN2>='Control Panel'!$R$11);$AD2*(1-'Control Panel'!$T$11);IF(AND('Control Panel'!$M$12="Turn On";$AU2>('Control Panel'!$P$12+$F2);$AN2>='Control Panel'!$R$12);$AD2*(1-'Control Panel'!$T$12);IF(AND('Control Panel'!$M$13="Turn On";$AU2>=('Control Panel'!$P$13+$F2);$AN2>='Control Panel'!$R$13);$AD2*(1-'Control Panel'!$T$13);IF(AND('Control Panel'!$M$14="Turn On";$AU2>('Control Panel'!$P$14+$F2);$AN2>='Control Panel'!$R$14);$AD2-'Control Panel'!$T$14;IF(AND('Control Panel'!$M$15="Turn On";$AU2>('Control Panel'!$P$15+$F2);$AN2>='Control Panel'!$R$15);$AD2-'Control Panel'!$T$15;IF(AND('Control Panel'!$M$16="Turn On";$AU2>=('Control Panel'!$P$16+$F2);$AN2>='Control Panel'!$R$16);$AD2-'Control Panel'!$T$16;IF(AND('Control Panel'!$M$20="Turn On";$AU2<=($F2-'Control Panel'!$P$20);$AN2>='Control Panel'!$R$20;$AR2<>"0");$AD2*(1+'Control Panel'!$T$20);IF(AND('Control Panel'!$M$21="Turn On";$AU2<($F2-'Control Panel'!$P$21);$AN2>='Control Panel'!$R$21;$AR2<>"0");$AD2*(1+'Control Panel'!$T$21);IF(AND('Control Panel'!$M$22="Turn On";$AU2<($F2-'Control Panel'!$P$22);$AN2>='Control Panel'!$R$22;$AR2<>"0");$AD2*(1+'Control Panel'!$T$22);IF(AND('Control Panel'!$M$23="Turn On";$AU2<=($F2-'Control Panel'!$P$23);$AN2>='Control Panel'!$R$23;$AR2<>"0");$AD2+'Control Panel'!$T$23;IF(AND('Control Panel'!$M$24="Turn On";$AU2<($F2-'Control Panel'!$P$24);$AN2>='Control Panel'!$R$24;$AR2<>"0");$AD2+'Control Panel'!$T$24;IF(AND('Control Panel'!$M$25="Turn On";$AU2<($F2-'Control Panel'!$P$25);$AN2>='Control Panel'!$R$25;$AR2<>"0");$AD2+'Control Panel'!$T$25;IF(AND('Control Panel'!$M$29="Turn On";$AV2<=((1/$F2)-'Control Panel'!$P$29);$AN2>='Control Panel'!$R$29);$AD2*(1-'Control Panel'!$T$29);IF(AND('Control Panel'!$M$30="Turn On";$AV2<((1/$F2)-'Control Panel'!$P$30);$AN2>='Control Panel'!$R$30);$AD2*(1-'Control Panel'!$T$30);IF(AND('Control Panel'!$M$31="Turn On";$AV2<((1/$F2)-'Control Panel'!$P$31);$AN2>='Control Panel'!$R$31);$AD2*(1-'Control Panel'!$T$31);IF(AND('Control Panel'!$M$32="Turn On";$AV2<((1/$F2)-'Control Panel'!$P$32);$AN2>='Control Panel'!$R$32);$AD2-'Control Panel'!$T$32;IF(AND('Control Panel'!$M$33="Turn On";$AV2<((1/$F2)-'Control Panel'!$P$33);$AN2>='Control Panel'!$R$33);$AD2-'Control Panel'!$T$33;IF(AND('Control Panel'!$M$34="Turn On";$AV2<((1/$F2)-'Control Panel'!$P$34);$AN2>='Control Panel'!$R$34);$AD2-'Control Panel'!$T$34;IF(AND('Control Panel'!$M$38="Turn On";$AV2>((1/$F2)+'Control Panel'!$P$38);$AN2>='Control Panel'!$R$38;$AR2<>"0");$AD2*(1+'Control Panel'!$T$38);IF(AND('Control Panel'!$M$39="Turn On";$AV2>((1/$F2)+'Control Panel'!$P$39);$AN2>='Control Panel'!$R$39;$AR2<>"0");$AD2*(1+'Control Panel'!$T$39);IF(AND('Control Panel'!$M$40="Turn On";$AV2>=((1/$F2)+'Control Panel'!$P$40);$AN2>='Control Panel'!$R$40;$AR2<>"0");$AD2*(1+'Control Panel'!$T$40);IF(AND('Control Panel'!$M$41="Turn On";$AV2>((1/$F2)+'Control Panel'!$P$41);$AN2>='Control Panel'!$R$41;$AR2<>"0");$AD2+'Control Panel'!$T$41;IF(AND('Control Panel'!$M$42="Turn On";$AV2>((1/$F2)+'Control Panel'!$P$42);$AN2>='Control Panel'!$R$42;$AR2<>"0");$AD2+'Control Panel'!$T$42;IF(AND('Control Panel'!$M$43="Turn On";$AV2>=((1/$F2)+'Control Panel'!$P$43);$AN2>='Control Panel'!$R$43;$AR2<>"0");$AD2+'Control Panel'!$T$43;IF(AND('Control Panel'!$M$47="Turn On";$AM2<='Control Panel'!$P$47;$AN2<='Control Panel'!$R$47);$AD2*(1+'Control Panel'!$T$47);IF(AND('Control Panel'!$M$48="Turn On";$AM2<='Control Panel'!$P$48;$AN2<='Control Panel'!$R$48);$AD2*(1+'Control Panel'!$T$48);IF(AND('Control Panel'!$M$49="Turn On";$AM2<='Control Panel'!$P$49;$AN2<='Control Panel'!$R$49);$AD2*(1+'Control Panel'!$T$49);IF(AND('Control Panel'!$M$50="Turn On";$AM2<='Control Panel'!$P$50;$AN2<='Control Panel'!$R$50);$AD2+'Control Panel'!$T$50;IF(AND('Control Panel'!$M$51="Turn On";$AM2<='Control Panel'!$P$51;$AN2<='Control Panel'!$R$51);$AD2+'Control Panel'!$T$51;IF(AND('Control Panel'!$M$52="Turn On";$AM2<='Control Panel'!$P$52;$AN2<='Control Panel'!$R$52);$AD2+'Control Panel'!$T$52;IF(AND('Control Panel'!$Y$11="Turn On";$AR2<='Control Panel'!$AB$11;$AP2>='Control Panel'!$AD$11;$AN2>='Control Panel'!$AF$11);$AD2*(1-'Control Panel'!$AH$11);IF(AND($A$2

 

[SanjayGMusafir]

If I'm not wrong you can not nest more than 16 IF within an IF Function

Also, Probably there is limit to number of characters in a cell

 

[An Quala]

If I'm not wrong you can not nest more than 16 IF within an IF Function

Also, Probably there is limit to number of characters in a cell

For example this formula, it has 30 IF functions, and still works perfectly fine.


=IFERROR(MIN(MAX(ROUND(IF($F2="";"";IF(AND('Control Panel'!$Q$11="Turn On";$AT2<='Control Panel'!$T$11;$AN2>='Control Panel'!$V$11;$AU2>$F2);$AY2;IF(AND('Control Panel'!$Q$13="Turn On";$AR2<='Control Panel'!$T$13;$AN2>='Control Panel'!$V$13);$AY2;IF(AND('Control Panel'!$Q$20="Turn On";$AU2>='Control Panel'!$T$20;$AN2>='Control Panel'!$V$20);$AD2*(1-'Control Panel'!$X$20);IF(AND('Control Panel'!$Q$21="Turn On";$AU2>='Control Panel'!$T$21;$AN2>='Control Panel'!$V$21);$AD2-'Control Panel'!$X$21;IF(AND('Control Panel'!$Q$23="Turn On";$AU2>('Control Panel'!$T$23+$F2);$AN2>='Control Panel'!$V$23);$AD2*(1-'Control Panel'!$X$23);IF(AND('Control Panel'!$Q$24="Turn On";$AU2>('Control Panel'!$T$24+$F2);$AN2>='Control Panel'!$V$24);$AD2*(1-'Control Panel'!$X$24);IF(AND('Control Panel'!$Q$25="Turn On";$AU2>('Control Panel'!$T$25+$F2);$AN2>='Control Panel'!$V$25);$AD2*(1-'Control Panel'!$X$25);IF(AND('Control Panel'!$Q$26="Turn On";$AU2>('Control Panel'!$T$26+$F2);$AN2>='Control Panel'!$V$26);$AD2-'Control Panel'!$X$26;IF(AND('Control Panel'!$Q$27="Turn On";$AU2>('Control Panel'!$T$27+$F2);$AN2>='Control Panel'!$V$27);$AD2-'Control Panel'!$X$27;IF(AND('Control Panel'!$Q$28="Turn On";$AU2>('Control Panel'!$T$28+$F2);$AN2>='Control Panel'!$V$28);$AD2-'Control Panel'!$X$28;IF(AND('Control Panel'!$Q$30="Turn On";$AU2<($F2-'Control Panel'!$T$30);$AN2>='Control Panel'!$V$30;$AR2<>"0");$AD2*(1+'Control Panel'!$X$30);IF(AND('Control Panel'!$Q$31="Turn On";$AU2<($F2-'Control Panel'!$T$31);$AN2>='Control Panel'!$V$31;$AR2<>"0");$AD2*(1+'Control Panel'!$X$31);IF(AND('Control Panel'!$Q$32="Turn On";$AU2<($F2-'Control Panel'!$T$32);$AN2>='Control Panel'!$V$32;$AR2<>"0");$AD2*(1+'Control Panel'!$X$32);IF(AND('Control Panel'!$Q$33="Turn On";$AU2<($F2-'Control Panel'!$T$33);$AN2>='Control Panel'!$V$33;$AR2<>"0");$AD2+'Control Panel'!$X$33;IF(AND('Control Panel'!$Q$34="Turn On";$AU2<($F2-'Control Panel'!$T$34);$AN2>='Control Panel'!$V$34;$AR2<>"0");$AD2+'Control Panel'!$X$34;IF(AND('Control Panel'!$Q$35="Turn On";$AU2<($F2-'Control Panel'!$T$35);$AN2>='Control Panel'!$V$35;$AR2<>"0");$AD2+'Control Panel'!$X$35;IF(AND('Control Panel'!$Q$37="Turn On";$AU2>='Control Panel'!$T$37;$AO2<='Control Panel'!$V$37;$AN2>='Control Panel'!X$37);$AD2*(1-'Control Panel'!$Z$37);IF(AND('Control Panel'!$Q$38="Turn On";$AU2>='Control Panel'!$T$38;$AO2<='Control Panel'!$V$38;$AN2>='Control Panel'!X$38);$AD2-'Control Panel'!$Z$38;IF(AND('Control Panel'!$Q$39="Turn On";$AU2<'Control Panel'!$T$39;$AO2>'Control Panel'!$V$39;$AN2>='Control Panel'!X$39);$AD2*(1+'Control Panel'!$Z$39);IF(AND('Control Panel'!$Q$40="Turn On";$AU2<'Control Panel'!$T$40;$AO2>'Control Panel'!$V$40;$AN2>='Control Panel'!X$40);$AD2+'Control Panel'!$Z$40;IF(AND('Control Panel'!$Q$42="Turn On";$AR2<='Control Panel'!$T$42;$AP2>='Control Panel'!$V$42;$AN2>='Control Panel'!$X$42);$AD2*(1-'Control Panel'!$Z$42);IF(AND('Control Panel'!$Q$43="Turn On";$AR2<='Control Panel'!$T$43;$AP2>='Control Panel'!$V$43;$AN2>='Control Panel'!$X$43);$AD2-'Control Panel'!$Z$43;IF(AND('Control Panel'!$Q$45="Turn On";$AM2<='Control Panel'!$T$45;$AN2<='Control Panel'!$V$45);$AD2*(1+'Control Panel'!$X$45);IF(AND('Control Panel'!$Q$46="Turn On";$AM2<='Control Panel'!$T$46;$AN2<='Control Panel'!$V$46);$AD2*(1+'Control Panel'!$X$46);IF(AND('Control Panel'!$Q$47="Turn On";$AM2<='Control Panel'!$T$47;$AN2<='Control Panel'!$V$47);$AD2*(1+'Control Panel'!$X$47);IF(AND('Control Panel'!$Q$48="Turn On";$AM2<='Control Panel'!$T$48;$AN2<='Control Panel'!$V$48);$AD2+'Control Panel'!$X$48;IF(AND('Control Panel'!$Q$49="Turn On";$AM2<='Control Panel'!$T$49;$AN2<='Control Panel'!$V$49);$AD2+'Control Panel'!$X$49;IF(AND('Control Panel'!$Q$50="Turn On";$AM2<='Control Panel'!$T$50;$AN2<='Control Panel'!$V$50);$AD2+'Control Panel'!$X$50;"")))))))))))))))))))))))))))));2);$AY2);$AZ2);"")