Forum Discussion
How to ignore #CALC errors without using IFERROR
reevesgetsaround Not sure I follow but it seems you are using FILTER but don't want to see #CALC! without using IFERROR.
Try this:
=FILTER(C:C,(A:A=E2)*(B:B>4),"")
and copy down.
I assume that the length of your results is more than 32767 characters in a cell which is the limit.
Excel specifications and limits - Microsoft Support
I have done an example in Excel for the web with 1000 results in a cell which works as expected since the number of characters in the cell is less than 32767.
Hi there
None of my charachter lengths exceed 2000.
But when testing the 255 theory, it does seem that as soon as the charachter limit exceeds 255 it effectively creates and error and doesn't display, even for the correct matches.
The formula currently in column F, should return the contents of column C wherever the risk column B) exceeds 4.
This is just dummy data to test the formula, which seemed to work. However with my real world data, the charachter counts are much longer than what is being used here.
In C15 I have created a string of text to be added into the column C range, and it does indeed disappear from the results I'm column F as soon as the charachter count exceeds 255.
Yes, conditional functions like IF, CASE, etc includes IFERROR gave an error if condition is an array and value is more than 255 characters. For example
=IF( {1}, REPT(256), "a") ) returned an error but =IF( {1}, REPT(255), "a")) not.
Now it fixed, but it looks like not for IFERROR. The workaround could be to avoid direct IFERROR, e.g. instead of
=IFERROR( FILTER(C:C,(A:A=E6)*(B:B>4) ), "" )use
=LET(f, FILTER(C:C,(A:A=E6)*(B:B>4) ), IF(ISERROR(f),"",f) )with your sample it works
reevesgetsaround Not sure I follow but it seems you are using FILTER but don't want to see #CALC! without using IFERROR.
Try this:
=FILTER(C:C,(A:A=E2)*(B:B>4),"")
and copy down.
In the screenshot you can see that i've 27504 characters in the result cell F2 and 27500 characters in cell C15. The same result should be returned when you open the attached sample sheet.
=LEN(F2)This is the formula in cell G2.
That formula you have there in column F is different to the one I was using. I don't need a text join on this one as my real world dataset won't contain any duplicate rows in which to match/concat.
The problem seems to be when I use an IFERROR in relation to cells with over 255 characters. You can see the formula I'm referring to in my document. If you were to add that 27504 cell into C2 of that document, you will see what I mean.
It works as intended with your formula without IFERROR. My assumption was that you want to return the results in one cell that's why i'd added the TEXTJOIN.
