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.

[joshua4]

FYIY here is an auditing macro that flags all cells that would show an error if blank cells were not considered to be 0. It poisons the blank cells in the data, flags the error cells, then unpoisons the blank cells. The hard part of doing something like this is efficiency–the naive approach alone would be too slow to be practical. Even this macro might be a little sluggish on big sheets.

Sub FlagBlankDependencies()
	Dim oDoc As Object
	Dim oSheet As Object
	Dim oCursor As Object
	Dim oUsedRange As Object
	Dim oRange As Object
	Dim aData() As Variant
	Dim oCell As Object
	Dim iRow As Long
	Dim iCol As Long
	Dim bAutoCalc As Boolean
	Dim sMsg As String

	Const Flag = " " 'Must be a string since clearContents enum is STRING below
	sMsg = "Flagging Failed. Sorry."
	oDoc = ThisComponent
	oSheet = oDoc.CurrentController.ActiveSheet
	oUsedRange = GetUsedRange(oSheet)

	On Error Goto Handler 'Recover autocalculation and controller lock!
	
	'Increase efficiency by turning off screen updating and calculation
	If Not oDoc.isActionLocked Then ' Check if already locked to avoid errors
		oDoc.lockControllers()
	End If
	oDoc.calculate() 'Optional initial calculation
	bAutoCalc = oDoc.isAutomaticCalculationEnabled()
	oDoc.enableAutomaticCalculation(False)

	'Set all empty cells in used range to the Flag constant
	'	queryEmptyCells returns an UNO sequence of range objects
	'	that we iterate over with Each for each individual range
	For Each oRange in oUsedRange.queryEmptyCells()
	   	aData = oRange.getDataArray()
		For iRow = 0 To UBound(aData)
			For iCol = 0 To UBound(aData(0))
				aData(iRow)(iCol) = Flag
			Next
		Next
		oRange.setDataArray(aData)
	Next
	iRow = 0
	iCol = 0

	'Flag erroroneous cells
	oDoc.calculate() 'This is necessary to get the #VALUE!'s needed
	For Each oCell in oUsedRange.queryFormulaCells(com.sun.star.sheet.FormulaResult.ERROR).Cells
		oCell.CellStyle = gsFlaggedStyle
	Next
	
	'Remove the Flag values to return to blank cells
	With oUsedRange.RangeAddress
		For iRow = .StartRow To .EndRow
			For iCol = .StartColumn To .EndColumn
				oCell = oSheet.getCellByPosition(iCol, iRow)
				If oCell.getString() = Flag Then
					oCell.clearContents(com.sun.star.sheet.CellFlags.STRING)
				End If
			Next iCol
		Next iRow
	End With
	oDoc.calculate() 'Optional final calculation
	
	'Reset processing options
	sMsg = "Flagging Complete"
	Handler:
	If oDoc.isActionLocked Then ' Check if currently locked
		oDoc.unlockControllers()
	End If
	oDoc.enableAutomaticCalculation(bAutoCalc)

	MsgBox sMsg
	
End Sub

Function GetUsedRange(Sheet As Object)
	Dim oCursor As Object
	oCursor = Sheet.createCursor()
	oCursor.gotoEndOfUsedArea(False)
	With oCursor.RangeAddress
		GetUsedRange = Sheet.getCellRangeByPosition(0, 0, .EndColumn, .EndRow)
 	End With

End Function

See the attached ODS for a more complete setup and unflagging macro if you are interested.

Flag Blank Dependencies.ods (49.7 KB)

[LeroyG]

@Lupp

UDF

user-defined functions