Can an If function return a truely blank value?

[Stevie D]

Hi everyone,

In columns A and B on my worksheet I input numbers from a printout.

If there are any 0 values on the printout for the data to be entered in
either column A or B, then the cell should be left empty rather than the 0
input.

In column C I had a formula that divides the value in column A by the value
in column B e.g. =A1/B1.

For the rows with blank cells, the formula in column C returned either 0 or
#DIV/0! where I wanted it to be blank.

To get around this problem I changed the formula in column C e.g:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)

This works fine as far as it goes, however, In column D I have the formula
=C1+5

For the rows with "blank" values in columns A or B the formula in column D
returns #VALUE obviously because I'm trying to add a number to a text value,
albeit a blank one.

This is a problem I come across repeatedly, please can anybody suggest away
that I can get the IF function to return a truly blank value rather than
just an empty text string?

Thanks in advance,

Steve

[vandenberg p]

Hello:

There may be someone who knows a way, but I don't believe there is.
But you can fix your problem by changing the formula in D to:

=IF(ISERROR(C1+5),5,C1+5)

Which will behave as if C was blank. I am assuming you want the value
5 to appear if C1 contains the "". You can put any other value you
wish.

Pieter Vandenberg

Stevie D <[email protected]> wrote:
: Hi everyone,

: In columns A and B on my worksheet I input numbers from a printout.

: If there are any 0 values on the printout for the data to be entered in
: either column A or B, then the cell should be left empty rather than the 0
: input.

: In column C I had a formula that divides the value in column A by the value
: in column B e.g. =A1/B1.

: For the rows with blank cells, the formula in column C returned either 0 or
: #DIV/0! where I wanted it to be blank.

: To get around this problem I changed the formula in column C e.g:

: =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)

: This works fine as far as it goes, however, In column D I have the formula
: =C1+5

: For the rows with "blank" values in columns A or B the formula in column D
: returns #VALUE obviously because I'm trying to add a number to a text value,
: albeit a blank one.

: This is a problem I come across repeatedly, please can anybody suggest away
: that I can get the IF function to return a truly blank value rather than
: just an empty text string?

: Thanks in advance,

: Steve

[RagDyer]

Try this:

=IF(C1="",5,C1+5)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Stevie D" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
>
> In columns A and B on my worksheet I input numbers from a printout.
>
> If there are any 0 values on the printout for the data to be entered in
> either column A or B, then the cell should be left empty rather than the 0
> input.
>
> In column C I had a formula that divides the value in column A by the
value
> in column B e.g. =A1/B1.
>
> For the rows with blank cells, the formula in column C returned either 0
or
> #DIV/0! where I wanted it to be blank.
>
> To get around this problem I changed the formula in column C e.g:
>
> =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)
>
> This works fine as far as it goes, however, In column D I have the formula
> =C1+5
>
> For the rows with "blank" values in columns A or B the formula in column D
> returns #VALUE obviously because I'm trying to add a number to a text
value,
> albeit a blank one.
>
> This is a problem I come across repeatedly, please can anybody suggest
away
> that I can get the IF function to return a truly blank value rather than
> just an empty text string?
>
> Thanks in advance,
>
> Steve
>
>

[Selvarathinam]

Dear Steve,

Remove 0 values display option by getting into -> Tools -> Options ->
and remove tick from the check box of "Zero Values".

The above option helps u to display the 0 value as null in the screen.

then ur same syntax with 0 instead of using null ("")
i.e =IF(OR(ISBLANK(A1),ISBLANK(B1)),0,A1/B1)

The above syntax will help u sum the value into D column.

Hope the above solution will help u.

Regards,
Selvarathinam.

[Dave Peterson]

One way:
=sum(c1,5)

another:
=n(c1)+5

another:
=5+if(isnumber(c1),c1,0)




Stevie D wrote:
>
> Hi everyone,
>
> In columns A and B on my worksheet I input numbers from a printout.
>
> If there are any 0 values on the printout for the data to be entered in
> either column A or B, then the cell should be left empty rather than the 0
> input.
>
> In column C I had a formula that divides the value in column A by the value
> in column B e.g. =A1/B1.
>
> For the rows with blank cells, the formula in column C returned either 0 or
> #DIV/0! where I wanted it to be blank.
>
> To get around this problem I changed the formula in column C e.g:
>
> =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)
>
> This works fine as far as it goes, however, In column D I have the formula
> =C1+5
>
> For the rows with "blank" values in columns A or B the formula in column D
> returns #VALUE obviously because I'm trying to add a number to a text value,
> albeit a blank one.
>
> This is a problem I come across repeatedly, please can anybody suggest away
> that I can get the IF function to return a truly blank value rather than
> just an empty text string?
>
> Thanks in advance,
>
> Steve

--

Dave Peterson

[RagDyeR]

One way:
=sum(c1,5)

DUH ! ! !<bg>

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
One way:
=sum(c1,5)

another:
=n(c1)+5

another:
=5+if(isnumber(c1),c1,0)




Stevie D wrote:
>
> Hi everyone,
>
> In columns A and B on my worksheet I input numbers from a printout.
>
> If there are any 0 values on the printout for the data to be entered in
> either column A or B, then the cell should be left empty rather than the 0
> input.
>
> In column C I had a formula that divides the value in column A by the
value
> in column B e.g. =A1/B1.
>
> For the rows with blank cells, the formula in column C returned either 0
or
> #DIV/0! where I wanted it to be blank.
>
> To get around this problem I changed the formula in column C e.g:
>
> =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)
>
> This works fine as far as it goes, however, In column D I have the formula
> =C1+5
>
> For the rows with "blank" values in columns A or B the formula in column D
> returns #VALUE obviously because I'm trying to add a number to a text
value,
> albeit a blank one.
>
> This is a problem I come across repeatedly, please can anybody suggest
away
> that I can get the IF function to return a truly blank value rather than
> just an empty text string?
>
> Thanks in advance,
>
> Steve

--

Dave Peterson

[Dave Peterson]

But you can't return a truly empty cell using a formula.

Stevie D wrote:
>
> Hi everyone,
>
> In columns A and B on my worksheet I input numbers from a printout.
>
> If there are any 0 values on the printout for the data to be entered in
> either column A or B, then the cell should be left empty rather than the 0
> input.
>
> In column C I had a formula that divides the value in column A by the value
> in column B e.g. =A1/B1.
>
> For the rows with blank cells, the formula in column C returned either 0 or
> #DIV/0! where I wanted it to be blank.
>
> To get around this problem I changed the formula in column C e.g:
>
> =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)
>
> This works fine as far as it goes, however, In column D I have the formula
> =C1+5
>
> For the rows with "blank" values in columns A or B the formula in column D
> returns #VALUE obviously because I'm trying to add a number to a text value,
> albeit a blank one.
>
> This is a problem I come across repeatedly, please can anybody suggest away
> that I can get the IF function to return a truly blank value rather than
> just an empty text string?
>
> Thanks in advance,
>
> Steve

--

Dave Peterson