Analyze a cell whose displayed value is '#VALUE!'?

[risedu]

I want to apply conditional formatting to this.

The following worked:

=CELL("CONTENTS"; B3)

But the following did not:

=IF(D2='#value!', 'yes', 'no')

Output is: #NAME?

Also LibreCalc keeps converting ‘#VALUE!’ to lower case in that IF function.

[karolus]

hallo

=ISERROR(D2)

[mikekaganski]

In Calc, single quote is not the same as double quote. It’s not Python. Use double quotes to define strings.

And indeed, use @karolus’ advise to check for errors - the cell with an error will not compare equal to any string. ERRORTYPE is another useful function in this case.

[Villeroy]

Calc knows 3 types of data:

1. Text
2. Numbers including dates, times, currencies, boolean TRUE/FALSE
3. Formulas may return errors.

In MS Excel boolean TRUE/FALSE is a 4th type. In Calc =TRUE=1 returns TRUE because TRUE is equivalent to 1, whereas in Excel the same formula returns FALSE because TRUE is a different type of value than 1.
You may type the literal text #VALUE! into some cell. The cell won’t have any error value.
Likewise, it is possible to type the literal text 00123 into a cell. That cell has a text of 5 characters that happen to be digits. The cell won’t have a numeric value. You can enter numeric text by adding a leading apostrophe or by preparing the cell with number format “Text” before data entry. The text 123 is not equivalent to the number 123.