if zero, then return blank/empty

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cls - 31 Jan 2008 03:22 GMT

Column F is sometimes empty. Sometimes does contain a value. Column H
contains the formula = F1 / 900. F2 / 900. F3 / 900. ... Currently, if a
row in column H refers to a blank row in column F, I get a return value of
zero. Any way to write the formula so that if row F is empty/blank, then I
get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

T. Valko - 31 Jan 2008 03:35 GMT

Try this:

=IF(F1=0,"",F1/900)

Copy down as needed.

Signature

Biff
Microsoft Excel MVP

> Column F is sometimes empty. Sometimes does contain a value. Column H
> contains the formula = F1 / 900. F2 / 900. F3 / 900. ... Currently, if
[quoted text clipped - 3 lines]
> I
> get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

Sebastian - 01 Feb 2008 15:18 GMT

Or try this:

=IF(ISBLANK(F1),"",F1/900)

> Try this:
>
[quoted text clipped - 9 lines]
> > I
> > get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

cls - 04 Feb 2008 02:25 GMT

=IF(F1=0,"",F1/900) This one worked best for my simple purposes/mind. & is
the most straight forward. One that I can also easily remember in future.
Sebastian's: =IF(ISBLANK(F1),"",F1/900) worked also. Thanks to all!

> Try this:
>
[quoted text clipped - 9 lines]
> > I
> > get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

T. Valko - 04 Feb 2008 04:27 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> =IF(F1=0,"",F1/900) This one worked best for my simple purposes/mind. & is
> the most straight forward. One that I can also easily remember in future.
[quoted text clipped - 16 lines]
>> > I
>> > get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

Teethless mama - 31 Jan 2008 04:33 GMT

=IF(AND(ISNUMBER(F1),F1<>0),F1/900,"")

> Column F is sometimes empty. Sometimes does contain a value. Column H
> contains the formula = F1 / 900. F2 / 900. F3 / 900. ... Currently, if a
> row in column H refers to a blank row in column F, I get a return value of
> zero. Any way to write the formula so that if row F is empty/blank, then I
> get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

T. Valko - 31 Jan 2008 05:02 GMT

=IF(COUNT(F1,-(F1<>0))=2,F1/900,"")

Signature

Biff
Microsoft Excel MVP

> =IF(AND(ISNUMBER(F1),F1<>0),F1/900,"")
>
[quoted text clipped - 6 lines]
>> I
>> get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

T. Valko - 31 Jan 2008 05:21 GMT

Ooops!

>or if it is zero, do not show??

Disregard this formula:

=IF(COUNT(F1,-(F1<>0))=2,F1/900,"")

Signature

Biff
Microsoft Excel MVP

> =IF(COUNT(F1,-(F1<>0))=2,F1/900,"")
>
[quoted text clipped - 8 lines]
>>> then I
>>> get a blank/null return value?? or if it is zero, do not show??

Reply to this Message

T. Valko - 31 Jan 2008 05:36 GMT

If your goal was a bulletproof formula:

=IF(COUNT(F1,1/F1)=2,F1/900,"")

Signature

Biff
Microsoft Excel MVP

> Ooops!
>
[quoted text clipped - 16 lines]
>>>> then I
>>>> get a blank/null return value?? or if it is zero, do not show??

Reply to this Message