1. ## Inconsistent Formula Error

I am having a problem with an inconsistent formula error, even though its not an inconsistent formula. I am uncertain if the formula is working or not working. On my spreadsheet the formula is entered into cells R415, S415, T415, U415, V415 and for every week thereafter. The formula is:

"=(R10/\$DL\$8*\$DO\$8)+(R15/\$DL\$13*\$DO\$13)+(R20/\$DL\$18*\$DO\$18)+(R25/\$DL\$23*\$DO\$23)+(R30/\$DL\$28*\$DO\$28)+(R35/\$DL\$33*\$DO\$33)+
(R40/\$DL\$38*\$DO\$38)+(R45/\$DL\$43*\$DO\$43)+(R50/\$DL\$48*\$DO\$48)+(R56/\$DL\$54*\$DO\$54)+(R61/\$DL\$59*\$DO\$59)+(R66/\$DL\$64*\$DO\$64)+
(R71/\$DL\$69*\$DO\$69)+(R76/\$DL\$74*\$DO\$74)+(R81/\$DL\$79*\$DO\$79)+(R86/\$DL\$84*\$DO\$84)+(R91/\$DL\$89*\$DO\$89)+(R97/\$DL\$95*\$DO\$95)+
(R102/\$DL\$100*\$DO\$100)+(R107/\$DL\$105*\$DO\$105)+(R113/\$DL\$111*\$DO\$111)+(R118/\$DL\$116*\$DO\$116)+(R123/\$DL\$121*\$DO\$121)+
(R129/\$DL\$127*\$DO\$127)+(R134/\$DL\$132*\$DO\$132)+(R139/\$DL\$137*\$DO\$137)+(R144/\$DL\$142*\$DO\$142)+(R149/\$DL\$147*\$DO\$147)+
(R153/\$DL\$151*\$DO\$151)+(R157/\$DL\$155*\$DO\$155)+(R161/\$DL\$159*\$DO\$159)+(R165/\$DL\$163*\$DO\$163)+(R169/\$DL\$167*\$DO\$167)+
(R173/\$DL\$171*\$DO\$171)+(R177/\$DL\$175*\$DO\$175)+(R181/\$DL\$179*\$DO\$179)+(R185/\$DL\$183*\$DO\$183)+(R189/\$DL\$187*\$DO\$187)+
(R193/\$DL\$191*\$DO\$191)+(R197/\$DL\$195*\$DO\$195)+(R201/\$DL\$199*\$DO\$199)+(R205/\$DL\$203*\$DO\$203)+(R209/\$DL\$207*\$DO\$207)+
(R213/\$DL\$211*\$DO\$211)+(R217/\$DL\$215*\$DO\$215)+(R221/\$DL\$219*\$DO\$219)+(R225/\$DL\$223*\$DO\$223)+(R229/\$DL\$227*\$DO\$227)+
(R233/\$DL\$231*\$DO\$231)+(R237/\$DL\$235*\$DO\$235)+(R241/\$DL\$239*\$DO\$239)+(R245/\$DL\$243*\$DO\$243)+(R249/\$DL\$247*\$DO\$247)+
(R253/\$DL\$251*\$DO\$251)+(R257/\$DL\$255*\$DO\$255)+(R261/\$DL\$259*\$DO\$259)+(R265/\$DL\$263*\$DO\$263)+(R269/\$DL\$267*\$DO\$267)+
(R273/\$DL\$271*\$DO\$271)+(R277/\$DL\$275*\$DO\$275)+(R281/\$DL\$279*\$DO\$279)+(R285/\$DL\$283*\$DO\$283)+(R289/\$DL\$287*\$DO\$287)+
(R293/\$DL\$291*\$DO\$291)+(R297/\$DL\$295*\$DO\$295)+(R301/\$DL\$299*\$DO\$299)+(R305/\$DL\$303*\$DO\$303)+(R309/\$DL\$307*\$DO\$307)+
(R313/\$DL\$311*\$DO\$311)+(R317/\$DL\$315*\$DO\$315)+(R321/\$DL\$319*\$DO\$319)+(R325/\$DL\$323*\$DO\$323)+(R329/\$DL\$327*\$DO\$327)+
(R333/\$DL\$331*\$DO\$331)+(R337/\$DL\$335*\$DO\$335)+(R341/\$DL\$339*\$DO\$339)+(R345/\$DL\$343*\$DO\$343)+(R349/\$DL\$347*\$DO\$347)+
(R353/\$DL\$351*\$DO\$351)+(R357/\$DL\$355*\$DO\$355)+(R361/\$DL\$359*\$DO\$359)+(R365/\$DL\$363*\$DO\$363)+(R369/\$DL\$367*\$DO\$367)+
(R373/\$DL\$371*\$DO\$371)+(R377/\$DL\$375*\$DO\$375)+(R381/\$DL\$379*\$DO\$379)+(R385/\$DL\$383*\$DO\$383)+(R389/\$DL\$387*\$DO\$387)+
(R393/\$DL\$391*\$DO\$391)+(R397/\$DL\$395*\$DO\$395)+(R401/\$DL\$399*\$DO\$399)+(R405/\$DL\$403*\$DO\$403)

The formula's pattern sometimes adds 5 rows (e.g. (R10/...)+(R15/...)+(R20/...) and it sometimes adds 4 rows (e.g. (R397/...)+(R401/...)+(R405/...), but its the same for all formulas in R415 to V415 and beyond. The inconsistent formula error has occurred since the spreadsheet was created, but the formula has still worked, until I added the most recent calculation. The problem I dont understand is that when I added the last string "(R405/\$DL\$403*\$DO\$403), all of formula's tracing colors in the formula bar go black. It happens as soon as I type "/" for the last entry. Am I reaching a character limit or some kind of argument limit? Is the formula still working or has it stopped?

I have researched multiple online sources but have not found the answer yet. Does anyone know why Excel would turn the entire formula black after entering the most recent calculation into the formula, and has the formula stopped working?  Reply With Quote

2. I think you might well be getting to the limits of formula length.
In the attached, have a look at the formulae in cells R415:V415, go in as if to edit and report if the correct ranges are being referred to.
The attached has a couple of macros in but they're not needed so you don't need to have macros enabled.

I did an analysis of your formula in the top left part of that sheet. As you say, it's not consistent; if you look at column G, some elements are 6 rows apart too.  Reply With Quote

https://www.excelforum.com/excel-for...ula-error.html
This is a requirement, not just a request.  Reply With Quote