Change Display of NA?

[PenguinGuru]

Hi, is there any way to configure LibreOffice Calc to display NA differently? For example, as “” or “—”?

I would like to use NA in cell functions to indicate when the data that would appear in those cells cannot be referenced for expected reasons, but the way NA displays by default is just too alarming and visually intrusive (i.e. big) in many cases. Since this is a matter of personal preference, I thought there might be some way to change the way it displays on a given spreadsheet or by program default. If not, perhaps by cell formatting?

[mikekaganski]

Just use IFNA spreadsheet function where you want.

[PenguinGuru]

This works, but it ends up taking quite a long time to add this condition to every cell, often multiple times in the same cell for multiple references. I then have to maintain two versions of the file-- one with all of the IFNA() and one without, since other people might prefer the more standard format.

Since posting, I have been investigating conditional formatting. It seems like this should be able to check for NA, but I am not sure it can be used to change the cell contents (other than to make them blend in with the background).

[mikekaganski]

often multiple times in the same cell for multiple references

why? isn’t it enough to wrap the whole formula?

[PenguinGuru]

I mean cases where one cell references another, the one one that is being referenced could be valid data or NA, and the cell that contains the reference should produce a result that is not NA. For example, if I am summing a range with some valid and some NA, I would need to use SUM(IFNA(…)). I still want the sum value. If I have changed content of the cell that is being referenced, I have to use something like SUM(IF("—",A1,0)). Not a big difference, the problem is that I have to change every reference in every cell. Maybe I’m confused and overlooking something.