# Conditional Formatting - ignoring null values

1. ## Conditional Formatting - ignoring null values

I have an =IF statement in column that is set to leave the cell blank if one of the fields is blank, otherwise do a calculation. How do I set the conditional formatting to highlight any cell in the column whether the value of the cell is greater than or less than zero, but no highlighting if the value is zero or null?

2. ## Re: Conditional Formatting - ignoring null values

For column A try using this formula in conditional formatting

=(A1<>0)*(A1<>"")

3. ## Re: Conditional Formatting - ignoring null values

Would the following also work?
=and(a1<>0,a1<>"")

4. ## Re: Conditional Formatting - ignoring null values

After entering the formula above, do I apply another conditional format for values not equal to zero?

5. ## Re: Conditional Formatting - ignoring null values

Actually, it worked - I just realized I didn't have any format set so everything was "clear". Now it is highlighted.

Thanks, but I'm not sure I understand the logic in the formula - can you explain?

6. ## Re: Conditional Formatting - ignoring null values

Which formula are you referring to?
Mine simply states that if the value is BOTH not equal (<>) to 0 and not blank, then apply the formatting. The AND function means it needs to meet BOTH criteria.

7. ## Re: Conditional Formatting - ignoring null values

Your's makes sense (with the "and" statement), it's the other one I'm trying to figure out. Although I applied the one with the multiplication value before I saw your reply.

8. ## Re: Conditional Formatting - ignoring null values

our formulas return the same thing. remember, in excel: 1=TRUE, and 0=FALSE
so... assuming the value is 5, the following would be calculated using the formula:
=(A1<>0)*(A1<>"")
=(1)*(1)
=1
=true

if the value was blank the formula would calculate the following
=(A1<>0)*(A1<>"")
=(1)*(0)
=0
=false

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1