+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting greater than 0 but not 0

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    Linthicum, MD
    MS-Off Ver
    Excel 2013
    Posts
    52

    Conditional Formatting greater than 0 but not 0

    I'm trying to figure out the conditional formatting formula to use for the following:
    If cell A1 is >0.7 format the cell green
    If cell A1 is <0.7 format the cell red
    If cell is 0, no formatting no color

    What I'm using now will format the cell green when you have a zero and green if you iferror the cell. Any help would be appreciated.

    Thanks.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Conditional Formatting greater than 0 but not 0

    for green

    =A1>0.7

    for red

    =AND(A1<>0,A1<0.7)

  3. #3
    Registered User
    Join Date
    02-29-2012
    Location
    Linthicum, MD
    MS-Off Ver
    Excel 2013
    Posts
    52

    Re: Conditional Formatting greater than 0 but not 0

    But that still leaves the cell green if it is a zero value. Any way to make the cell no color if zero?

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting greater than 0 but not 0

    No it won't.

    If A1 is 0
    Then =A1>0.7 is FALSE, therefor the green doesn't apply.

    What exactly is in A1, is it a formula? Post that formula.

    Or better yet, can you attach a sample XL book with the issue?

  5. #5
    Registered User
    Join Date
    02-29-2012
    Location
    Linthicum, MD
    MS-Off Ver
    Excel 2013
    Posts
    52

    Re: Conditional Formatting greater than 0 but not 0

    Here you go. Thank you so much for helping out.
    Attached Files Attached Files

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting greater than 0 but not 0

    You have the same conditional formats repeated like 100 times (i didn't actually count)
    And didn't have John's suggestion implimented..

    Here it is as John suggested (using C3 now as in your sheet)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-29-2012
    Location
    Linthicum, MD
    MS-Off Ver
    Excel 2013
    Posts
    52

    Red face Re: Conditional Formatting greater than 0 but not 0

    Perfect, thank you so much.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting greater than 0 but not 0

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting - Greater than or equal to
    By mmotorsport in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-28-2014, 04:40 PM
  2. [SOLVED] Conditional formatting: TIME greater then...
    By Chris! in forum Excel General
    Replies: 4
    Last Post: 09-19-2013, 08:56 PM
  3. Replies: 2
    Last Post: 02-04-2013, 02:31 PM
  4. Excel 2007 : Conditional formatting-Value is greater than
    By satishtote123 in forum Excel General
    Replies: 1
    Last Post: 06-11-2011, 12:17 PM
  5. Conditional Formatting and Greater than BUT less than
    By dagindi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2010, 03:08 PM
  6. Conditional Formatting greater than 100%
    By henro8 in forum Excel General
    Replies: 5
    Last Post: 10-08-2008, 05:18 PM
  7. Conditional Formatting if greater than zero
    By jkpha in forum Excel General
    Replies: 2
    Last Post: 10-31-2007, 01:50 PM
  8. Conditional formatting for the greater of 7 cells
    By guest123 in forum Excel General
    Replies: 8
    Last Post: 01-15-2007, 01:39 PM

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