Forum Discussion

jonallama's avatar
jonallama
Copper Contributor
Jan 12, 2022

Array formula error help

Hi, I have an array formula that is producing a value error for one particular cell in my spreadsheet. The weird thing is, the formula directly above it (which is the same formula, just referencing one fewer set of rows) works perfectly fine. 

 

When I evaluate the formula in the value error cell, I get this in the evaluation, which I had never seen before: it shows

 

Sum(--IF({0;0;0;0;0;0;0;0;0;0;0;0;0}<SUMIF($H$522:$H$539,"SSCC 6ES7518-4FP00-0AB0-SIMATIC S7-1500F,CPU 1518F-4 PN/DPP, CENTRAL PROCESSING UNIT WITH WORKING MEMORY...) and goes on like that for a bit.

 

I've never seen any evaluation like that, and I'm wondering if this error comes up as a result of lack of enough memory? 

 

The erroring formula itself is: =SUM(--IF(MMULT(--(ROW(E$521:E538)>=TRANSPOSE(ROW(E$521:E538))),--IF(D$521:D538=H539,E$521:E538,0))<SUMIF(H$522:H539,H539,I$522:I539),1,0))

 

The formula in the cell above (which works fine) is: =SUM(--IF(MMULT(--(ROW(E$521:E537)>=TRANSPOSE(ROW(E$521:E537))),--IF(D$521:D537=H538,E$521:E537,0))<SUMIF(H$522:H538,H538,I$522:I538),1,0))

 

In total, I've got a long column of 528 cells that have this same formula carried down, and four are producing this same error and have the same weird formula evaluation. 

 

Any help here would be appreciated! 

excel
Formulas and Functions

6 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 12, 2022

    jonallama 

    In formula with error this part

    --IF(D$521:D538=H539,E$521:E538,0)

    returns 1x18 array which you MMULT with 17x17 matrix.

     

    In formula without array they are 1x17 and 17x17.

     

    Thus ranges shall be corrected.

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jan 12, 2022

      SergeiBaklan hmm, when I look at it, it appears it should be giving an 18x18 (basically every array in that formula added 1 cell compared to the other formula).  That said, I would ask jonallama  what is in cell H539 since that is what the Eval is replacing with the odd string and why is the {0;0;0;...} have only 13 elements in it instead of the expected 18.

      • jonallama's avatar
        jonallama
        Copper Contributor
        Jan 13, 2022

        mtarler and SergeiBaklan  first of all thank you both so much for chiming in with assistance. truly much appreciate!

         

        mtarler to address the easiest question first, you're right, the {0;0;0;...} does consist of 18 zeroes, tired old eyes previously miscounted. so the evaluation of that first step is:

         

        SUM--(IF{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}<SUMIF($H$522:$H539,"SC 6ES7518-4FP00-0AB0-SIMATIC S7-1500F, CPU 1518F-4PN/DP, CENTRAL PROCESSING UNIT WITH WORKING MEMORY...) and continues on

         

        mtarler to answer your first question of what's in H539, it's a text string, specifically: 

         

        SC 6ES7518-4FP00-0AB0 - SIMATIC S7-1500F, CPU 1518F-4 PN/DP, CENTRAL PROCESSING UNIT WITH WORKING MEMORY 4,5 MB FOR PROGRAM AND 10 MB FOR DATA, 1. INTERFACE, PROFINET IRT WITH 2 PORT SWITCH, 2. INTERFACE, ETHERNET, 3. INTERFACE, ETHERNET, 4. INTERFACE, PROFIBUS, 1 NS BIT-PERF...

         

        mtarler SergeiBaklan is it possible the text string length is what's causing an error? i have four cells with the similar formula (out of over 500 cells) that reference various text strings that are erroring out, and the one common factor is that the corresponding text string length in the adjacent H column C is over 270 character lengths long. (two are 275, one is 277, one is 279) 

         

        Summary:

        Cell P652: when evaluated, returns a similar error as that formula in P539 (but with 131 zeroes): 

        Formula: =SUM(--IF(MMULT(--(ROW(E$521:E651)>=TRANSPOSE(ROW(E$521:E651))),--IF(D$521:D651=H652,E$521:E651,0))<SUMIF(H$522:H652,H652,I$522:I652),1,0))

        Length of text in cell H652: 275

         

        Cell P540: similar evaluation error as P652 and P539

        Formula: =SUM(--IF(MMULT(--(ROW(E$521:E539)>=TRANSPOSE(ROW(E$521:E539))),--IF(D$521:D539=H540,E$521:E539,0))<SUMIF(H$522:H540,H540,I$522:I540),1,0))

        Length of text in cell H540: 277

         

        The only error (the fourth cell with the value error) which does not fit this pattern is my formula in Cell P651

        Formula: =SUM(--IF(MMULT(--(ROW(E$521:E650)>=TRANSPOSE(ROW(E$521:E650))),--IF(D$521:D650=H651,E$521:E650,0))<SUMIF(H$522:H651,H651,I$522:I651),1,0))

        Length of text in cell H651: 275

        Initial evaluation: checks out (a bunch of "TRUE"s); first click of evaluation button though returns this:

        SUM(--IF(MMULT({1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1:1},--IF(#VALUE!=H651,E$521:E650,0))<SUMIF(H$522:H651,H651,I$522:I651),1,0)) // keeping my mind that there are actually 130 1s in that sequence

         

Resources