I forgot

[The Wolf]

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

 

[JE McGimpsey]

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?

 

[The Wolf]

=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.

 

[JE McGimpsey]

=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.

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>

 

[JE McGimpsey]

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.

 

[CyberTaz]

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

 

[CyberTaz]

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.

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 ;-)

 

[Geoff Lilley]

=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.



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

 

[The Wolf]

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?

 

[PhilD]

=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.

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

 

[CyberTaz]

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

 

[The Wolf]

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.

 

[JE McGimpsey]

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,"")

 

[The Wolf]

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)

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.

 

[JE McGimpsey]

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!

So use my initial suggestion from 4/24:

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

or the formula below.

Tells me there is a parenthesses missing.

There isn't.

 

[The Wolf]

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