Getting error message "formula too long"

Question

This is the first time that I have this problem - the formula in questions is as follows:

=(INDEX(Model!$B$57:$B$61,Calculations!B$2)*IF(ISERROR(MATCH(Data!$B2,Model!$A$75:$A$76,0)),1,INDEX(Model!B$75:B$76,MATCH(Data!$B2,Model!$A$75:$A$76,0)))*IF(ISERROR(MATCH(Data!$C2,Model!$A$80:$A$81,0)),1,INDEX(Model!B$80:B$81,MATCH(Data!$C2,Model!$A$80:$A$81,0)))*IF(ISERROR(MATCH(Data!$D2,Model!$A$85:$A$86,0)),1,INDEX(Model!B$85:B$86,MATCH(Data!$D2,Model!$A$85:$A$86,0)))*IF(ISERROR(MATCH(Data!$E2,Model!$A$90:$A$91,0)),1,INDEX(Model!B$90:B$91,MATCH(Data!$E2,Model!$A$90:$A$91,0)))*IF(ISERROR(MATCH(Data!$F2,Model!$A$95:$A$96,0)),1,INDEX(Model!B$95:B$96,MATCH(Data!$F2,Model!$A$95:$A$96,0)))*IF(ISERROR(MATCH(Data!$G2,Model!$A$100:$A$101,0)),1,INDEX(Model!B$100:B$101,MATCH(Data!$G2,Model!$A$100:$A$101,0)))*IF(ISERROR(MATCH(Data!$G2,Model!$A$100:$A$101,0)),1,INDEX(Model!B$100:B$101,MATCH(Data!$G2,Model!$A$100:$A$101,0)))*IF(ISERROR(MATCH(Data!$I2,Model!$A$110:$A$111,0)),1,INDEX(Model!B$110:B$111,MATCH(Data!$I2,Model!$A$110:$A$111,0)))*IF(ISERROR(MATCH(Data!$J2,Model!$A$115:$A$116,0)),1,INDEX(Model!B$115:B$116,MATCH(Data!$J2,Model!$A$115:$A$116,0)))*IF(ISERROR(MATCH(Data!$K2,Model!$A$120:$A$121,0)),1,INDEX(Model!B$120:B$121,MATCH(Data!$K2,Model!$A$120:$A$121,0)))*IF(ISERROR(MATCH(Data!$L2,Model!$A$125:$A$125,0)),1,INDEX(Model!B$125:B$126,MATCH(Data!$L2,Model!$A$125:$A$126,0)))*IF(ISERROR(MATCH(Data!$M2,Model!$A$130:$A$131,0)),1,INDEX(Model!B$130:B$131,MATCH(Data!$M2,Model!$A$130:$A$131,0)))*IF(ISERROR(MATCH(Data!$N2,Model!$A$135:$A$136,0)),1,INDEX(Model!B$135:B$136,MATCH(Data!$N2,Model!$A$135:$A$136,0)))*IF(ISERROR(MATCH(Data!$O2,Model!$A$140:$A$141,0)),1,INDEX(Model!B$140:B$141,MATCH(Data!$O2,Model!$A$140:$A$141,0)))*IF(ISERROR(MATCH(Data!$P2,Model!$A$145:$A$146,0)),1,INDEX(Model!B$145:B$146,MATCH(Data!$P2,Model!$A$145:$A$146,0)))*IF(ISERROR(MATCH(Data!$Q2,Model!$A$150:$A$151,0)),1,INDEX(Model!B$150:B$151,MATCH(Data!$Q2,Model!$A$150:$A$151,0)))*IF(ISERROR(MATCH(Data!$R2,Model!$A$155:$A$156,0)),1,INDEX(Model!B$155:B$156,MATCH(Data!$R2,Model!$A$155:$A$156,0)))*IF(ISERROR(MATCH(Data!$S2,Model!$A$160:$A$161,0)),1,INDEX(Model!B$160:B$161,MATCH(Data!$S2,Model!$A$160:$A$161,0)))*IF(ISERROR(MATCH(Data!$T2,Model!$A$165:$A$166,0)),1,INDEX(Model!B$165:B$166,MATCH(Data!$T2,Model!$A$165:$A$166,0)))*IF(ISERROR(MATCH(Data!$U2,Model!$A$170:$A$171,0)),1,INDEX(Model!B$170:B$171,MATCH(Data!$U2,Model!$A$170:$A$171,0)))*IF(ISERROR(MATCH(Data!$V2,Model!$A$175:$A$176,0)),1,INDEX(Model!B$175:B$176,MATCH(Data!$V2,Model!$A$175:$A$176,0)))*IF(ISERROR(MATCH(Data!$W2,Model!$A$180:$A$181,0)),1,INDEX(Model!B$180:B$181,MATCH(Data!$W2,Model!$A$180:$A$181,0)))*IF(ISERROR(MATCH(Data!$X2,Model!$A$185:$A$186,0)),1,INDEX(Model!B$185:B$186,MATCH(Data!$X2,Model!$A$185:$A$186,0)))*IF(ISERROR(MATCH(Data!$X2,Model!$A$185:$A$186,0)),1,INDEX(Model!B$185:B$186,MATCH(Data!$X2,Model!$A$185:$A$186,0)))*IF(ISERROR(MATCH(Data!$Z2,Model!$A$195:$A$196,0)),1,INDEX(Model!B$195:B$196,MATCH(Data!$Z2,Model!$A$195:$A$196,0)))*IF(ISERROR(MATCH(Data!$AA2,Model!$A$200:$A$201,0)),1,INDEX(Model!B$200:B$201,MATCH(Data!$AA2,Model!$A$200:$A$201,0)))*IF(ISERROR(MATCH(Data!$AB2,Model!$A$205:$A$206,0)),1,INDEX(Model!B$205:B$206,MATCH(Data!$AB2,Model!$A$205:$A$206,0)))*IF(ISERROR(MATCH(Data!$AC2,Model!$A$210:$A$211,0)),1,INDEX(Model!B$210:B$211,MATCH(Data!$AC2,Model!$A$210:$A$211,0)))*IF(ISERROR(MATCH(Data!$AD2,Model!$A$215:$A$216,0)),1,INDEX(Model!B$215:B$216,MATCH(Data!$AD2,Model!$A$215:$A$216,0)))*IF(ISERROR(MATCH(Data!$AE2,Model!$A$220:$A$221,0)),1,INDEX(Model!B$220:B$221,MATCH(Data!$AE2,Model!$A$220:$A$221,0)))*IF(ISERROR(MATCH(Data!$AF2,Model!$A$225:$A$226,0)),1,INDEX(Model!B$225:B$226,MATCH(Data!$AF2,Model!$A$220:$A$226,0)))*IF(ISERROR(MATCH(Data!$AG2,Model!$A$230:$A$231,0)),1,INDEX(Model!B$230:B$231,MATCH(Data!$AG2,Model!$A$230:$A$231,0)))*IF(ISERROR(MATCH(Data!$AH2,Model!$A$235:$A$236,0)),1,INDEX(Model!B$235:B$236,MATCH(Data!$AH2,Model!$A$235:$A$236,0)))*IF(ISERROR(MATCH(Data!$AI2,Model!$A$240:$A$241,0)),1,INDEX(Model!B$240:B$241,MATCH(Data!$AI2,Model!$A$240:$A$241,0)))*IF(ISERROR(MATCH(Data!$AJ2,Model!$A$245:$A$246,0)),1,INDEX(Model!B$245:B$246,MATCH(Data!$AJ2,Model!$A$245:$A$246,0)))*IF(ISERROR(MATCH(Data!$AK2,Model!$A$250:$A$251,0)),1,INDEX(Model!B$250:B$251,MATCH(Data!$AK2,Model!$A$250:$A$251,0)))*IF(ISERROR(MATCH(Data!$AM2,Model!$A$260:$A$261,0)),1,INDEX(Model!B$260:B$261,MATCH(Data!$AM2,Model!$A$260:$A$261,0)))*IF(Data!$AN2="",1,NORM.DIST(Data!$AN2,Model!B$266,Model!B$274,FALSE))*IF(Data!$AO2="",1,NORM.DIST(Data!$AO2,Model!B$267,Model!B$275,FALSE))*IF(Data!$AP2="",1,NORM.DIST(Data!$AP2,Model!B$268,Model!B$276,FALSE))*IF(Data!$AQ2="",1,NORM.DIST(Data!$AQ2,Model!B$269,Model!B$277,FALSE))*IF(Data!$AR2="",1,NORM.DIST(Data!$AR2,Model!B$270,Model!B$278,FALSE))*IF(ISERROR(MATCH(Data!$AS2,Model!$A$369:$A$371,0)),1,INDEX(Model!B$369:B$371,MATCH(Data!$AS2,Model!$A$369:$A$371,0)))*IF(ISERROR(MATCH(Data!$AT2,Model!$A$375:$A$377,0)),1,INDEX(Model!B$375:B$377,MATCH(Data!$AT2,Model!$A$375:$A$377,0)))*IF(ISERROR(MATCH(Data!$AU2,Model!$A$381:$A$383,0)),1,INDEX(Model!B$381:B$383,MATCH(Data!$AU2,Model!$A$381:$A$383,0)))*IF(ISERROR(MATCH(Data!$AV2,Model!$A$387:$A$389,0)),1,INDEX(Model!B$387:B$389,MATCH(Data!$AV2,Model!$A$387:$A$389,0)))*IF(ISERROR(MATCH(Data!$AW2,Model!$A$393:$A$395,0)),1,INDEX(Model!B$393:B$395,MATCH(Data!$AW2,Model!$A$393:$A$395,0)))*IF(ISERROR(MATCH(Data!$AX2,Model!$A$399:$A$401,0)),1,INDEX(Model!B$399:B$401,MATCH(Data!$AX2,Model!$A$399:$A$401,0)))*IF(ISERROR(MATCH(Data!$AY2,Model!$A$405:$A$407,0)),1,INDEX(Model!B$405:B$407,MATCH(Data!$AY2,Model!$A$405:$A$407,0)))*IF(ISERROR(MATCH(Data!$AZ2,Model!$A$411:$A$413,0)),1,INDEX(Model!B$411:B$413,MATCH(Data!$AZ2,Model!$A$411:$A$413,0)))*IF(ISERROR(MATCH(Data!$BA2,Model!$A$417:$A$419,0)),1,INDEX(Model!B$417:B$419,MATCH(Data!$BA2,Model!$A$417:$A$419,0)))*IF(ISERROR(MATCH(Data!$BB2,Model!$A$423:$A$425,0)),1,INDEX(Model!B$423:B$425,MATCH(Data!$BB2,Model!$A$423:$A$425,0)))*IF(ISERROR(MATCH(Data!$BC2,Model!$A$429:$A$431,0)),1,INDEX(Model!B$429:B$431,MATCH(Data!$BC2,Model!$A$429:$A$431,0)))*IF(ISERROR(MATCH(Data!$BD2,Model!$A$435:$A$437,0)),1,INDEX(Model!B$435:B$437,MATCH(Data!$BD2,Model!$A$435:$A$437,0)))*IF(ISERROR(MATCH(Data!$BE2,Model!$A$441:$A$443,0)),1,INDEX(Model!B$441:B$443,MATCH(Data!$BE2,Model!$A$441:$A$443,0)))*IF(ISERROR(MATCH(Data!$BF2,Model!$A$447:$A$449,0)),1,INDEX(Model!B$447:B$449,MATCH(Data!$BF2,Model!$A$447:$A$449,0)))*IF(ISERROR(MATCH(Data!$BG2,Model!$A$453:$A$455,0)),1,INDEX(Model!B$453:B$455,MATCH(Data!$BG2,Model!$A$453:$A$455,0)))*IF(ISERROR(MATCH(Data!$BH2,Model!$A$459:$A$461,0)),1,INDEX(Model!B$459:B$461,MATCH(Data!$BH2,Model!$A$459:$A$461,0)))*IF(ISERROR(MATCH(Data!$BI2,Model!$A$465:$A$467,0)),1,INDEX(Model!B$465:B$467,MATCH(Data!$BI2,Model!$A$465:$A$467,0)))*IF(ISERROR(MATCH(Data!$BJ2,Model!$A$471:$A$473,0)),1,INDEX(Model!B$471:B$473,MATCH(Data!$BJ2,Model!$A$471:$A$473,0)))*IF(ISERROR(MATCH(Data!$BK2,Model!$A$477:$A$479,0)),1,INDEX(Model!B$477:B$479,MATCH(Data!$BK2,Model!$A$477:$A$479,0)))*IF(ISERROR(MATCH(Data!$BL2,Model!$A$483:$A$485,0)),1,INDEX(Model!B$483:B$485,MATCH(Data!$BL2,Model!$A$483:$A$485,0)))*IF(ISERROR(MATCH(Data!$BM2,Model!$A$489:$A$490,0)),1,INDEX(Model!B$489:B$490,MATCH(Data!$BM2,Model!$A$489:$A$490,0)))*IF(ISERROR(MATCH(Data!$BN2,Model!$A$494:$A$495,0)),1,INDEX(Model!B$494:B$495,MATCH(Data!$BN2,Model!$A$494:$A$495,0)))*IF(ISERROR(MATCH(Data!$BO2,Model!$A$499:$A$500,0)),1,INDEX(Model!B$499:B$500,MATCH(Data!$BO2,Model!$A$499:$A$500,0)))*IF(ISERROR(MATCH(Data!$BP2,Model!$A$504:$A$505,0)),1,INDEX(Model!B$504:B$505,MATCH(Data!$BP2,Model!$A$504:$A$505,0)))*IF(ISERROR(MATCH(Data!$BQ2,Model!$A$509:$A$510,0)),1,INDEX(Model!B$509:B$510,MATCH(Data!$BQ2,Model!$A$509:$A$510,0)))*IF(ISERROR(MATCH(Data!$BR2,Model!$A$514:$A$515,0)),1,INDEX(Model!B$514:B$515,MATCH(Data!$BR2,Model!$A$514:$A$515,0)))*IF(ISERROR(MATCH(Data!$BS2,Model!$A$519:$A$520,0)),1,INDEX(Model!B$519:B$520,MATCH(Data!$BS2,Model!$A$519:$A$520,0)))*IF(ISERROR(MATCH(Data!$BT2,Model!$A$524:$A$525,0)),1,INDEX(Model!B$524:B$525,MATCH(Data!$BT2,Model!$A$524:$A$525,0)))*IF(ISERROR(MATCH(Data!$BU2,Model!$A$529:$A$530,0)),1,INDEX(Model!B$529:B$530,MATCH(Data!$BU2,Model!$A$529:$A$530,0)))*IF(ISERROR(MATCH(Data!$BV2,Model!$A$534:$A$535,0)),1,INDEX(Model!B$534:B$535,MATCH(Data!$BV2,Model!$A$534:$A$535,0)))*IF(ISERROR(MATCH(Data!$BW2,Model!$A$539:$A$540,0)),1,INDEX(Model!B$539:B$540,MATCH(Data!$BW2,Model!$A$539:$A$540,0)))*IF(ISERROR(MATCH(Data!$BX2,Model!$A$544:$A$545,0)),1,INDEX(Model!B$544:B$545,MATCH(Data!$BX2,Model!$A$544:$A$545,0)))*IF(ISERROR(MATCH(Data!$BY2,Model!$A$549:$A$550,0)),1,INDEX(Model!B$549:B$550,MATCH(Data!$BY2,Model!$A$549:$A$550,0)))*IF(ISERROR(MATCH(Data!$BZ2,Model!$A$554:$A$55,0)),1,INDEX(Model!B$554:B$555,MATCH(Data!$BZ2,Model!$A$554:$A$555,0)))*IF(ISERROR(MATCH(Data!$CA2,Model!$A$559:$A$560,0)),1,INDEX(Model!B$9559:B$560,MATCH(Data!$CA2,Model!$A$559:$A$560,0)))*IF(ISERROR(MATCH(Data!$CB2,Model!$A$564:$A$565,0)),1,INDEX(Model!B$564:B$565,MATCH(Data!$CB2,Model!$A$564:$A$565,0)))*IF(ISERROR(MATCH(Data!$CC2,Model!$A$569:$A$570,0)),1,INDEX(Model!B$569:B$570,MATCH(Data!$CC2,Model!$A$569:$A$570,0)))*IF(ISERROR(MATCH(Data!$CD2,Model!$A$574:$A$575,0)),1,INDEX(Model!B$574:B$575,MATCH(Data!$CD2,Model!$A$574:$A$575,0)))*IF(ISERROR(MATCH(Data!$CE2,Model!$A$579:$A$580,0)),1,INDEX(Model!B$579:B$580,MATCH(Data!$CE2,Model!$A$579:$A$580,0)))*IF(ISERROR(MATCH(Data!$CF2,Model!$A$584:$A$585,0)),1,INDEX(Model!B$584:B$585,MATCH(Data!$CF2,Model!$A$584:$A$585,0)))*IF(ISERROR(MATCH(Data!$CG2,Model!$A$589:$A$590,0)),1,INDEX(Model!B$589:B$590,MATCH(Data!$CG2,Model!$A$589:$A$590,0)))*IF(ISERROR(MATCH(Data!$CH2,Model!$A$594:$A$595,0)),1,INDEX(Model!B$594:B$595,MATCH(Data!$CH2,Model!$A$594:$A$595,0)))*IF(ISERROR(MATCH(Data!$CI2,Model!$A$599:$A$600,0)),1,INDEX(Model!B$599:B$600,MATCH(Data!$CI2,Model!$A$599:$A$600,0)))*IF(ISERROR(MATCH(Data!$CJ2,Model!$A$604:$A$605,0)),1,INDEX(Model!B$604:B$605,MATCH(Data!$CJ2,Model!$A$604:$A$605,0)))*IF(ISERROR(MATCH(Data!$CK2,Model!$A$609:$A$610,0)),1,INDEX(Model!B$619:B$610,MATCH(Data!$CK2,Model!$A$609:$A$610,0)))*IF(ISERROR(MATCH(Data!$CL2,Model!$A$614:$A$615,0)),1,INDEX(Model!B$614:B$615,MATCH(Data!$CL2,Model!$A$614:$A$615,0)))*IF(ISERROR(MATCH(Data!$CM2,Model!$A$619:$A$620,0)),1,INDEX(Model!B$619:B$620,MATCH(Data!$CM2,Model!$A$619:$A$620,0)))

Answer 1

Hi Carlosmsal,

In Excel, there are some limitations. For formula, the maximum length of formula contents is 8,192 characters. I checked your formula, it contains more than 10,000 characters. So, you get the error message "formula too long".

To learn more about Excel limitations, see articles below:

Excel specifications and limits

Specifications and limits for Excel 2011

Thanks for your understanding.

Tim

Answer 2

Wow, that's one heck of a cell formula!

Perhaps you might be better off using a SQL query. This tutorial explains how to use SQL within Excel. When you read this, keep in mind that it was written with the idea that an external workbook would be the data source. But you can build a query within a single workbook (you can't refresh such queries, however).

http://www.agentjim.com/MVP/Excel/2011Relational1Intro.html

Answer 3

Hi Carlosmsal,

It's not feasible to change this limitation. You should modify the formula to get the result you want (e.g. may be you can use serveral cells/steps to get the result rather than put all formula in one cell).

Best regards,

Tim

Answer 4

THANKS TIM!!!

I assume there is no way to get around this max limit - right?

Carlos