#VALUE

[amirstal]

A simple formula (let's say B1=A1-A2) returns a value when A1 and A2
have a value in them, but returns an error (#VALUE) when A1 NS a2 are
empty. Is there a way EXCEL can disregard the error/#VALUE and show
nothing ON B1 when A1 and A2 are empty while returning a value on B1
when A1 and A2 do have a value in them?

Thanks,

Amir

 

[Jim Thomlinson]

Not following... If A1 and A2 are empty then B1 shows 0. If you have text or
even a blank space character then you get #Value. In that case you can either
use data validation to ensure that the value sin A1 and A2 are numeric in
nature or you could validate the values in the formula. My preference is to
validate at entry time so thot you avoid bad data in the first place...

=IF(AND(ISNUMBER(A1), ISNUMBER(A2)), A1-A2, "")

 

[Fred Smith]

You do something like this:

=if(or(a1="",b1=""),"",a1-a2)

If you are willing to trap all errors, you could do:

=if(iserror(a1-a2),"",a1-a2)

Regards,
Fred.

 

[JLGWhiz]

Put this in B1:
=IF(AND(A1<>"", A2<>""),A1-A2, "")

 

[JLGWhiz]

If youi want it in VBA then:

If Range("A1") <> "" And Range("A2") <> "" Then
Range("B1") = Range("A1").Value - Range("A2").Value
End If

 

[farid2001]

Try this:

Range("B1").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-1]-R[1]C[-1],"" "")"
Range("B2").Select

Regards
farid2001

 

[Avi]

If you are using Excel 2007, there is a new formula IFERROR you can
use it.

= IFERROR(a1-a2,"") in the cell B1