software

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 quot;IFquot; 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

Apply =CODE(your_cell) to one of these cells, see if you get 32 or 160,
Regards,
Alan.
quot;Kekin Kakkaquot; lt;Kekin gt; wrote in message
...
gt; Each cell in my worksheet is a function call that is requesting data from
gt; webservice. But at times some of these cells dont have any values; and
gt; this
gt; is not an error on the part of the webservice but it happens so as thre is
gt; no
gt; data for that particular function call.
gt;
gt; Now when I am using a subtraction or and addition or using any factor to
gt; divide this number in a different cell I get a #VALUE error. I know I can
gt; use
gt; the quot;IFquot; function to get over this error but if there is any other way
gt; around
gt; this I would like to know. Also if there is a cell format that will change
gt; the null cell to a Zero (0) with a different color will do (color change
gt; will
gt; make me aware that it is something that the formating has done and the
gt; actual
gt; cell value is not a Zero.)
gt;
gt; I am using Excel 2003 Professional
You would need a formula, there is no format that will convert a quot;quot; (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 A4both will ignore text
--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Kekin Kakkaquot; lt;Kekin gt; wrote in message
...
gt; Each cell in my worksheet is a function call that is requesting data from
gt; webservice. But at times some of these cells dont have any values; and
gt; this
gt; is not an error on the part of the webservice but it happens so as thre is
gt; no
gt; data for that particular function call.
gt;
gt; Now when I am using a subtraction or and addition or using any factor to
gt; divide this number in a different cell I get a #VALUE error. I know I can
gt; use
gt; the quot;IFquot; function to get over this error but if there is any other way
gt; around
gt; this I would like to know. Also if there is a cell format that will change
gt; the null cell to a Zero (0) with a different color will do (color change
gt; will
gt; make me aware that it is something that the formating has done and the
gt; actual
gt; cell value is not a Zero.)
gt;
gt; I am using Excel 2003 Professional
Hi

Use SUM function instead of adding/substracting, like
=SUM(A1:A4,-A5)
, or
=SUM(A1:A4)-SUM(A5;A8)

Arvi Laanemets
quot;Kekin Kakkaquot; lt;Kekin gt; wrote in message
...
gt; Each cell in my worksheet is a function call that is requesting data from
gt; webservice. But at times some of these cells dont have any values; and
this
gt; is not an error on the part of the webservice but it happens so as thre is
no
gt; data for that particular function call.
gt;
gt; Now when I am using a subtraction or and addition or using any factor to
gt; divide this number in a different cell I get a #VALUE error. I know I can
use
gt; the quot;IFquot; function to get over this error but if there is any other way
around
gt; this I would like to know. Also if there is a cell format that will change
gt; the null cell to a Zero (0) with a different color will do (color change
will
gt; make me aware that it is something that the formating has done and the
actual
gt; cell value is not a Zero.)
gt;
gt; I am using Excel 2003 Professional
Thanks Peo,

This was a nice fix for my problem.

Regards,
Kekin

quot;Peo Sjoblomquot; wrote:

gt; You would need a formula, there is no format that will convert a quot;quot; (I
gt; assume that is what you have)
gt; to zero, there is another function that can be used
gt;
gt; =C3-N(A4)
gt;
gt; where A4 is the cell with the null string
gt;
gt; so
gt;
gt; =N(A4)
gt;
gt; will return zero if A4 is text or empty
gt;
gt; also if you are multiplying you can use
gt;
gt; =POWER(C3,A4)
gt;
gt; equals =C3*A4
gt;
gt; for adding you can use
gt;
gt; =SUM(C3,A4)
gt;
gt; equals =C3 A4
gt;
gt;
gt; both will ignore text
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Kekin Kakkaquot; lt;Kekin gt; wrote in message
gt; ...
gt; gt; Each cell in my worksheet is a function call that is requesting data from
gt; gt; webservice. But at times some of these cells dont have any values; and
gt; gt; this
gt; gt; is not an error on the part of the webservice but it happens so as thre is
gt; gt; no
gt; gt; data for that particular function call.
gt; gt;
gt; gt; Now when I am using a subtraction or and addition or using any factor to
gt; gt; divide this number in a different cell I get a #VALUE error. I know I can
gt; gt; use
gt; gt; the quot;IFquot; function to get over this error but if there is any other way
gt; gt; around
gt; gt; this I would like to know. Also if there is a cell format that will change
gt; gt; the null cell to a Zero (0) with a different color will do (color change
gt; gt; will
gt; gt; make me aware that it is something that the formating has done and the
gt; gt; actual
gt; gt; cell value is not a Zero.)
gt; gt;
gt; gt; I am using Excel 2003 Professional
gt;
gt;
gt;

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

quot;Alanquot; wrote:

gt; Apply =CODE(your_cell) to one of these cells, see if you get 32 or 160,
gt; Regards,
gt; Alan.
gt; quot;Kekin Kakkaquot; lt;Kekin gt; wrote in message
gt; ...
gt; gt; Each cell in my worksheet is a function call that is requesting data from
gt; gt; webservice. But at times some of these cells dont have any values; and
gt; gt; this
gt; gt; is not an error on the part of the webservice but it happens so as thre is
gt; gt; no
gt; gt; data for that particular function call.
gt; gt;
gt; gt; Now when I am using a subtraction or and addition or using any factor to
gt; gt; divide this number in a different cell I get a #VALUE error. I know I can
gt; gt; use
gt; gt; the quot;IFquot; function to get over this error but if there is any other way
gt; gt; around
gt; gt; this I would like to know. Also if there is a cell format that will change
gt; gt; the null cell to a Zero (0) with a different color will do (color change
gt; gt; will
gt; gt; make me aware that it is something that the formating has done and the
gt; gt; actual
gt; gt; cell value is not a Zero.)
gt; gt;
gt; gt; I am using Excel 2003 Professional
gt;
gt;
gt;

Thanks for the feedback

Peoquot;Kekin Kakkaquot; gt; wrote in message
news
gt; Thanks Peo,
gt;
gt; This was a nice fix for my problem.
gt;
gt; Regards,
gt; Kekin
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; You would need a formula, there is no format that will convert a quot;quot; (I
gt;gt; assume that is what you have)
gt;gt; to zero, there is another function that can be used
gt;gt;
gt;gt; =C3-N(A4)
gt;gt;
gt;gt; where A4 is the cell with the null string
gt;gt;
gt;gt; so
gt;gt;
gt;gt; =N(A4)
gt;gt;
gt;gt; will return zero if A4 is text or empty
gt;gt;
gt;gt; also if you are multiplying you can use
gt;gt;
gt;gt; =POWER(C3,A4)
gt;gt;
gt;gt; equals =C3*A4
gt;gt;
gt;gt; for adding you can use
gt;gt;
gt;gt; =SUM(C3,A4)
gt;gt;
gt;gt; equals =C3 A4
gt;gt;
gt;gt;
gt;gt; both will ignore text
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt; quot;Kekin Kakkaquot; lt;Kekin gt; wrote in message
gt;gt; ...
gt;gt; gt; Each cell in my worksheet is a function call that is requesting data
gt;gt; gt; from
gt;gt; gt; webservice. But at times some of these cells dont have any values; and
gt;gt; gt; this
gt;gt; gt; is not an error on the part of the webservice but it happens so as thre
gt;gt; gt; is
gt;gt; gt; no
gt;gt; gt; data for that particular function call.
gt;gt; gt;
gt;gt; gt; Now when I am using a subtraction or and addition or using any factor
gt;gt; gt; to
gt;gt; gt; divide this number in a different cell I get a #VALUE error. I know I
gt;gt; gt; can
gt;gt; gt; use
gt;gt; gt; the quot;IFquot; function to get over this error but if there is any other way
gt;gt; gt; around
gt;gt; gt; this I would like to know. Also if there is a cell format that will
gt;gt; gt; change
gt;gt; gt; the null cell to a Zero (0) with a different color will do (color
gt;gt; gt; change
gt;gt; gt; will
gt;gt; gt; make me aware that it is something that the formating has done and the
gt;gt; gt; actual
gt;gt; gt; cell value is not a Zero.)
gt;gt; gt;
gt;gt; gt; I am using Excel 2003 Professional
gt;gt;
gt;gt;
gt;gt;