#VALUE
 

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

#VALUE
 

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, "")
--
HTH...

Jim Thomlinson


"amirstal" wrote:

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

#VALUE
 

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.

"amirstal" wrote in message
...
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

#VALUE
 

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

"amirstal" wrote:

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

#VALUE
 

If youi want it in VBA then:

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






"amirstal" wrote:

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

#VALUE
 

Try this:

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

Regards
farid2001



"amirstal" wrote:

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

#VALUE
 

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

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

On Sep 16, 2:33*am, amirstal wrote:
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