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

How to make a null cell as 0 (Zero) so that the subtraction works
 

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

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

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

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

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

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