Error with sum formula in excel 2007

  • Thread starter Thread starter Heera
  • Start date Start date
H

Heera

Hi,

Cell A1=5
Cell A2=5
Cell A3=if(sum(A1+A2)=0,"",sum(A1+A2)) ----- will return 10 or
""(Blank)
Cell A4=10

Cell A5=sum(A3+A4) ------should return 20 or 10 or 0

Now here is the error: If there is no value in the Cell A1 & A2 I get
error(#value) in the Cell A5 becuase I have put ""(Blank) in the
formula which is there in the A3. I tried different formats but they
are not working. I never faced such error in excel 2003.

Regards

Heera
 
Using Excel 2003, I get the same error you describe. Here are two possible
solutions:

1. Change for formula in A3 to return zero instead of "".

2. Change your formula in A5 to

=IF(A3="",0,SUM(A3+A4))

Hope this helps,

Hutch
 
It might be wise for you to stop and think about what the SUM() function is
doing for you, and to remind yourself in Excel help what the syntax of the
function is (and then think about what the effect is if you feed it only one
parameter).

To use =SUM(A3+A4) is as pointless as to say =MIN(A3+A4) or =MAX(A3+A4) or
=PRODUCT(A3+A4) or =MEDIAN(A3+A4)
In all cases, the answer is the same as if you had merely used =A3+A4, as
A3+A4 is the only parameter you have fed to each of the various functions,
and each of those functions is expecting a number of input parameters.

Perhaps what you probably intended to do was to use =sum(A3,A4) in A5, and
=IF(SUM(A1,A2)=0,"",SUM(A1,A2)) in A3 ?
 
Firstly, XL2003 behaves exactly the same - I tried it
Secondly, why use SUM when a simple formula is easier? =IF(A1+A2=0,"
",A1+A2)
This, however, does not solve the problem in A5
But this will =IF(ISNUMBER(A3),A3+A4,A4)

Alternatively, use a custom format that does not display zero in A3. Fro
example: 0;0;""
best wishes
 
Hi,

Cell A1=5
Cell A2=5
Cell A3=if(sum(A1+A2)=0,"",sum(A1+A2)) ----- will return 10 or
""(Blank)
Cell A4=10

Cell A5=sum(A3+A4) ------should return 20 or 10 or 0

Now here is the error: If there is no value in the Cell A1 & A2 I get
error(#value) in the Cell A5 becuase I have put ""(Blank) in the
formula which is there in the A3. I tried different formats but they
are not working. I never faced such error in excel 2003.

Regards

Heera
Click to expand...

If you had entered it exactly as you have above, you probably would have seen
the error in XL2003 also.

Change

A5: =SUM(A3:A4)

Also, in A3, if the contents of A1 or A2 can only be a number or blank, the SUM
is useless. In other words, as you see in A5,

=A1+A2 gives the same result (and errors) as

=SUM(A1+A2)

--ron
 
Back
Top