#N/A message & Conditional format

K

Ken

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
 
J

JLatham

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

Jon Peltier

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

=ISNA(D3)

- Jon
 
T

Teethless mama

Select your range
Conditional Formatting

Formula Is: =ISNA(A1)

format any color you like
 
J

JLatham

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

Jon Peltier

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
 
J

JLatham

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