Forum Discussion
Array formula error help
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.
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
- I seem to recall there may be a 256 (or maybe 255) character limit for certain things so that may very well be the issue. maybe try limiting all the values using LEFT(xxxx, 250) or less if you can and still be confident the differences will still be detected.
That said, what are you really doing with these equations? It appears you are trying to find how many meet a threshold or something. Maybe there is a better way using pivot table or power query.mtarler Thank you for the speedy reply.
This is part of a FIFO model I inherited from a friend to use for FIFO modeling, so I’m not 100% confident a pivot table would be able to work or power query would work, just given some of the logic needed around the “first” nuance of FIFO, but if you have come across another model that works for multiple different products that is not so memory consuming, please let me know
jonallama so what I recall about the 255 limit I believe was for arguments to a function (e.g. IFS(arg1, arg2, ....) you can't have more than 255 or 127 pairs of condition-output in this case). So I don't think that applies here. But here is a useful link I found that SergeiBaklan gave in a previous thread:
All limits are here https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US#ID0EBABAAA=Excel_2016-2013But since I don't have another idea right now, did you try the LEFT() idea? Just curious if it would make any difference:
copied from your msg above, formula in Cell P651
=SUM(--IF(MMULT(--(ROW(E$521:E650)>=TRANSPOSE(ROW(E$521:E650))),--IF(LEFT(D$521:D650,250)=LEFT(H651,250),E$521:E650,0))<SUMIF(LEFT(H$522:H651,250),LEFT(H651,250),I$522:I651),1,0))