blank to zero conversion?

J

Joe Miller

I wish to refer to a column of numbers in a formula where a zero i
represented by a blank rather than the number zero in the referenc
column. Is there a function which will convert the blank to a zero t
avoid an error?
(e.g.)
the following results in an error in cell B1 so I guess I need
function which I can use in the formula in B1 which will convert th
blank in A1 to a numeric zero?
CELL A1 CONTAINS blank
CELL A2 CONTAINS 2
CELL B1 CONTAINS =A1+A
 
B

Biff

Hi!

Instead of this: = A1+A2

Use this: =SUM(A1,A2)

The SUM() function will ignore TEXT entries.

Biff
 
G

Guest

If I understand what you described, I would put an IF function in B1 to check
the value of A1 in order to avoid an error. Also, if I understand A1 to have
a blank and and A2 = 2 (in this example), then B1 should be the value of 0+2.
I am not sure of the logic behind knowing what numbers you wish to add in
column A, so for this example I am going to just address the cells A1 & A2 in
checking for a blank.

FORMULA for B1
=IF(OR(A1="",A1=" "), A2, IF(OR(A2="",A2=" "), A1, A1+A2))

I tested this and it seems to work fine. However, I did get a value of 0
(zero) in B1 with both A1 & A2 were empty. Not sure why, but maybe this will
work for you.

Les
 
J

Joe Miller

THANKS LES
JOE MILLER
If I understand what you described, I would put an IF function in B1 t
check
the value of A1 in order to avoid an error. Also, if I understand A
to have
a blank and and A2 = 2 (in this example), then B1 should be the valu
of 0+2.
I am not sure of the logic behind knowing what numbers you wish to ad
in
column A, so for this example I am going to just address the cells A1
A2 in
checking for a blank.

FORMULA for B1
=IF(OR(A1="",A1=" "), A2, IF(OR(A2="",A2=" "), A1, A1+A2))

I tested this and it seems to work fine. However, I did get a value o
0
(zero) in B1 with both A1 & A2 were empty. Not sure why, but mayb
this will
work for you.

Les
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top