Treat empty cells NOT as zero?

[KlausB]

A recurring pattern I need to use in my formulas is

=IF(ISBLANK(A1), NA(), A1)
=IF(ISNUMBER(A1), A1, NA())

i.e. I want to treat empty cells as missing data, not as “zero” for the sake of arithmetic. The exception are functions with range arguments like SUM(), where it is convenient to not explicitly handle empty rows in the range.

However, if I have something like =A1*C1, I basically always want missing data to be treated as such. Writing each such expression as, e.g.

# formula hard to read
=IF(ISBLANK(A1), NA(), A1) * IF(ISBLANK(C1), NA(), C1)

# keeps turning on "array formula" mode, is long, is hard to edit compared to =A1*C1
=LET(a, IF(ISBLANK(A1), NA(), A1), b, IF(ISBLANK(C1), NA(), C1), a*b)

# hard to change, because it requires 
# separate enumeration and usage of all references
=IF(OR(ISBLANK(A1), ISBLANK(C1)), NA(), A1*C1)

is a significant burden, and makes e.g. later correcting a complicated formula very hard.

Is there instead a way to tell LibreOffice Calc, ideally on a per-spreadsheet or even per-table basis, to consider empty cells as NA()?

[Lupp]

Functions or expressions cannot return "nothing" in Calc.
There also isn’t a setting to generally avoid that a reference to a blank cell in a calculation is taken as 0. You can create a respective feature request.
If you can accept the usage of a macro, you can try the attached example:
disask122264_NoZeroForBlankCells.ods (16.8 KB)
Instead of IF() with three arguments you can use an UDF with one argument (simple reference).

[Steph1]

I would create additional columns containing either the value or NA():
=IF(ISBLANK(A1), NA(), A1)
then use these columns for the multiplication. You can then hide these working columns.