#N/A

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

 

[Gary''s Student]

You can explicitly address the #!N/A using the FormulaIs option in
conditional formatting and use ISNA or ISERROR in the formula

 

[Barb Reinhardt]

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.

 

[Doug]

What would the formula look like if I am wanting all values > 0 to have a
green arrow up?

 

[Doug]

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

 

[ker_01]

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

 

[Jan van Niekerk]

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.

 

[Jan van Niekerk]

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

 

[Doug]

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.

 

[Doug]

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

 

[Doug]

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

 

[Doug]

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