Why does Conditional Formatting highlight "n/a" as a number?

[Valerie]

I have written a formula that may return "n/a" or a number. If it is a
number, there are 3 conditional format colors that can occur if the number is
within 10%, etc from a specified target. If the formula brings back "n/a",
the cell is still turning a color as if it is 25% from the target. How do I
get the cell to have no highlight, since the value is "n/a"?
Thank you,
-Valerie

 

[Peo Sjoblom]

In the formula that returns either a number or the "n/a",
if you use a text representation of it change it to NA() instead of "N/A"
Or use formula is in the conditional formatting and use

=AND(ISNUMBER(A1),your_criteria)

if you have a text n/a in the cell it will always be greater than any number
thus greater than
25% from the target

--


Regards,


Peo Sjoblom

 

[Valerie]

Peo,
Condition #1 = IF Cell value is (greater than or equal to) (=h16*.25),
highlight it pink. I tried your edit to condition, but it remains pink. Any
other insight? The first solution did work but is not as clear as the second
one.
Thank you!
-Valerie

 

[Peo Sjoblom]

You would need something like this, for the first condition change to
formula is instead of cell value
(you can't use cell value when there are multiple conditions)


=AND($E$16>=$H$16*0.25,ISNUMBER($E$16))


where E16 is the cell that holds the conditional formatting

if you have multiple cells in the same column that needs this format
remove the dollar signs from everything but $H$16




--


Regards,


Peo Sjoblom

 

[Valerie]

Peo,
I think the most recent solution you sent does not work because the cells
have 3 conditions on them (within 10%, 10%-25%, over 25%) to create 3 cell
highlights. I will use the NA() to keep it simple. It results in #NA in the
cell instead of just n/a but that is fine.
Thank you so much!

 

[T. Valko]

I think the most recent solution you sent does not work

because the cells have 3 conditions on them (within 10%,
10%-25%, over 25%) to create 3 cell highlights.

I assume "within 10%" means from 0 to <10%. Do you want 0 to be highlighted?
Or, should it be >0 but <10% ?

Select the cell in question. Let's assume it's cell A1
Goto the menu Format>Conditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the little box on the right:
=AND(COUNT(A1),A1>25%)
Click the Format button
Select the desired style(s)
OK
Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the little box on the right:
=AND(COUNT(A1),A1>=10%)
Click the Format button
Select the desired style(s)
OK
Click the Add button

Condition 3
Select the Formula Is option
Enter this formula in the little box on the right:
=AND(COUNT(A1),A1>=0)
Click the Format button
Select the desired style(s)
OK out