I forgot

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The Wolf - 24 Apr 2007 05:03 GMT

What is the conditional format for a cell to show nothing (blank) is a
formula in that cell can't reference other cells it needs.

Currently is show gobbly goop.

Thanks,
Mike

Reply to this Message

JE McGimpsey - 24 Apr 2007 06:15 GMT

> What is the conditional format for a cell to show nothing (blank) is a
> formula in that cell can't reference other cells it needs.
>
> Currently is show gobbly goop.

Can you be a bit more specific - what formula does the cell contain?

Reply to this Message

The Wolf - 24 Apr 2007 13:46 GMT

=1-(D8/D9)

There is nothing currently in d9 so the formula returns #DIV/0!

In the future there will be something in d9

There is a way to conditional format the formula cell so it is blank UNTIL
there is something in d9.

I just forgot how to do it.

On 4/23/07 10:15 PM, in article
jemcgimpsey-C893AA.23152223042007@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>> What is the conditional format for a cell to show nothing (blank) is a
>> formula in that cell can't reference other cells it needs.
>>
>> Currently is show gobbly goop.
>
> Can you be a bit more specific - what formula does the cell contain?

Reply to this Message

JE McGimpsey - 24 Apr 2007 14:50 GMT

> =1-(D8/D9)
>
[quoted text clipped - 4 lines]
> There is a way to conditional format the formula cell so it is blank UNTIL
> there is something in d9.

One could use conditional formatting, but in general it's best to avoid
the error entirely so that it doesn't inadvertently carry through. For
instance:

=IF(D9=0,"",1-D8/D9)

To conditionally format:

CF1: Formula Is =ISERROR(A1)
Format1: <font>/<same as background>

Reply to this Message

CyberTaz - 24 Apr 2007 15:08 GMT

Not Conditional Formatting, but one of several options:

In the formula cell use;

=If(ISERROR(1-(D8/D9)),,1-(D8/D9))

which will return a result of 0 if the formula errors, otherwise the result
will display.

You could also put something such as "No Data" between the 2 commas
(including the quotes) or any other text message of your choice if you don't
want to display the 0.

I'll bet John will come up with something niftier, but I didn't have anythig
better to do :-)

Signature

HTH |:>)
Bob Jones
[MVP] Office:Mac

> =1-(D8/D9)
>
[quoted text clipped - 17 lines]
>>
>> Can you be a bit more specific - what formula does the cell contain?

Reply to this Message

JE McGimpsey - 24 Apr 2007 15:39 GMT

> Not Conditional Formatting, but one of several options:
>
> In the formula cell use;
>
> =If(ISERROR(1-(D8/D9)),,1-(D8/D9))

Just a minor niggle: ISERROR will also mask other errors, so I don't
recommend it for this situation. For instance, if D9 is a calculated
value, and one of its input cells returns #VALUE, then the above formula
will return TRUE just as if D9 = 0.

Reply to this Message

CyberTaz - 24 Apr 2007 18:27 GMT

>> Not Conditional Formatting, but one of several options:
>>
[quoted text clipped - 6 lines]
> value, and one of its input cells returns #VALUE, then the above formula
> will return TRUE just as if D9 = 0.

You're quite correct, my friend, but I had the impression that the OP didn't
want "any" errors displayed in the cell. There may also be a possibility
that D8 is empty as well. What about:

=IF(ISBLANK(D9),"",1-D8/D9)
or
=IF(OR(ISBLANK(D9),ISBLANK(D8)),"",1-D8/D9)

I'm just an amateur at this computer stuff, ya' know ;-)

Signature

Regards |:>)
Bob Jones
[MVP] Office:Mac

Reply to this Message

The Wolf - 25 Apr 2007 01:13 GMT

Wow, thank you both, I am a neophyte.

=IF(ISBLANK(B8),"",1-B7/B8)

Works fine but leaves the solid triangle in the upper left corner of the
cell.

Does that still indicate some sort of error?

On 4/24/07 11:29 AM, in article #5pM4TphHHA.960@TK2MSFTNGP03.phx.gbl,

>>> Not Conditional Formatting, but one of several options:
>>>
[quoted text clipped - 16 lines]
>
> I'm just an amateur at this computer stuff, ya' know ;-)

Reply to this Message

CyberTaz - 25 Apr 2007 11:21 GMT

Yes - in this case it simply indicates that the formula refers to 1 or more
empty cells. When the cells have content the flag will go away or you can
remove it permanently: Select the cell then click the warning tag that
appears to its left (yellow diamond containing !), select Ignore Error.

Regards |:>)
Bob Jones
[MVP] Office:Mac

On 4/24/07 8:13 PM, in article C253EB2A.D345%elvisp@compuserve.com, "The
Wolf" <elvisp@compuserve.com> wrote:

> Wow, thank you both, I am a neophyte.
>
[quoted text clipped - 27 lines]
>>
>> I'm just an amateur at this computer stuff, ya' know ;-)

Reply to this Message

The Wolf - 25 Apr 2007 13:37 GMT

OK something else is going on now.

I am using this =IF(ISBLANK(B8),"",1-B7/B8)

But, the column net income is still showing #DIV/0! Because B7 contains
$0.00 since it has already run another formula.

On 4/25/07 3:21 AM, in article C254A3D2.230EA%onlygeneraltaz1@com.cast.net,

> Yes - in this case it simply indicates that the formula refers to 1 or more
> empty cells. When the cells have content the flag will go away or you can
[quoted text clipped - 39 lines]
>>>
>>> I'm just an amateur at this computer stuff, ya' know ;-)

Reply to this Message

JE McGimpsey - 25 Apr 2007 14:48 GMT

> OK something else is going on now.
>
> I am using this =IF(ISBLANK(B8),"",1-B7/B8)
>
> But, the column net income is still showing #DIV/0! Because B7 contains
> $0.00 since it has already run another formula.

If a cell contains a formula, it's not blank. I assume you meant that
the net income was #DIV/0 was because B8 contained 0.

if the formula returns a null string (""):

=IF(B8="","",1-B7/B8)

or, if you want to wear belt and suspenders:

=IF(AND(ISNUMBER(B8),B8<>0),1-B7/B8,"")

Reply to this Message

The Wolf - 26 Apr 2007 14:22 GMT

On 4/25/07 6:48 AM, in article
jemcgimpsey-00CE78.07480125042007@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>> OK something else is going on now.
>>
[quoted text clipped - 9 lines]
>
> =IF(B8="","",1-B7/B8)

Seems to work if there are values in b7/b8, but:

B8 contains =J8-B8-D8-F8-H8-L8

So, if there is nothing in j9 yet blah blah blah B8 reads $0.00

So, I am still getting #DIV/0!

> or, if you want to wear belt and suspenders:
>
> =IF(AND(ISNUMBER(B8),B8<>0),1-B7/B8,"")

Tells me there is a parenthesses missing.

Reply to this Message

JE McGimpsey - 26 Apr 2007 14:42 GMT

> Seems to work if there are values in b7/b8, but:
>
[quoted text clipped - 3 lines]
>
> So, I am still getting #DIV/0!

So use my initial suggestion from 4/24:

=IF(B8=0, "",1-B7/B8)

or the formula below.

> > or, if you want to wear belt and suspenders:
> >
> > =IF(AND(ISNUMBER(B8),B8<>0),1-B7/B8,"")
>
> Tells me there is a parenthesses missing.

There isn't.

Reply to this Message

The Wolf - 27 Apr 2007 14:32 GMT

Thank you for all the help, it is working the way I want now.

On 4/26/07 6:42 AM, in article
jemcgimpsey-E60CAA.07425626042007@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>> Seems to work if there are values in b7/b8, but:
>>
[quoted text clipped - 17 lines]
>
> There isn't.

Reply to this Message

Geoff Lilley - 25 Apr 2007 01:05 GMT

> =1-(D8/D9)
>
[quoted text clipped - 19 lines]
>
> > Can you be a bit more specific - what formula does the cell contain?

You could do one of two things:
1) Without using conditional formatting at all, you could do this:
=IF(ISERROR(1-(D8/D9)),"",1-(D8-D9))
2) If you really want to use conditional formatting, your condition
would be:
=ISERROR(1-(D8/D9))
and then format the cell with white text. That would work.

HTH
Cheers
Geoff

Reply to this Message

PhilD - 25 Apr 2007 08:15 GMT

> =1-(D8/D9)
>
[quoted text clipped - 6 lines]
>
> I just forgot how to do it.

Well, you could do

=if(d9="","",1-(d8/d9))

which says if D9 is blank, return a blank, otherwise run the formula.

Hope this helps.

PhilD

--
<><

Reply to this Message

Mac Forum / Applications / Excel / April 2007

I forgot