IF Statemet returning error (#VALUE!) [Subtracting from ""]

[Shane O]

Hi,

the following 'if' statement returns a #VALUE! error if the target cells have no value:

=IF(G19-H19<0,G19-H19,"")

Here's what I tried to fix it, which was not successful.

=IF(G19="",IF(H19="","",IF(G19-H19>0,G19-H19,"")),IF(G19-H19>0,G19-H19,""))

I understand that subtracting a "" value doesn't make any logical sense to excel, but is there a way to get excel to 'think' that these "" values represent 0?

[daddylonglegs]

Try

=IF(COUNT(G19,H19)=2,IF(G19>H19,G19-H19,""),"")

I'm assuming you only want to see a result if it's positive

[zbor]

Mybe this:

=IF(G19<H19,G19-H19,"")

THis how you avoid substracting ""

[Shane O]

Acutally I still one more question..

There are times I need to subtract from 0 (or in this case, ""). In these rare but existant cases the error persists.

Both solutions listed by zbor and daddylonglegs wouldn't work in this case.

Any thoughts on how to fix this issue?


Also, in case it helps.. I'm not manually typing these formulas in.. the folloing VBA code is being used:

Sub FillFormula(strTarCell As String, strCell1 As String, strCell2 As String, _
        Optional bolGreater As Boolean = True, Optional shtSheet As Worksheet)
        
    Dim chrSign As String
    
    If bolGreater Then
        chrSign = ">"
    Else
        chrSign = "<"
    End If
    
    shtSheet.Range(strTarCell & "5").Value = "=IF(" & strCell1 & "5" & chrSign _
        & strCell2 & "5" & "," & strCell1 & "5-" & strCell2 & "5," _
        & Chr(34) & Chr(34) & ")"

End Sub