+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting - ignoring null values

  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    Issaquah, WA
    MS-Off Ver
    Excel 2010
    Posts
    17

    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. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Conditional Formatting - ignoring null values

    For column A try using this formula in conditional formatting

    =(A1<>0)*(A1<>"")
    Audere est facere

  3. #3
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Conditional Formatting - ignoring null values

    Would the following also work?
    =and(a1<>0,a1<>"")
    Please click the * icon below if I have helped.

  4. #4
    Registered User
    Join Date
    11-08-2012
    Location
    Issaquah, WA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Conditional Formatting - ignoring null values

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

  5. #5
    Registered User
    Join Date
    11-08-2012
    Location
    Issaquah, WA
    MS-Off Ver
    Excel 2010
    Posts
    17

    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. #6
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    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. #7
    Registered User
    Join Date
    11-08-2012
    Location
    Issaquah, WA
    MS-Off Ver
    Excel 2010
    Posts
    17

    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. #8
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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