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.
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.
- Thank you Riny and Thank you Quad for your perseverance - I have got it working now without the IFERROR.
Best Regards