Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a null cell as 0 (Zero) so that the subtraction works
Each cell in my worksheet is a function call that is requesting data from
webservice. But at times some of these cells dont have any values; and this is not an error on the part of the webservice but it happens so as thre is no data for that particular function call. Now when I am using a subtraction or and addition or using any factor to divide this number in a different cell I get a #VALUE error. I know I can use the "IF" function to get over this error but if there is any other way around this I would like to know. Also if there is a cell format that will change the null cell to a Zero (0) with a different color will do (color change will make me aware that it is something that the formating has done and the actual cell value is not a Zero.) I am using Excel 2003 Professional |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a null cell as 0 (Zero) so that the subtraction works
You would need a formula, there is no format that will convert a "" (I
assume that is what you have) to zero, there is another function that can be used =C3-N(A4) where A4 is the cell with the null string so =N(A4) will return zero if A4 is text or empty also if you are multiplying you can use =POWER(C3,A4) equals =C3*A4 for adding you can use =SUM(C3,A4) equals =C3+A4 both will ignore text -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Kekin Kakka" <Kekin wrote in message ... Each cell in my worksheet is a function call that is requesting data from webservice. But at times some of these cells dont have any values; and this is not an error on the part of the webservice but it happens so as thre is no data for that particular function call. Now when I am using a subtraction or and addition or using any factor to divide this number in a different cell I get a #VALUE error. I know I can use the "IF" function to get over this error but if there is any other way around this I would like to know. Also if there is a cell format that will change the null cell to a Zero (0) with a different color will do (color change will make me aware that it is something that the formating has done and the actual cell value is not a Zero.) I am using Excel 2003 Professional |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a null cell as 0 (Zero) so that the subtraction works
Hi
Use SUM function instead of adding/substracting, like =SUM(A1:A4,-A5) , or =SUM(A1:A4)-SUM(A5;A8) Arvi Laanemets "Kekin Kakka" <Kekin wrote in message ... Each cell in my worksheet is a function call that is requesting data from webservice. But at times some of these cells dont have any values; and this is not an error on the part of the webservice but it happens so as thre is no data for that particular function call. Now when I am using a subtraction or and addition or using any factor to divide this number in a different cell I get a #VALUE error. I know I can use the "IF" function to get over this error but if there is any other way around this I would like to know. Also if there is a cell format that will change the null cell to a Zero (0) with a different color will do (color change will make me aware that it is something that the formating has done and the actual cell value is not a Zero.) I am using Excel 2003 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a null cell as 0 (Zero) so that the subtraction wo
Thanks Peo,
This was a nice fix for my problem. Regards, Kekin "Peo Sjoblom" wrote: You would need a formula, there is no format that will convert a "" (I assume that is what you have) to zero, there is another function that can be used =C3-N(A4) where A4 is the cell with the null string so =N(A4) will return zero if A4 is text or empty also if you are multiplying you can use =POWER(C3,A4) equals =C3*A4 for adding you can use =SUM(C3,A4) equals =C3+A4 both will ignore text -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Kekin Kakka" <Kekin wrote in message ... Each cell in my worksheet is a function call that is requesting data from webservice. But at times some of these cells dont have any values; and this is not an error on the part of the webservice but it happens so as thre is no data for that particular function call. Now when I am using a subtraction or and addition or using any factor to divide this number in a different cell I get a #VALUE error. I know I can use the "IF" function to get over this error but if there is any other way around this I would like to know. Also if there is a cell format that will change the null cell to a Zero (0) with a different color will do (color change will make me aware that it is something that the formating has done and the actual cell value is not a Zero.) I am using Excel 2003 Professional |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a null cell as 0 (Zero) so that the subtraction wo
Hi Alan,
I did not follow and so did not use the response that you sent, but thanks a lot for the help. Regards, Kekin "Alan" wrote: Apply =CODE(your_cell) to one of these cells, see if you get 32 or 160, Regards, Alan. "Kekin Kakka" <Kekin wrote in message ... Each cell in my worksheet is a function call that is requesting data from webservice. But at times some of these cells dont have any values; and this is not an error on the part of the webservice but it happens so as thre is no data for that particular function call. Now when I am using a subtraction or and addition or using any factor to divide this number in a different cell I get a #VALUE error. I know I can use the "IF" function to get over this error but if there is any other way around this I would like to know. Also if there is a cell format that will change the null cell to a Zero (0) with a different color will do (color change will make me aware that it is something that the formating has done and the actual cell value is not a Zero.) I am using Excel 2003 Professional |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make a null cell as 0 (Zero) so that the subtraction wo
Thanks for the feedback
Peo "Kekin Kakka" wrote in message ... Thanks Peo, This was a nice fix for my problem. Regards, Kekin "Peo Sjoblom" wrote: You would need a formula, there is no format that will convert a "" (I assume that is what you have) to zero, there is another function that can be used =C3-N(A4) where A4 is the cell with the null string so =N(A4) will return zero if A4 is text or empty also if you are multiplying you can use =POWER(C3,A4) equals =C3*A4 for adding you can use =SUM(C3,A4) equals =C3+A4 both will ignore text -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Kekin Kakka" <Kekin wrote in message ... Each cell in my worksheet is a function call that is requesting data from webservice. But at times some of these cells dont have any values; and this is not an error on the part of the webservice but it happens so as thre is no data for that particular function call. Now when I am using a subtraction or and addition or using any factor to divide this number in a different cell I get a #VALUE error. I know I can use the "IF" function to get over this error but if there is any other way around this I would like to know. Also if there is a cell format that will change the null cell to a Zero (0) with a different color will do (color change will make me aware that it is something that the formating has done and the actual cell value is not a Zero.) I am using Excel 2003 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make my arrow buttons move from cell to cell in Excel? | Excel Discussion (Misc queries) | |||
Make cell contents a reference | Excel Discussion (Misc queries) | |||
Referencing cell in another sheet yields null? | Excel Worksheet Functions | |||
cell shows 0 when referenced cell is null | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel |