blank to zero conversion?

[Joe Miller]

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

[Biff]

Hi!

Instead of this: = A1+A2

Use this: =SUM(A1,A2)

The SUM() function will ignore TEXT entries.

Biff

"Joe Miller" <[email protected]> wrote
in message news:[email protected]...
>
> I wish to refer to a column of numbers in a formula where a zero is
> represented by a blank rather than the number zero in the reference
> column. Is there a function which will convert the blank to a zero to
> avoid an error?
> (e.g.)
> the following results in an error in cell B1 so I guess I need a
> function which I can use in the formula in B1 which will convert the
> blank in A1 to a numeric zero?
> CELL A1 CONTAINS blank
> CELL A2 CONTAINS 2
> CELL B1 CONTAINS =A1+A2
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile:
> http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=570145
>

[WLMPilot]

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



"Joe Miller" wrote:

>
> I wish to refer to a column of numbers in a formula where a zero is
> represented by a blank rather than the number zero in the reference
> column. Is there a function which will convert the blank to a zero to
> avoid an error?
> (e.g.)
> the following results in an error in cell B1 so I guess I need a
> function which I can use in the formula in B1 which will convert the
> blank in A1 to a numeric zero?
> CELL A1 CONTAINS blank
> CELL A2 CONTAINS 2
> CELL B1 CONTAINS =A1+A2
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=570145
>
>

[Joe Miller]

THANKS BIFF
JOE MILLER

Hi!

Instead of this: = A1+A2

Use this: =SUM(A1,A2)

The SUM() function will ignore TEXT entries.

Biff

"Joe Miller" <[email protected]> wrote
in message news:[email protected]...
>
> I wish to refer to a column of numbers in a formula where a zero is
> represented by a blank rather than the number zero in the reference
> column. Is there a function which will convert the blank to a zero to
> avoid an error?
> (e.g.)
> the following results in an error in cell B1 so I guess I need a
> function which I can use in the formula in B1 which will convert the
> blank in A1 to a numeric zero?
> CELL A1 CONTAINS blank
> CELL A2 CONTAINS 2
> CELL B1 CONTAINS =A1+A2
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile:
> http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=570145
>

[Joe Miller]

THANKS LES
JOE MILLER

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



"Joe Miller" wrote:

>
> I wish to refer to a column of numbers in a formula where a zero is
> represented by a blank rather than the number zero in the reference
> column. Is there a function which will convert the blank to a zero to
> avoid an error?
> (e.g.)
> the following results in an error in cell B1 so I guess I need a
> function which I can use in the formula in B1 which will convert the
> blank in A1 to a numeric zero?
> CELL A1 CONTAINS blank
> CELL A2 CONTAINS 2
> CELL B1 CONTAINS =A1+A2
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=570145
>
>

[Biff]

You're welcome!

Biff

"Joe Miller" <[email protected]> wrote
in message news:[email protected]...
>
> THANKS BIFF
> JOE MILLER
>
> Biff Wrote:
>> Hi!
>>
>> Instead of this: = A1+A2
>>
>> Use this: =SUM(A1,A2)
>>
>> The SUM() function will ignore TEXT entries.
>>
>> Biff
>>
>> "Joe Miller" <[email protected]>
>> wrote
>> in message
>> news:[email protected]...
>> >
>> > I wish to refer to a column of numbers in a formula where a zero is
>> > represented by a blank rather than the number zero in the reference
>> > column. Is there a function which will convert the blank to a zero
>> to
>> > avoid an error?
>> > (e.g.)
>> > the following results in an error in cell B1 so I guess I need a
>> > function which I can use in the formula in B1 which will convert the
>> > blank in A1 to a numeric zero?
>> > CELL A1 CONTAINS blank
>> > CELL A2 CONTAINS 2
>> > CELL B1 CONTAINS =A1+A2
>> >
>> >
>> > --
>> > Joe Miller
>> >
>> ------------------------------------------------------------------------
>> > Joe Miller's Profile:
>> > http://www.excelforum.com/member.php...o&userid=29900
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=570145
>> >
>
>
> --
> Joe Miller
> ------------------------------------------------------------------------
> Joe Miller's Profile:
> http://www.excelforum.com/member.php...o&userid=29900
> View this thread: http://www.excelforum.com/showthread...hreadid=570145
>