Forum Discussion
How to ignore #CALC errors without using IFERROR
Hello
The subject line basically says it all. I have a dataset in which I'm trying to ignore a series #CALC errors. I usually just use IFERROR(****,""), but for the data I have the returned results are well over 255 per cell. And as I understand it, Excel recognizes anything above this as an error, thus removing my correct results.
Cab anyone suggest a way around this. Unfortunately I'm unable to do anything about the number of characters, it is just the nature of the data I have to work with.
Many Thanks
S
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.
8 Replies
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
