#N/A

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

Some of my cells have #N/A in them because one of the required lookups in
missing in some rows.
The problem I am having is when I try to set up Conditional Formatting for
the values in that column. It will not work if the selected range runs over
top of that #N/A value.
Is there a way around this problem? I would just a soon that it overlook the
#N/A value.
 
You can explicitly address the #!N/A using the FormulaIs option in
conditional formatting and use ISNA or ISERROR in the formula
 
Can you give an example of what you're doing for your conditional format and
what's happening? I'm having a hard time envisioning a problem with
conditional formats and #N/A, if the CF is written properly.
 
I am using a three color arrow style

The rule type is: Format all cells based on thier values
Format style is: Icon Sets
Green up arrow when value is > 0 Number
Yellow arrow when <=0 and >= 0
Red down arrow when <0
 
1. Are you using 2007 or 2003?

2. Can you explain your second condition? If condition A is ">" and
condition C is "<", wouldn't that only leave "=" in the middle? or are you
trying to color a multicell range based on all of the values in that range
being above, below (or mixed) compared to a target value?

3. If you are coloring a cell at a time via conditional formatting, you can
meet your criteria even in 2003. Set your default cell color to green, then
set your first condition to formula =iserror( ) and use that condition to
turn the cell white. then apply your conditions 2 and 3 with yellow and red.
Anything left over is green by default.

HTH,
Keith
 
Doug said:
Some of my cells have #N/A in them because one of the required lookups in
missing in some rows.
The problem I am having is when I try to set up Conditional Formatting for
the values in that column. It will not work if the selected range runs over
top of that #N/A value.
Is there a way around this problem? I would just a soon that it overlook the
#N/A value.
Click to expand...
 
Hi

I use the if function and the iserror to eliminate the #na

if(Iserror(vlookup(...)),0,vlookup(...))
the iserror can be replaced by isna

The 0 can be replaced by "" this makes it need
The conditional formatting will refer to a text field ""
 
1. I am using 2007
2. Yes I am wanting values greater than 0 to show green arrow up, values
less zero to show red arrow down, and values 0 to show nothing.
 
I think you have given the info that I need, but am having a hard time
applying it.
I just changed it to this, but is says I have an error. Do you see one here?
This returns a percentage difference between to sets of data, and I'm trying
to emit the N/A when one of the sets of data is not there?

=IF(ISERROR(VLOOKUP($Q3,Import!$Q:$CA,16,FALSE)/(VLOOKUP($Q3,'Old
Import'!$Q:$CA,16,FALSE))))-1
 
I think you have given the info that I need, but am having a hard time
applying it.
I just changed it to this, but is says I have an error. Do you see one here?
This returns a percentage difference between to sets of data, and I'm trying
to emit the N/A when one of the sets of data is not there?

=IF(ISERROR(VLOOKUP($Q3,Import!$Q:$CA,16,FALSE)/(VLOOKUP($Q3,'Old
Import'!$Q:$CA,16,FALSE))))-1
 
I tried this and it says there is an error. Do you see one?

=IF(ISERROR(VLOOKUP($S3,Import!$P:$CB,17,FALSE)/(VLOOKUP($S3,'Old
Import'!$P:$CB,17,FALSE))))-1
 
Back
Top