Ignoring N/A in a formula

[Kessie]

Hello, sorry I am an Excel novice with a bit of "willing" to improve processes at work.

I am trying to highlight cells that are different using conditional formatting, but ignore if there is a N/A in one of the cells.

So far I have:
=$C3<>D3 to highlight the cells orange if they do not match.

What can I add to this to perform the same function, but ignore the formula if one of the cells has a N/A in it?

Thank you

 

[Joe4]

Welcome to the Board!

Since those look like text entries of "N/A", and are not "#N/A" errors, try this:

=AND($C3<>D3,$C3<>"N/A",D3<>"N/A")

 

[Rhodie72]

I'm not sure what you're trying to achieve here as there is missing info.
If statments help wiuth this combined with ISERROR

 

[Joe4]

I'm not sure what you're trying to achieve here as there is missing info.
If statments help wiuth this combined with ISERROR

Not sure what you mean. Looks like a pretty straightforward and complete Conditional Formatting question.
I think the formula I provided should do what they want.

 

[Kessie]

Welcome to the Board!

Since those look like text entries of "N/A", and are not "#N/A" errors, try this:

=AND($C3<>D3,$C3<>"N/A",D3<>"N/A")

Thank you for your welcome and response.
That almost worked. I have attached a second image. The only bit that doesn't work is when the cells are the same - the formatting should be blank i.e. not highlighted.
Apologies, I am not an Excel wizz by any means, I understand this is probably very simple.
K

 

[Joe4]

Now I am confused. You are comparing column C to column D in the same row, not cell C7 to C5.

Can you explain in more detail, and include the row/column headers in your screen prints so we can see what cells you are talking about?

 

[Kessie]

Now I am confused. You are comparing column C to column D in the same row, not cell C7 to C5.

Can you explain in more detail, and include the row/column headers in your screen prints so we can see what cells you are talking about?

Apologies.
I am comparing the date in column C against the date in column D
If the dates are the same - no formatting required.
If the dates are different - highlight in colour.
If any of the columns contain the text N/A, no formatting required.
I hope this is clear.
Thank you
K

 

[Joe4]

Apologies.
I am comparing the date in column C against the date in column D
If the dates are the same - no formatting required.
If the dates are different - highlight in colour.
If any of the columns contain the text N/A, no formatting required.
I hope this is clear.
Thank you
K

That is exactly what the formula I gave you should do, except if you want both columns highlighted, you will need to lock down both column references in your formula, i.e.

=AND($C3<>$D3,$C3<>"N/A",$D3<>"N/A")

Also, make sure that when you enter the formula, you enter it as it pertains to the FIRST row in the range you have selected to apply the Conditional Formatting to.
It need to tie exactly to the range you have selected, not necessarily the range you want it to apply to.

I have seen people select entire columns (so it includes row 1), but think they can just write the formula using row 2 references, because they want to skip the first row.
You cannot do it that way. Everything would be shifted onre row.
The formula needs to synch up with the range you selected, not the range you want to apply it to.

 

[Kessie]

That is exactly what the formula I gave you should do, except if you want both columns highlighted, you will need to lock down both column references in your formula, i.e.

=AND($C3<>$D3,$C3<>"N/A",$D3<>"N/A")

Also, make sure that when you enter the formula, you enter it as it pertains to the FIRST row in the range you have selected to apply the Conditional Formatting to.
It need to tie exactly to the range you have selected, not necessarily the range you want it to apply to.

I have seen people select entire columns (so it includes row 1), but think they can just write the formula using row 2 references, because they want to skip the first row.
You cannot do it that way. Everything would be shifted onre row.
The formula needs to synch up with the range you selected, not the range you want to apply it to.

That has worked, thank you so much! I really appreciate your support.

 

[Joe4]

You are welcome.

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.