POST A QUESTION
 

Formula is in cell A3 which is =A1+A2. If I don't put any numbers in A1 or A2
cell A3 displays #value! Is there a way to not have the #value! show in cell
A3?

POST A QUESTION
 

Hi,
if you are doing a sum shouldn't give you that result anyway you can use

=if(A1="",A2,if(A2="",A1,A1+A2))


"dwolf" wrote:

Formula is in cell A3 which is =A1+A2. If I don't put any numbers in A1 or A2
cell A3 displays #value! Is there a way to not have the #value! show in cell
A3?

POST A QUESTION
 

Check to see if A1 or A2 is formatted as something other than a
number, or if one of those contains a space or other non-numeric
entry.

Dave O'Brien
Eschew obfuscation

POST A QUESTION
 

"dwolf" wrote:
Formula is in cell A3 which is =A1+A2.
If I don't put any numbers in A1 or A2
cell A3 displays #value! Is there a way
to not have the #value! show in cell A3?

If A1 and A2 are truly empty, =A1+A2 will result in zero.

So I presume you mean that A1 or A2 __appears__ blank because one or both
have a formula of the form IF(condition,"",something), which is resulting in
a null string.

In that case, =SUM(A1,A2) might be the simplest solution. It also tolerates
other text strings, e.g. " "; that is one or more blanks, which should be
avoided anyway.

POST A QUESTION
 

PS....

I wrote:
In that case, =SUM(A1,A2) might be the simplest solution.

But that presumes that you want 0 when one or both of A1 and A2 are truly
empty or they have text instead of a number.

If instead you would like the cell with the addition to appear blank unless
and until A1 and A2 have numbers, one of the following formulas should do the
trick, listed in the order of simplest to most robust assumptions about the
contents of A1 and A2.

=if(A1&A2="", "", A1+A2)

=if(TRIM(A1&A2)="", "", A1+A2)

=if(AND(ISNUMBER(A1),ISNUMBER(A2)), A1+A2, "")

=if(AND(ISNUMBER(VALUE(A1)),ISNUMBER(VALUE(A2))), A1+A2, "")


PPS: "Post a Question" was a terrible choice for a subject. In the future,
it would behoove you to choose a subject that is more descriptive of the
problem.


----- original message -----

"Joe User" wrote:
"dwolf" wrote:
Formula is in cell A3 which is =A1+A2.
If I don't put any numbers in A1 or A2
cell A3 displays #value! Is there a way
to not have the #value! show in cell A3?

If A1 and A2 are truly empty, =A1+A2 will result in zero.

So I presume you mean that A1 or A2 __appears__ blank because one or both
have a formula of the form IF(condition,"",something), which is resulting in
a null string.

In that case, =SUM(A1,A2) might be the simplest solution. It also tolerates
other text strings, e.g. " "; that is one or more blanks, which should be
avoided anyway.

POST A QUESTION
 

I wrote a lot of alternatives, namely:
=if(A1&A2="", "", A1+A2)
=if(TRIM(A1&A2)="", "", A1+A2)
=if(AND(ISNUMBER(A1),ISNUMBER(A2)), A1+A2, "")
=if(AND(ISNUMBER(VALUE(A1)),ISNUMBER(VALUE(A2))), A1+A2, "")

Or most simply:

=IF(COUNT(A1,A2)=2, A1+A2, "")


----- original message -----

"Joe User" <joeu2004 wrote in message
...
PS....

I wrote:
In that case, =SUM(A1,A2) might be the simplest solution.

But that presumes that you want 0 when one or both of A1 and A2 are truly
empty or they have text instead of a number.

If instead you would like the cell with the addition to appear blank
unless
and until A1 and A2 have numbers, one of the following formulas should do
the
trick, listed in the order of simplest to most robust assumptions about
the
contents of A1 and A2.

=if(A1&A2="", "", A1+A2)

=if(TRIM(A1&A2)="", "", A1+A2)

=if(AND(ISNUMBER(A1),ISNUMBER(A2)), A1+A2, "")

=if(AND(ISNUMBER(VALUE(A1)),ISNUMBER(VALUE(A2))), A1+A2, "")


PPS: "Post a Question" was a terrible choice for a subject. In the
future,
it would behoove you to choose a subject that is more descriptive of the
problem.


----- original message -----

"Joe User" wrote:
"dwolf" wrote:
Formula is in cell A3 which is =A1+A2.
If I don't put any numbers in A1 or A2
cell A3 displays #value! Is there a way
to not have the #value! show in cell A3?

If A1 and A2 are truly empty, =A1+A2 will result in zero.

So I presume you mean that A1 or A2 __appears__ blank because one or both
have a formula of the form IF(condition,"",something), which is resulting
in
a null string.

In that case, =SUM(A1,A2) might be the simplest solution. It also
tolerates
other text strings, e.g. " "; that is one or more blanks, which should be
avoided anyway.