Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error with sum formula in excel 2007
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error with sum formula in excel 2007
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 "Heera" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error with sum formula in excel 2007
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 ? -- David Biddulph "Heera" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error with sum formula in excel 2007
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Heera" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error with sum formula in excel 2007
On Thu, 9 Oct 2008 09:43:51 -0700 (PDT), Heera wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error in Excel 2007 | Excel Discussion (Misc queries) | |||
Formula error - Excel 2007 | Excel Discussion (Misc queries) | |||
#REF! error when copying formula beyond row 65536 in Excel 2007 | Excel Discussion (Misc queries) | |||
Error in Excel 2007 code | Excel Discussion (Misc queries) | |||
Excel 2007 Solver Error | Excel Worksheet Functions |