Results 1 to 3 of 3

Thread: Inconsistent Formula Error

  1. #1
    Neophyte Zfishermon's Avatar
    Join Date
    Oct 2019
    Posts
    1
    Articles
    0
    Excel Version
    2016

    Inconsistent Formula Error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,604
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files
    Last edited by p45cal; 2019-10-17 at 01:57 AM.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,604
    Articles
    0
    Excel Version
    365
    cross posted without links:
    https://www.excelforum.com/excel-for...ula-error.html
    Zfishermon/Bowers, for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •