Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I post a question that Needs to show an equation that won't | New Users to Excel | |||
get error message when I try to post a question | Excel Discussion (Misc queries) | |||
Why can't I post a question? | Excel Discussion (Misc queries) | |||
General question - editing a post | Excel Discussion (Misc queries) | |||
embarassed to post question | New Users to Excel |