#N/A message & Conditional format
 

Hi!

Is there any way to use conditional format to make the cell black, when the
formula results of cells are #N/A.

Thank you very much!

Ken

#N/A message & Conditional format
 

Yes,
In the conditional format setup, choose Formula Is rather than value is and
enter the condition that would result in a #N/A error.
Lets say that your cell's actual formula is
=VLOOKUP(A1,B1:C10,2,0)
and it results in an #N/A when there's nothing in A1 (or no match in the
B1:C10 table).
in the conditional format setup, after choosing Formula Is then enter this:
=ISNA(VLOOKUP(A1,B1:C10,2,0))
and set your format conditions accordingly.

"Ken" wrote:

Hi!

Is there any way to use conditional format to make the cell black, when the
formula results of cells are #N/A.

Thank you very much!

Ken

#N/A message & Conditional format
 

Or just ISNA with the cell that contains #N/A (less calculation required):

=ISNA(D3)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes,
In the conditional format setup, choose Formula Is rather than value is
and
enter the condition that would result in a #N/A error.
Lets say that your cell's actual formula is
=VLOOKUP(A1,B1:C10,2,0)
and it results in an #N/A when there's nothing in A1 (or no match in the
B1:C10 table).
in the conditional format setup, after choosing Formula Is then enter
this:
=ISNA(VLOOKUP(A1,B1:C10,2,0))
and set your format conditions accordingly.

"Ken" wrote:

Hi!

Is there any way to use conditional format to make the cell black, when
the
formula results of cells are #N/A.

Thank you very much!

Ken

#N/A message & Conditional format
 

Select your range
Conditional Formatting

Formula Is: =ISNA(A1)

format any color you like


"Ken" wrote:

Hi!

Is there any way to use conditional format to make the cell black, when the
formula results of cells are #N/A.

Thank you very much!

Ken

#N/A message & Conditional format
 

Now why didn't I think of it like that? I will the next time. The pupil
learns!

Question - without testing: if they had
=IF(ISNA(formula),"",formula)
in the cell, then =ISNA(D3) wouldn't work would it? Darn, now I've got to
go check.

"Jon Peltier" wrote:

Or just ISNA with the cell that contains #N/A (less calculation required):

=ISNA(D3)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes,
In the conditional format setup, choose Formula Is rather than value is
and
enter the condition that would result in a #N/A error.
Lets say that your cell's actual formula is
=VLOOKUP(A1,B1:C10,2,0)
and it results in an #N/A when there's nothing in A1 (or no match in the
B1:C10 table).
in the conditional format setup, after choosing Formula Is then enter
this:
=ISNA(VLOOKUP(A1,B1:C10,2,0))
and set your format conditions accordingly.

"Ken" wrote:

Hi!

Is there any way to use conditional format to make the cell black, when
the
formula results of cells are #N/A.

Thank you very much!

Ken

#N/A message & Conditional format
 

I guess that wouldn't work. However, in my philosophy of using different
ranges for different purposes, I'd keep my calculation intact in a range
offstage, then link my display to the calculation using

=IF(ISNA(AA3),"",AA3)

and also link the CF:

=IF(ISNA(AA3)

If I were making a line or XY chart from the data, I'd keep the #N/A in
place, so the point is omitted from the chart. "" isn't a blank, so it would
be plotted as a zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Now why didn't I think of it like that? I will the next time. The pupil
learns!

Question - without testing: if they had
=IF(ISNA(formula),"",formula)
in the cell, then =ISNA(D3) wouldn't work would it? Darn, now I've got to
go check.

"Jon Peltier" wrote:

Or just ISNA with the cell that contains #N/A (less calculation
required):

=ISNA(D3)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes,
In the conditional format setup, choose Formula Is rather than value is
and
enter the condition that would result in a #N/A error.
Lets say that your cell's actual formula is
=VLOOKUP(A1,B1:C10,2,0)
and it results in an #N/A when there's nothing in A1 (or no match in
the
B1:C10 table).
in the conditional format setup, after choosing Formula Is then enter
this:
=ISNA(VLOOKUP(A1,B1:C10,2,0))
and set your format conditions accordingly.

"Ken" wrote:

Hi!

Is there any way to use conditional format to make the cell black,
when
the
formula results of cells are #N/A.

Thank you very much!

Ken

#N/A message & Conditional format
 

Thanks for the explanation. Makes sense. And in the case of the OP, I think
it's probably preserving the #N/A also and the conditional formatting is
there much as for your missing data point - the black cell highlighting the
offending calculation.



"Jon Peltier" wrote:

I guess that wouldn't work. However, in my philosophy of using different
ranges for different purposes, I'd keep my calculation intact in a range
offstage, then link my display to the calculation using

=IF(ISNA(AA3),"",AA3)

and also link the CF:

=IF(ISNA(AA3)

If I were making a line or XY chart from the data, I'd keep the #N/A in
place, so the point is omitted from the chart. "" isn't a blank, so it would
be plotted as a zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Now why didn't I think of it like that? I will the next time. The pupil
learns!

Question - without testing: if they had
=IF(ISNA(formula),"",formula)
in the cell, then =ISNA(D3) wouldn't work would it? Darn, now I've got to
go check.

"Jon Peltier" wrote:

Or just ISNA with the cell that contains #N/A (less calculation
required):

=ISNA(D3)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes,
In the conditional format setup, choose Formula Is rather than value is
and
enter the condition that would result in a #N/A error.
Lets say that your cell's actual formula is
=VLOOKUP(A1,B1:C10,2,0)
and it results in an #N/A when there's nothing in A1 (or no match in
the
B1:C10 table).
in the conditional format setup, after choosing Formula Is then enter
this:
=ISNA(VLOOKUP(A1,B1:C10,2,0))
and set your format conditions accordingly.

"Ken" wrote:

Hi!

Is there any way to use conditional format to make the cell black,
when
the
formula results of cells are #N/A.

Thank you very much!

Ken