Consistent data types in columns: how are #ERRORS treated?

[eteb3]

Principles for building a large spreadsheet

Note that one of the things that greatly slows Calc down is, when your column has non-uniform data. Say, A1:A10 have numbers, A11 has text, A12:A100 numbers, A101 blank, A102:A250 numbers, A251:A253 formulas … and so on (note that “text looking like a number” is still text). When the total number of rows is great, this data setup gives a huge blow to the performance. Compared to this, a simple A1:A1000000 having only numbers would work maybe hundred of times faster.

This ^ principle (keeping data types consistent in any column, either text or numbers) has greatly improved the performance of my workbooks.

How do #ERROR codes behave here? I have tried ISTEXT and ISNUMBER, and they appear to be neither.

I can force an error code to become like a number, or like text, to make completely consistent data. eg, IFERROR(... , 0) or IFERROR(... , "").

Is this necessary to ensure good performance? Or maybe I don’t need to worry about error codes, if they’re useful?

Thanks.

[JohnSUN]

In general, if you didn’t plan the error on purpose (to be honest, I can’t imagine a situation where this would be justified), then it’s better to get rid of errors immediately after they’re discovered, not try to process them, but rather correct them.
I hope that Bugerra extension will make this task easier for you. Yes, the extension is not perfect, in case of a very large number of errors it works very, very slowly. But in your large table of these errors there are not very many?

[Villeroy]

SpecialCells » Libreoffice Extensions is really quick with thousands of error ranges

[eteb3]

@JohnSUN

to be honest, I can’t imagine a situation where this would be justified

I’m looking for text in text. So I might have a formula, FIND("foo",A1), which I then want to use in logic. LEN(A2) is TRUE if “foo” is found, and #NA if not. I force a return of FALSE by using IFERROR to transform the #NA into 0.

On ranges I’m doing the same with MATCH: I know there is no match if it returns #NA, then IFERROR “normalises” the result.

(COUNTIF will return 0 instead of #NA, but that creates other problems.)

Is this a good approach?

[erAck]

In the case of catching #N/A errors use IFNA() instead of IFERROR() because otherwise the latter suppresses any error that you won’t be aware of.

[Villeroy]

Unfortunately, it is an old spreadsheet traditional that FIND returns #VALUE when it should return 0 or #N/A.
IFERROR(FIND(...);0) returns number 0 in case of no match.
IFERROR(FIND(...);NA()) returns error #N/A in case of no match.

[karolus]

@erAck

use IFNA() instead

hm… compare the the 3 FormulaRanges in Columns G:I in
ask_113434.ods (15.1 KB)
attached file…

[erAck]

That seems to be because if you pass an error value as lookup-value (e.g. NA() or 1/0 (#DIV/0!)) then VLOOKUP() returns Err:504 (Error in parameter list); not quite sure ad-hoc whether that is right or wrong or it should return the passed error value instead.

[karolus]

the enclosed »REGEX(…)« returns a valid String or nothing, if this »nothing« is invalid for »VLOOKUP« should’t it throw a 504? (IMHO)

[erAck]

@karolus

the enclosed »REGEX(…)« returns a valid String or nothing

If there is no match then it returns #N/A, for example the REGEX($B3;G$1) in G9. (if that is what you are referring with nothing then ok).

@karolus

if this »nothing« is invalid for »VLOOKUP« should’t it throw a 504?

That’s what it does (if you replace »nothing« with #N/A). I believe it is correct.

[karolus]

@erAck

I believe it is correct

omg yes … my bad! i did change the whole thing at prior state without realizing thats the regex throws the the #N/A.
The working formula:

=VLOOKUP(IFNA(REGEX($B2;G$1);"none");reference;2;0)

the »reference« range:

cat	125
tiger	125
snake	234
bug	678
none	…