how can I count blank cells as a zero, when using formula please??
 

how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

{using the fake data of 5-6=1 and 0-3=#VALUE!}

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.

how can I count blank cells as a zero, when using formula please??
 

=IF(OR(A2="",B2=""),0,A2-B2)

--
Ian
--
"Ted" wrote in message
...
how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and
there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

{using the fake data of 5-6=1 and 0-3=#VALUE!}

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.

how can I count blank cells as a zero, when using formula plea
 

Hi, thanks for your help, but unfortunately it just makes it return zeros for
all of the sums - any ideas??

thanks again, Ted

"Ian" wrote:

=IF(OR(A2="",B2=""),0,A2-B2)

--
Ian
--
"Ted" wrote in message
...
how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and
there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

{using the fake data of 5-6=1 and 0-3=#VALUE!}

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.

how can I count blank cells as a zero, when using formula please??
 

Hi Ted

I don't quite understand the problem.
With the values shown and the formula of A1-B1, and A2-B2 you should get -1
and -3 respectively.
If you are getting a #VALUE for A2-B2, then A2 is probably not blank, but
contains a space or a "" value as a result of another formula.

You could use
=IF(COUNT(A2:B2)1,A2-B2,0)
or
=IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2-B2,0)



Regards

Roger Govier


Ted wrote:
how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

{using the fake data of 5-6=1 and 0-3=#VALUE!}

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.

how can I count blank cells as a zero, when using formula plea
 

thanks Roger - I'll give it a try,

Ted.

"Roger Govier" wrote:

Hi Ted

I don't quite understand the problem.
With the values shown and the formula of A1-B1, and A2-B2 you should get -1
and -3 respectively.
If you are getting a #VALUE for A2-B2, then A2 is probably not blank, but
contains a space or a "" value as a result of another formula.

You could use
=IF(COUNT(A2:B2)1,A2-B2,0)
or
=IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2-B2,0)



Regards

Roger Govier


Ted wrote:
how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

{using the fake data of 5-6=1 and 0-3=#VALUE!}

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.

how can I count blank cells as a zero, when using formula plea
 

I'm not sure what you need. The formula I posted is (in words). IF A2 is
blank OR B2 is blank, THEN this cell=0, ELSE this cell=A2-B2. If this is not
what you need, can you be clearer about your requirements, please?

--
Ian
--
"Ted" wrote in message
...
Hi, thanks for your help, but unfortunately it just makes it return zeros
for
all of the sums - any ideas??

thanks again, Ted

"Ian" wrote:

=IF(OR(A2="",B2=""),0,A2-B2)

--
Ian
--
"Ted" wrote in message
...
how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and
there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and
have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

{using the fake data of 5-6=1 and 0-3=#VALUE!}

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.