Can an If function return a truely blank value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stevie D - 17 Jan 2006 19:16 GMT

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

Reply to this Message

vandenberg p - 17 Jan 2006 20:06 GMT

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

: 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

Reply to this Message

RagDyer - 17 Jan 2006 20:17 GMT

Try this:

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

Signature

HTH,

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

> Hi everyone,
>
[quoted text clipped - 28 lines]
>
> Steve

Reply to this Message

Dave Peterson - 17 Jan 2006 20:29 GMT

One way:
=sum(c1,5)

another:
=n(c1)+5

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

> Hi everyone,
>
[quoted text clipped - 28 lines]
>
> Steve

Signature

Dave Peterson

Reply to this Message

RagDyeR - 18 Jan 2006 15:30 GMT

One way:
=sum(c1,5)

DUH ! ! !<bg>

Signature

Regards,

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

One way:
=sum(c1,5)

another:
=n(c1)+5

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

Stevie D wrote:

> Hi everyone,
>
[quoted text clipped - 28 lines]
>
> Steve

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 17 Jan 2006 20:29 GMT

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

> Hi everyone,
>
[quoted text clipped - 28 lines]
>
> Steve

Signature

Dave Peterson

Reply to this Message

Selvarathinam - 17 Jan 2006 20:30 GMT

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.

Reply to this Message