POST A QUESTION

[dwolf]

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?

 

[Eduardo]

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))

 

[Dave O]

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

 

[Joe User]

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.

 

[Joe User]

PS....

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]

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 -----