How to ignore #CALC errors without using IFERROR

[reevesgetsaround]

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

[#2]

@reevesgetsaround 

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.

[#3]

@OliverScheurich 

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.

[#4]

@reevesgetsaround 

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.

[#5]

@OliverScheurich 

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.

[#6 · Accepted solution]

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

[#7]

@reevesgetsaround 

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. 

[#8]

Thank you Riny and Thank you Quad for your perseverance - I have got it working now without the IFERROR.

Best Regards

[#9]

@reevesgetsaround 

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