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.
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